SQLStarters

Distributed Replay is a feature that was first introduced with SQL Server 2012. It allows you to play a set of recorded transactions against a SQL Server database. This can be extremely useful if you’re doing hardware or SQL Server version upgrades and want to test the performance impacts of these changes, or if they’re going to break your application.

While setting up a Distributed Replay isn’t overwhelming amount of work it does take some time, especially if you’re looking to build an environment with multiple clients (which totally is what you should be doing!). For that you’re going to need the following things:

• A SQL Server instance where to restore a copy of a database to run your recorder workload against. I can’t stress this part enough, but never, ever run this against your production database or servers!
• At least once machine where to install Distributed Replay components. I prefer having minimum of two computers for this purpose, but you can get away with using just one.
• Backup of a database taken before you recorded the workload.

To set up your Distributed Replay machines you need to install the following components by running SQL Server setup. Management tools – Basic, SQL Server Distributed Replay Client and SQL Server Distributed Replay Controller. If you have more than one machine at your disposal, consider making one a dedicated controller and running the Clients on the other machines. On this scenario you’ll install Management tools – Basic and SQL Server Distributed Replay Controller on one machine and SQL Server Distributed Replay Client on the rest.

Firewall rules

If you have Windows Firewall enabled you need to enable Controller and Clients to communicate between each other. The easiest way is to create allow rules for DReplayController.Exe and DReplayClient.exe

You can do this either through GUI or by running the following commands in command line:

netsh advfirewall firewall add rule name="Allow DReplay Controller" dir=in program="[full_path_to_dreplaycontroller]\DReplayController.exe" action=allow

netsh advfirewall firewall add rule name="Allow DReplay Client" dir=in program="[full_path_to_dreplayclient]\DReplayClient.exe" action=allow

Just replace the [full_path_to_xxx] with the actual path where the executables reside and you’re done with the firewall.

Distributed Transaction settings

As the Distributed Replay uses DTC you’ll also need to add some permissions to DReplayController DCOM Config. This can be done by running the dcomcnfg.exe and then navigating to Computers\My Computer\DCOM Config folder and opening the properties for DReplayController.

Go to Security tab and then edit Launch and Activation Permissions. Add the user account you created for running the Distributed Replay and give permissions to Local and Remote Activation.

Then add Access Permissions. Add the same user account again and give permissions to Local and Remote Access.

Starting the Services and connecting to database

Now we can finally start the services for the Distributed Replay. On the controller you need to start the one called “SQL Server Distributed Replay Controller” either by going to Services management console or running the following command from the command line:

net start "SQL Server Distributed Replay Controller"

Repeat the same procedure for all the client computers but on them the service is called “SQL Server Distributed Replay Client“. After this we should check that the clients can connect to controller either by looking at their log-files, which are located in the “C:\Program Files (x86)\Microsoft SQL Server\xxx\Tools\DReplayClient\Log” directory. Or by running or by running the following command on the Controller:

dreplay status

If everything is working as it should, you’ll should see clients reporting READY status. Hit CTRL+C and return to command prompt again. If the clients aren’t connecting and you haven’t restarted them after performing the steps above, restart them now and try again.

The last step of preparing the environment is giving the account we’re using to run the Distributed Replay necessary permissions to connect to the server and database we’re testing.

Preparing and running the replay

After the environment is build, you’ve restored the database and have the workload recorded we can start working with Distributed Replay. First thing we need to do is pre-processing, which you can do by using the following command:

dreplay preprocess -i "X:\FolderToTrace\filename.trc" -d "X:\WorkDirectory"

If you’re recorded trace is large, this part might take awhile.

Once we’re done with the processing of the replay trace file we’ll get the following information on the console:

At this point we can finally run the workload against our database. This is done by running the following command at the Distributed Replay Controller:

dreplay replay -s "server_name" -o -d "X:\WorkDirectory" -w "dreplay_client1, dreplay_client2"

As you start the replay it’ll first dispatch events to all the clients. Again this might take awhile depending on the controller and replay machines and how large recording you have.

Once the events have been dispatched the clients will start executing queries against your database. You can monitor the progress from the Administration Console, it gives you an estimate how much time is left for the replay to complete and the success rate. Eventually it’ll end and you’ll see a screen that looks something like this.

In my case it took almost 4 hours to complete the run with almost 11,000,000 events with very good success rate (over 99%). As we chose to capture the client activity by using -0 parameter with dreplay.exe, we now have trace-files in all client machines. These trace files hold the usual information about the executions such as execution times, reads, writes and about the transactions that failed.

After running the test it’s a good idea to see what, if any, queries were failing. Also if you’re interested in comparing how certain queries ran compared to production, you can use the client generated trace files for that. And if you’re interested in monitoring the performance of the server hardware you can always run Performance Monitor collector on the background while running the replay.