SQL Server 2012 Distributed Replay

 

Distributed Replay is a new feature intruded in SQL Server 2012. This blog post is to document the process of setting up distributed replay and a quick demo of how to use distributed replay.

 

First step is to install Distributed Replay Controller and Distributed Replay Client. Distributed Replay Controller and Distributed Replay Client don’t have to be on the same server. SQL Server Enterprise supports up to 16 Distributed Replay Clients. Using 16 Distributed Replay Clients to replay a workload doesn’t mean SQL Server will be getting 16 times of original workload, it means the original workload will be distributed into 16 players and database server will still be receiving same amount of workload.

 

 

After controller and client are installed, there will be 2 Windows services created accordingly. I used to a service account to run these 2 services:

 

Now we need to setup the permission so clients will be able to access controller. To do that, we will need to go to Component Services and look for DReplay Controller under DCOM Config. The permissions are required include “Local Access”, “Remote Access” for “Access Permissions” and “Local Activation”, “Remote Activation” for “Launch and Activation Permissions”:

 

 

Also, the service account needs to be added in “Distributed COM Users” group:

 

After permissions are setup, we will be able to start controller and client:

 

Note, firewall exception might be needed for successful connection.

 

The connection between client and controller has been successfully established, we will be able to see “Registered with controller” message in client log:

 

Now, we need to prepare a workload using SQL Server Profiler. We will use “TSQL_Replay” template to capture the workload needs to be replayed:

 

Save the captured trace file on local disk:

 

Use DReplay to preprocess the trace file for replaying:

 

Once preprocessing is done, we can start replaying the trace:

 

That’s it!

 

There are config files for both controller and client can be used for different test scenarios, refer to BOL for more details.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s