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"

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

Description:
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"

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!

Comments

• Anonymous
  December 25, 2012
  Great article :)

• Anonymous
  April 16, 2013
  great article; question though: have you seen "Replay client has run out of memory" error.  I'm running a replay on 2 clients, both having 8GB ram on Win 2008R2 and at around 10% mark it fails with this error. and the  server memory isn't saturated fully.

• Anonymous
  October 20, 2013
  Where can I found StartWorkload.cmd?  The first step says create the folder C:DRDemo... so on step 5, I don't understand where C:DRDemoStartWorkload.cmd woudl come from....

• Anonymous
  October 02, 2014
  Would benefit from a discussion of the issues around constructing the test itself. e.g. stress mode vs. synchronization mode, what are the implications of using multiple clients (does the data from a single trace get replicated across all clients, can we use a different trace on each client), presumably we need to carefully choose what activities get traced (if we trace a set of database interactions that depend upon the data within the database being in a specific initial state, then we need to restore the database to that state before each replay; also we can't fire the same run from multiple clients because each will upset the data that the other depends upon).

• Anonymous
  December 30, 2014
  Mr Michael Brooks can you please give more details. Thanks