Configure SQL Server Distributed Replay feature


By:   |   Updated: 2013-10-22   |   Comments   |   Related: More > 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)

Distributed Replay Windows Services

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


Last Updated: 2013-10-22


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

How to Grant Permissions to run SQL Server Profiler for a non System Admin User

Scheduling a SQL Server Profiler Trace

Use SQL Server Profiler to trace database calls from third party applications

Find Long Running SQL Server Queries Using Profiler

Using the SQL Server Default Trace to Audit Events





get free sql tips
agree to terms


Learn more about SQL Server tools