Improving Performance of the Distributed Replay Utility on SQL Server 2012

Setup the DRU

What is it?

The Distributed Replay Utility (DRU) on SQL Server allows you to capture Profiler trace information on one or more client servers and then physically replay them on a different server.  First introduced in SQL Server 2012, it works with all subsequent editions and captures workloads with version 2005 onward.

Why would you want to use it?

How does it work?

The DRU has four parts:

  • Administration tool – Currently the cmd prompt.
  • Controller – Think of this as the brain of the utility, as it oversees and coordinates the clients. There can be only one controller in each environment.
  • Target server – Where you replay the workload.  The target can be the same as the controller or the client.

A model of the DRU can be found here.

How do you set it up?

  1. Decide the architecture of the utility (location of the controller, client(s), and target).
  2. Create two accounts in the Active Directory – one for the controller and one for the client.  Note: if you choose to install the controller and the client on the same machine, these two accounts could be local accounts instead of AD accounts.
  3. Install the controllers and client, following the same SQL Server installation process used to install SQL Server 2012 or higher (you are adding a new feature to an existing application).  Simply add the Distributed Replay Controller feature to the controller server and the Distributed Replay Client feature to the client server (Jonathan Kehayias does an in-depth, step-by-step demonstration of the install and setup with screenshots here).

Unfortunately, there are a couple of installation “gotchas”:

  • Ensure the clients have permissions for the Distributed Replay Controller service (including the DBAs)!  Some sites recommend not including yourself, but I have found issues with the replay if you don’t take this extra step.
  • On the client side, it’s important to include the controller name (preferably just the host name and not the fully qualified domain name) in the install.
  • Go to “Component Services” on the server, then Computers>My Computer> DCOM Config>DReplayController. Right click to find “Properties,” then go to Security>Launch and Activation Permissions>Edit. Check that the client (and the DBAs) has all permissions listed (the illustration below shows the client specifically).
Ensuring that the client has all the permissions it needs to work.

4. Go to “Component Services” on the server, then Computers>My Computer> DCOM Config>DReplayController. Right click to find “Properties,” then go to Security>Launch and Activation Permissions>Edit. Check that the client (and the DBAs) has all permissions listed (the illustration below shows the client specifically).

5. Ensure the controller account and client accounts are sysadmins on the SQL servers in question (just one of the many reasons never to use this in production!). The controller must be able to access and run whatever workloads are coming from the client servers, and the clients need to have the same access. While there is some debate over whether sysadmin permissions are required, I highly recommend it. Otherwise, you may encounter errors if any permissions needed during the run are not present.

6. Start the client and confirm that it has registered to the controller by going to “Services” on the client server.  To confirm that the service has started and registered, go to C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log (below is a sample of the log file).

If the client is not registering to the controller, go to “Services” on the server; right click; choose Properties; and confirm that you have the correct account/password for the client. If those properties are correct, check whether or not you entered the correct controller name when you installed the utility (not that I was ever bitten by this a time or two…or three!).  If you forgot to do so, then you will probably have to uninstall and reinstall.

You can now stop the services (first on the client(s), then on the controller), as you are ready to trace a workload and replay it, which we’ll cover in the next post.

Additional Reading

https://www.sqlskills.com/blogs/jonathan/installing-and-configuring-sql-server-2012-distributed-replay/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s