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?
- For figuring out what might break when upgrading SQL Server (the classic DRU use case). This includes things like applications or deprecated code.
- For performance testing
- For load testing (with some “gotchas,” which I cover in a later post)
- Because Microsoft says…
- It is the recommended feature to use for replay
- It has the ability to “…replay using multiple load generation engines as opposed to Profiler, which is only a single-client, single-threaded engine”
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?
- Decide the architecture of the utility (location of the controller, client(s), and target).
- 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.
- 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).
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 Server110ToolsDReplayClientLog (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.