dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, 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 the postings!
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:
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:
Connect to the target and a screen for trace properties opens:
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:
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:
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:
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:
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:
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:
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.
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).