Blog - comments

Hi Olivier,DELAYED_DURABILITY doesn't change the SQL Server behavior of opening the data and log fil...
Hi David,Thanks for this demo with write functions. As much I know, SQL Server always access files (...
Olivier Berthommé
Thank you for your feedback. I will build a test environment and realize a guide to deploy Grid Infr...

Hi Greg,

Thanks

great job Dave ! thanks

greg
Blog Stéphane Savorgnano SQL Server 2012: Create & replay a trace with Distributed Replay

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

SQL Server 2012: Create & replay a trace with Distributed Replay

My last blog was about installing and configuring Microsoft SQL Server 2012 Distributed Replay. This time, I will show you how you can create a trace and replay it with Distributed Replay Clients on a specific SQL Server target.

This is my Distributed Replay configuration:

b2ap3_thumbnail_DR_Config_Test.jpg

 

Creation of the trace

I will use the two Distributed Replay clients to generate a workload against the target server with two PowerShell Windows which will execute diverse SQL scripts. To capture the trace, I will use the SQL Server profiler tool which is known in SQL Server to create, watch, store, and replay a trace.

Go to Microsoft SQL Server Management Studio, Tools and Open SQL Server Profiler:

b2ap3_thumbnail_SQLProfiler.jpg

 

Connect to the target and a screen for trace properties opens:

b2ap3_thumbnail_Trace_save.jpg

 

Add a name to your trace, save it to a file, change the property "Maximum file size" to avoid the split of your trace file. You should also enable a stop time for your trace which will save the workload during 20 minutes.

When the trace is done, you have to copy it on your Distributed Replay controller (vmtestsqlwin01) to perform the preprocess task.

 

Preprocess the trace

To preprocess the trace we have first to open a command prompt windows and start the Distributed Replay controller services:

Preprocess1.jpg

Now that the service is started, we have the possibility to edit the preprocess configuration file to add System Sessions during our replay. This configuration file (DReplay.Exe.Preprocess.config) is an XML file which looks like this:

b2ap3_thumbnail_Preprocess0.jpg

 

The syntax of the prepross command is:

 

dreplay preprocess[-m controller] -i input_trace_file     -d controller_working_dir[-c config_file][-f status_interval] 

To preprocess your trace, you should go back to your command prompt windows, execute 'cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" ' and run the preprocess command:

b2ap3_thumbnail_Preprocess2.jpg

 

This process will create two files on c:\distributedreplay which will be necessary to perform the replay. Those two files are ReplayEvents.irf and TraceStats.xml.

You can see the complete preprocess below:

b2ap3_thumbnail_Preprocess3.jpg

 

Replay the trace

Now that the trace is in preprocess, your Distributed Replay environment is ready to replay the trace on the target SQL Server vmtestsqlwin03.

First, you have to start the Distributed Replay client services on each client(vmtestsqlwin01 and vmtestsqlwin02) via the net start command on a command prompt window:

 

NET START "SQL Server Distributed Replay Client"

 

When, done you can go to the log file to see if both clients are connected to the controller (I explained how to do that in my first blog on this topic: SQL Server 2012: Installing and configuring Distributed Replay)

Before running the replay, you have the possibility to edit the replay configuration file to change parameters. This configuration file (DReplay.Exe.Replay.config) is an XML file which looks as follow:

b2ap3_thumbnail_Preprocess0-1.jpg

 

The syntax of the replay command is:

 

dreplay replay[-m controller] -d controller_working_dir [-o]     [-s target_server] -w clients [-c config_file]     [-f status_interval]

  • c is mandatory is you change the Replay configuration file or if you store it in a different place as the normal one
  • o is to capture  the clients' replay activity and saves it to a result trace file in the path specified by the "resultdirectory" element in the client configuration file
  • f is to specifie the frequency (in seconds) at which to display the status, by default each thirty secondes
 

Go to "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" and execute the following replay command:

 

DReplay replay -s “vmtestsqlwin03" -d "c:\distributedreplay" -w “vmtestsqlwin01,vmtestsqlwin02“ -o -f 15
 

This command will execute a replay of the trace from the working directory c:\distributedreplay where the preprocessed files are stored to the target SQL Server vmtestsqlwin03 via the two clients vmtestsqlwin01 and vmtestsqlwin02.

An output file will be created on each client in the Result Directory and the screen will be updated each fifteen secondes during the replay:

b2ap3_thumbnail_DR_replay_process.jpg

 

During the replay process, you can follow the status on the screen. When the replay is finished, you have the possibility to check the replay trace result on the ResultDir directory of each client.

 

Conclusion

Microsoft SQL Server Distributed Replay is in my opinion the best way to simulate a mission critical workload with the availability to run the trace up to sixteen clients with different modes like stress mode or synchronisation mode.

The drawback of this solution is that the implementation is more complex than with SQL Server profiler and also needs more resources (one controller, several clients and a target).

Rate this blog entry:
1

Stéphane Savorgnano is Consultant at dbi services. He has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. Stéphane Savorgnano is Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server implementation and maintenance. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. Stéphane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking / Financial Services, Chemicals & Pharmaceuticals, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Tuesday, 30 September 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter