Using Distributed Replay to load test your SQL Server–Part 2

In the first part of this series, Batuhan Yildiz , a Premier Field Engineer from Microsoft Dubai, explained you how to install and get started with Distributed Replay. He continues in this series by showing you how to actually capture and replay workloads.

After we have configured the Distributed Replay as explained in the previous post, we can use the Distributed Replay environment to create the load on the SQL Server.

Data Collection from Source Server

We need to collect some data from the main production server and then we can use the collected data to create load on the new SQL Server. To do this, follow the steps below.

  1. Create “C:\DRDemo” folder on the controller (which is ServerN1 in our test.)
  2. Open SQL Server 2012 Profiler Trace utility on ServerN1
  3. Select File –> New Trace –> Connect to ServerN1. Note that we are using ServerN1 to simulate the workload as well for this test. In the real world we would be capturing the trace from a server which is currently in production.
  4. Select the TSQL – Replay template within “Use the template” drop down menu and run the trace.
  5. Run “C:\DRDemo\StartWorkload.cmd” to bring some load to SQL Server 2008 R2 system. The system is not production so the script is used to create some load on the production.
  6. After the script is completed, save the trace file within “C:\DRDemo”. For the example it is “C:\DRDemo\ServerN1_SQL2008R2_Trace.trc”.
  7. Now the trace from source server is ready.

Preprocess the source Trace file

The source trace file will be used to prepare files which are used by distributed clients to create load on the new SQL Server. Run the command below using command prompt on the controller (ServerN1) to prepare the files for the distributed clients.

c:\DRDemo>dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

Permissions need to be granted to the interactive user for DREPLAY

The screenshot above shows that an error occurred. The important part is “…and that the console user has the proper permissions to access the controller service”

The login account is sqladmin on the server. It can be checked using “whoamI” command using the command prompt on ServerN1. The system event log on ServerN1 says that

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {6DF8CB71-153B-4C66-8FC4-E59301B8011B} and APPID {961AD749-64E9-4BD5-BCC8-ECE8BA0E241F} to the user AYSQLTEST\sqladmin SID (S-1-5-21-2826735731-136765897-3671058344-1125) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

To fix this issue, we need to the steps below on ServerN1:

  1. ServerN1 - Run and type dcomcnfg and Component Services will be opened.
  2. Find DReplayController (Console Root –> Component Services –> Computers –> My Computer – >DCOM Config -> DReplayController)
  3. Open the properties of DReplayController and select Security tab
  4. Edit “Launch and Activation Permissions” and grant “sqladmin” domain user account “Local Activation” and “Remote Activation” permissions.
  5. Edit “Access Permissions” and grant “sqladmin” domain user account “Local Access” and “Remote Access”.
  6. Restart controller and client services like below
    NET STOP "SQL Server Distributed Replay Controller"
    NET STOP "SQL Server Distributed Replay Client"
    NET START "SQL Server Distributed Replay Controller"
    NET START "SQL Server Distributed Replay Client"
  7. Run the command again:

dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

Successful execution of DREPLAY preprocess

BTW, these steps might seem familiar – we had used them in the first post for the service account itself. Once the permission issue is fixed, the preprocess command generated 2 files within “C:\DRPreProcess” folder like shown below.

You can refer to this article for more details.

Replay against SQL Server 2012 using clients

Preprocess phase has been completed. Controller will take 2 files “ReplayEvents.irf”, “TraceStats.xml” and copy them to the clients (ServerN1,ServerN3) and replay them on the clients against target server which is SQL Server 2012 called ServerN2\SQL2012. Here is the command to do so:

dreplay replay -s ServerN2\sql2012rc0 -w ServerN1,ServerN3 -f 10 -o -d "C:\DRPreProcess"

The -o parameter will save the trace output within “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir”

At the same time open a profiler trace and connect to target server which is ServerN2\SQL2012 collect profiler trace. Then you can compare the trace file from source server and trace file from target server and check some query performance. You can refer to this article for more details.

That’s it! Hopefully we have covered the basics of Distributed Replay and how you can use it to reproduce a workload on a test system. Please leave your feedback and questions in the comments area below!