DEA 2.0 – Configuring SQL Server Distributed Replay environment

Database Experimentation Assistant utilizes the Distributed Replay tools from the SQL Server installation. We recommend doing a test run with a small trace file before doing a full fledged replay to ensure proper replay of queries.

Setup a virtual network or domain

Distributed replay requires usage of common accounts between machines. For this and security reasons, we recommend running under a virtual network or a domain controlled network.

Create a Controller machine and Client machine(s) in the environment.

Ensure that Controller and Client machine(s) can ping each other over the network.

Distributed Replay client machines must have connectivity to the replay target SQL server.

Setup Controller service

Install Distributed Replay Controller using the SQL Server installer.

In case you missed the SQL Server Installer wizard step to configure the Distributed Replay Controller, configure it through the configuration file. For a typical installation, it can be found at C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\DReplayController\DReplayController.config

DCOM Configuration

Find DReplayController under DCOM Config. Right-click and open its Properties.

Click on the Security tab.

Under Launch and Activation Permissions, select Customize and click Edit.

Add the user that will be starting the replay and give the user Local Launch and Local Activation permissions. If the user plans to launch or activate remotely, give the user Remote Launch and Remote Activation permissions also.

Click OK to commit the changes and go back to the Security tab on the Properties window.

Under Access Permissions, select Customize and click Edit.

Add the user that will be starting the replay and give the user Local Access permission. If the user plans to access the Controller service remotely, give the user Remote Access permission also.

Click OK to commit the changes and back to the Security tab on the Properties window.

Click OK to commit the changes.

Restart the service SQL Server Distributed Replay Controller from Services.msc or run from the command prompt, the following commands:

NET STOP "SQL Server Distributed Replay Controller"

NET START "SQL Server Distributed Replay Controller"

Setup Client service

Before performing this step, verify that the Controller and Client machines can see each other using networking tools such as ping.

Install Distributed Replay Client using the SQL Server installer.

Open Services.msc and navigate to the service SQL Server Distributed Replay Client.

Right click on the service > Click Properties > Set the service account to an account that is common to the Controller and Client machines within the network.

Click OK to close the Properties window.

In case you missed the SQL Server Installer wizard step to configure the Distributed Replay Client, configure it through the configuration file. For a typical installation, it can be found at C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\DReplayClient\DReplayClient.config

Ensure that the DReplayClient.config contains the name of the Controller machine as its controller for registration.

Restart the service SQL Server Distributed Replay Client from Services.msc or run from the command prompt, the following commands:

NET STOP "SQL Server Distributed Replay Client"

NET START "SQL Server Distributed Replay Client"

Distributed Replay Controller logs are present at C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\DReplayClient\Log. The log will indicate whether the client is able to register itself with the controller.

If the configuration is successful, the log will show the message, "Registered with controller <controller name>".

Setup Distributed Replay Administration tools

DReplay administration tool will allow you to quickly test whether Distributed Replay is functioning properly in the environment, especially an environment in which multiple Client machines are registered with a Controller. SQL Server Management Studio may need to be installed to get the administration tool.

Go to the SSMS install location and look for the Distributed Replay administration tools, namely dreplay.exe and its dependent components.

Open a command prompt, and run dreplay.exe status -f 1

If all the previous steps are successful, the console output will indicate that the Controller is able to see its Clients in READY state.

Firewall configuration for Remote Distributed Replay access

Remotely accessing Distributed Replay requires opening ports that are visible within the domain or virtual network.

Open the Windows Firewall with Advanced Security

Navigate to Inbound Rules.

Create a new inbound firewall rule for program C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\DReplayController\DReplayController.exe.

Allow Domain level access to all ports for DReplayController.exe to be able to communicate with the Controller service remotely.

Save the rule.

Setting up Target SQL Servers

Two Replays are required for performing an A/B test or an experiment. That is, you may need two separate instances of SQL Server installations for a migration scenario.

Installing the two versions of SQL Server instances, respectively, on the same machine will also work. One caveat though is to make sure that the instances are isolated completely when a Replay is in progress.

The following steps must be performed for each Replay.

Restore the back up of the database.

Provide permission to the Client service account user to access the databases under the SQL Server instance. This is required for the queries to be executed on the SQL Server instance.