By: Ben Snaidero | Comments | Related: > Profiler and Trace
Problem
If you read my previous tip on Using the replay feature within SQL Server Profiler you should now know how to replay simple SQL Server traces. But what if there is a lot of concurrency in your application that can't easily be replicated. This tip will look at the SQL Server Distributed Replay feature that was introduced in SQL Server 2012 which can be used to replay these more complex workloads.
Solution
Overview of architecture
There are 4 components/services that make up the SQL Server Distributed Replay architecture:
- Administration Tool - This is a command line application that can be used from any machine to control a distributed replay session by sending requests to the controller windows service
- Controller Windows Service - This windows service controls the actions of all the replay clients based on information it receives from the administration tool
- Client Windows Service - This windows service is the component that is used to simulate the workload against the target SQL Server environment
- Target Server - This is the SQL Server database server that the replay will be run against.
There are a couple things I want to highlight in regards to the architecture. There can only be one controller service in your replay environment. There can be as many client services as required to replay your workload but each of these instances must be installed on their own computer. The controller service and one of the client services can run on the same machine if required.
For complete details on the SQL Server Distributed Replay architecture you can read this link from Microsoft.
Configure Controller Windows Service
The first thing that we need to configure is the controller windows service. The controller configuration file is located where you installed the Distributed Replay Controller service. Mine was in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.config. Here you can set the logging level for the controller service to either INFORMATION, WARNING or CRITICAL (default). We'll leave it set to the default. Here is what that file looks like.
<?xml version='1.0'?> <Options> <LoggingLevel>CRITICAL</LoggingLevel> </Options>
Configure Client Windows Service
Next to be configured is the client window service. This service runs on each client computer we plan to use to execute our replay session. The client configuration file is located where you installed the Distributed Replay Client service. Mine was in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.config. There is one thing that must be updated in this configuration file and that is the computer name of system running the controller service. This is needed because the client has to register with the controller. Here you can also set the logging level for the client as well as the working and client directories. The logging levels are the same as with the controller service and if not specified both directories default to the location of the configuration file. We'll leave these settings on the defaults and here is and an example of this configuration file with the these settings.
<?xml version='1.0'?>
<Options>
<Controller>DReplayControlCompter</Controller>
<WorkingDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient</WorkingDirectory>
<ResultDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient</ResultDirectory>
<LoggingLevel>CRITICAL</LoggingLevel>
</Options>
Configure Administration Tool - Preprocess Configuration File
The next thing we need to configure is the preprocess configuration file which is located in the folder where the administration tool was installed. In my case this was in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DReplay.Exe.Preprocess.config. This file contains a couple of options for setting the maximum idle time as well as whether or not system session activities will be replayed during the replay. We'll leave these set to the defaults as well and here is an example of that default file.
<?xml version='1.0'?>
<Options>
<PreprocessModifiers>
<IncSystemSession>No</IncSystemSession>
<MaxIdleTime>-1</MaxIdleTime>
</PreprocessModifiers>
</Options>
Configure Administration Tool - Replay Configuration File
The final thing to configure is the Replay configuration file which was is also located in the folder where the administration tool was installed, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DReplay.Exe.Replay.config. This file includes items like the name of the target server where the replay will be executed against as well as query timeouts and output options. Here is an example of the default configuration.
<?xml version='1.0'?>
<Options>
<ReplayOptions>
<Server></Server>
<SequencingMode>stress</SequencingMode>
<ConnectTimeScale></ConnectTimeScale>
<ThinkTimeScale></ThinkTimeScale>
<HealthmonInterval>60</HealthmonInterval>
<QueryTimeout>3600</QueryTimeout>
<ThreadsPerClient></ThreadsPerClient>
</ReplayOptions>
<OutputOptions>
<ResultTrace>
<RecordRowCount>Yes</RecordRowCount>
<RecordResultSet>No</RecordResultSet>
</ResultTrace>
</OutputOptions>
</Options>
One thing to note in regards to both the preprocess and replay configuration files. Although I've outlined above how these files can be customized, if you prefer to just use command line switches the configuration file values can be overridden by providing these switches when calling the DReplay.exe executable. A full description of all the options available for each component can be found here.
Starting the Windows Services
Now that we have everything configured we can start the services. Since I generally run multiple clients I usually start all the services remotely from one computer. Here is an example of how this can be done using PowerShell. Note: You'll need to replace $$SERVERNAME$$ with your the name of the server running each service in your environment.
Start-Service -inputobject $(Get-Service -ComputerName $$SERVERNAME$$
-Name "SQL Server Distributed Replay Controller")
Start-Service -inputobject $(Get-Service -ComputerName $$SERVERNAME$$
-Name "SQL Server Distributed Replay Client")Once started you can launch the services snap-in and verify that they are running (or use PowerShell)
Once done executing your replay you can stop the services using a PowerShellscript similar to the one we used to start the services. Here is an example.
Stop-Service -inputobject $(Get-Service -ComputerName $$SERVERNAME$$
-Name "SQL Server Distributed Replay Controller")
Stop-Service -inputobject $(Get-Service -ComputerName $$SERVERNAME$$
-Name "SQL Server Distributed Replay Client")
Next Steps
- Keep an eye out for my next tip where I will demonstrate the steps required to replay a trace using this feature
- Read more tips on using PowerShell to manage sql server services
- Read about other new features available in SQL Server 2012
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
