Distributed Replay - A multi-part series. Part 2 – The Parts

Distributed Replay is a feature to help you assess the impact of future SQL Server upgrades, the impact of hardware or operating system upgrades, and for SQL Server tuning.   However, the best way to look at it is that it is similar to SQL Server Profiler but is not limited to replaying the workload from a single computer.  When replaying an intensive OLTP application workload that has many concurrent connections, profiler can become a resource nightmare.  This is where distributed replay comes in; it is excellent for situations where the concurrency in the captured trace is so high that a single replay client cannot sufficiently simulate it.  

Distributed Replay accomplishes this by being truly distributed. It uses multiple computers for concurrent processing, making it excellent for performance testing and capacity planning in OLTP environments.  Distributed replay supports up to 16 physical or virtual computers.  The ability to recreate a real world workflow make it fantastic for testing indexes and performance issues, whether they are hardware or software related.

So what are the parts that make distributed replay? It is a simple console tool that requires some setup and configuration, but can be very powerful in its simple elegance.

Let’s start with the Administration Tool. Installed as part of the management tools in the feature selection of SQL Server 2012, it is a console application that initiates, monitors or cancels operations on the controller.

The Management Tools for SQL Server 2012 can be installed on a SQL Server 2005, 2008, or 2008 R2 server and allows the collection of data from instances as old as 2005.  However, all replay must be done on 2012 and greater instances.

The Distributed replay controller service coordinates the multiple clients.  There can only be one controller and one administration tool per installed distributed replay environment.  I will elaborate more on that in a future blog post, when we discuss running this in different domains.

A Distributed Replay Client Service is required on each client server. These servers can be either physical or virtual and will work together to simulate the workload on the target server.  Distributed replay allows for a maximum of 16 client servers.  16 physical or virtual machines can give you extensive coordinated processing power for testing concurrent workloads.  The target server must be a SQL Server 2012 or higher instance and it is recommended that it be a test environment.

You may be concerned that the functionality of a console tool will be limited.. However, each stage of processing and replay of the tool has its own XML configuration file to enable customization.  This is another way that distributed replay allows you to use your data your way!

I mentioned that Distributed Replay is installed as part of Management Tools 2012 or higher.  For ease of explanation I have diagramed each of the pieces of the tool on separate servers. The blue box above outlines the services that can be combined on a single server.  In such a situation all the configuration files for those three boxes would reside on the same server.  Each service has its own configuration file to allow for customization.  The XML files can be found in the tools directory under the DReplayClient folder for the Client configuration and the DreplayController file for the remaining configuration files.

C:\Program Files (x86)\Microsoft SQL Server\110\Tools

\DReplayClient and \DReplayController

Let’s take a look at what level of control each of these configuration files give us. Within the controller configuration file the only thing we can configure is the logging level with options of Informational, Warning, or Critical

Yes, I did say they are XML (EXtensible Markup Language) files, but don’t worry if you are not familiar with XML. The files follow a standard pattern, with start and end tags encapsulating your data.  

In the controller configuration file the only thing we can configure is the logging level with options of; Informational, Warning, or Critical.  This is the easiest place to learn.  You can see here, between the greater than and less than symbols, where you can make the change to the logging level.  

Within the preprocess configuration file, we can modify which system session activities are included and idle time only.

The client configuration file has only controller, working directory, result directory and logging level that can be configured.

The replay configuration file is located on the same server as the administration tool.  This configuration file will be used by the administration tool to control all the actions of the coordinated replay on all of the clients. You can configure the target instance, sequencing, stress scale, granularity, use connection pooling, health monitor interval, query timeout, threads per client, connect time scale, and think time scale. The details of each of these can be a blog post on its own so I will cover the details next time.  

Configuration of of the XML files gives the added flexibility needed to use Distributed Replay in ways that will change how you test upgrades and do performance testing on OLTP multi-client systems.