Using Distributed Replay to load test your SQL Server–Part

Have you decided to migrate SQL Server to a newer version? Would you like to test the load on the new SQL Server? How can you create a load on the new SQL Server? Do you need to develop a multi threaded application to simulate load on SQL Server? Using a new feature of SQL Server 2012, called Distributed Replay, it is so simple to simulate load on a SQL Server for testing purposes.
Before proceeding, it may be useful to review what Distributed Replay is all about.

Installation

There should be one distributed replay controller and between 1 and 16 distributed replay clients. Distributed Replay Controller and Distributed Replay Client can be installed separately or together on any server. If you decide to install Distributed Replay on a Server, in addition to this Management Tools should be installed to the same SQL Server because dreplay.exe file (Distributed Replay Administration Tool) comes with the client tools as highlighted below.

Test Setup

We are using the following computers for our test.

• ServerN1: this will be our controller; it has the following components installed:
• Distributed Replay Client
• Distributed Replay Controller
• Management Tools
• SQL Server 2008 R2 Database Engine Services
• ServerN2: this will be our target system. It has SQL Server 2012 Database Engine Services installed.
• ServerN3: this is a Distributed Replay Client

The controller name is set as ServerN1 for ServerN1 and ServerN3
For your information, here are the installation folders for each server:
ServerN1: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient and
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController
ServerN2: Nothing installed related to Distributed Replay because those features has not been selected.
ServerN3: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient

Service Accounts

Separate service accounts can be configured for “Distributed Replay Client” and “Distributed Replay Controller”. For our example “sqlservice” domain user account has been used.
After setting new service accounts "SQL Server Distributed Replay Controller" and "SQL Server Distributed Replay Client" services has been restarted using the command prompt on ServerN1 and ServerN3 like below.
ServerN1:

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"

ServerN3:

NET STOP "SQL Server Distributed Replay Client"
NET START "SQL Server Distributed Replay Client"

After the restart, check the last log file of the Distributed Replay Client within the folder “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log”. You may find that an error “Failed to connect controller with error code 0x80070005” is reported. Error code 0x80070005 means “Access is denied”.

Remediation: ServerN1

1. ServerN1 - Start -> 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 “sqlservice” domain user account “Local Activation” and “Remote Activation” permissions.
5. Edit “Access Permissions” and grant “sqlservice” domain user account “Local Access” and “Remote Access”.
6. Add “sqlservice” domain user account within “Distributed COM Users” group.
7. 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"
8. Check the Distributed Replay Client log file and see the message “Registered with controller ServerN1”

ServerN3

1. Restart client service like below
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Client"
2. Check the Distributed Replay Client log file and see the message can be seen like “Failed to connect controller with error code 0x800706BA”. ServerN3 client may not connect to controller which is running on ServerN1.

Firewall Exceptions

As per this link, we need to set the firewall exceptions correctly. Apply the steps below on each remote Distributed Replay Client:

1. Open Windows Firewall with Advanced Security
2. Click Inbound Rules – Right click and select New Rule
3. Rule Type: Select Program and click next
4. Program: Select “This program path” and browse “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.exe” and click next
5. Action: Select “Allow the connection” and click next
6. Profile: Keep all selected such as Domain, Private and Public (usually Domain should be enough) and click next
7. Name: Type “Allow Distributed Replay Client” and click finish

Apply the steps below on each Distributed Replay Controller:

1. Open Windows Firewall with Advanced Security
2. Click Inbound Rules – Right click and select New Rule
3. Rule Type: Select Program and click next
4. Program: Select “This program path” and browse “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.exe” and click next
5. Action: Select “Allow the connection” and click next
6. Profile: Keep all selected such as Domain, Private and Public (usually Domain should be enough) and click next
7. Name: Type “Allow Distributed Replay Controller” and click finish

Apply the steps below on ServerN3

1. Restart client service like below
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Client"
2. Check the Distributed Replay Client log file and see the message “Registered with controller ServerN1”. ServerN3 client will connect to controller properly.

Conclusion

At this stage, our Distributed Replay clients are setup and working correctly. In the next part of this article, we will show you how to use the DREPLAY utility to replay a workload on the server.

Edited and posted by Arvind Shyamsundar, MSPFE Editor.