Distributed Replay Architecture
The diagram in Figure 21-1 gives you an overview of the Distributed Replay components
and how the replay process works across the preprocess and replay phases. In the
preprocess phase, an intermediate file is created in the working directory of the
controller. In the replay phase, dispatch files are created at the clients before the events
are dispatched to the target.
Intermediate file
Pre-process Phase
Replay Phase
Controller
Clients (1..16)
Target Instance
Dispatch file
Figure 21-1.  Distributed Replay architecture
Configuring the Environment
Before we begin to replay traces, we need to configure the Distributed Replay controller,
the Distributed Replay clients, and the Distributed Replay Administration Tool. These
activities are discussed in the following sections.

Configuring the Controller
You can find the DReplayController.config file within the Tools\DReplayController
folder, which resides in the 32-bit shared directory of SQL Server. Therefore, if SQL
Server is installed with default paths, the fully qualified path is C:\Program Files
(x86)\Microsoft SQL Server\150\Tools\DReplayController\DReplayController.
config. You can use this configuration file to control the logging level. These are the
possible options:
•
INFORMATIONAL: Logs all messages to the controller log.
•
WARNINGS: Filters out informational messages, but logs all errors and
warnings.
•
CRITICAL: Logs only critical errors. This is the default value.
The default contents of the DReplayController.config file are shown in Listing 21-­1.
Listing 21-1.  DReplayController.config
<?xml version='1.0'?>
<Options>
    <LoggingLevel>CRITICAL</LoggingLevel>
</Options>
Because the service pulls this logging level from the configuration file at the point
when the service starts, if you change the logging level after starting the service, then you
need to restart the service. The log itself is created when the service starts, and it includes
startup information, such as the service account being used. You can find the log in the DReplayController\Log folder named DReplay Controller Log_<Uniquifier>. A new
log is generated each time the service starts.
If you are running the Distributed Replay Client service under a different service
account than the Distributed Replay Controller service, then you also need to configure
DCOM (Distributed Component Object Model) permissions on the Distributed Replay
Controller service. You can do this via Component Services, which can be found in
Administrative Tools in Windows.
Once Component Services has been invoked, you need to drill through Console Root
➤ Component Services ➤ Computers ➤ My Computer ➤ DCOM Config and then select
Properties from the DReplayController context menu. This causes the Properties dialog
box to be invoked. From here, you should navigate to the Security tab.

Now use the Edit button in the Launch and Activation Permissions section to launch
the Permissions dialog box. Here, use the Add button to add the service account of the
Distributed Replay Client service before granting it the Local Activation and Remote
Activation privileges.
You now need to repeat this process in the Access Permissions section to grant the
service account the Local Access and Remote Access privileges.
You also need to ensure that the Distributed Replay Client service account is added
to the Distributed COM Users Windows Group on the server running the Distributed
Replay Controller. Once you have applied these changes, you need to restart both the
Distributed Replay Controller service and the Distributed Replay Client service(s) for the
changes to take effect.
Tip  In regard to firewall configuration, the controller and the client communicate
using port 135 and dynamic ports; therefore, you must ensure that these are
open between the servers. Opening the dynamic port range can violate some
organization’s security best practice, however. The workaround is to configure
Windows Firewall so it allows the Distributed Replay executables to communicate
through any port. This poses its own issues, however, because some corporate
firewalls are not configured to offer this functionality, meaning that even though
Windows Firewall does allow the traffic through, the packets may be dropped at
the corporate firewall level.
Configuring Clients
You can find the DReplayClient.config file in the Tools\DReplayClient folder,
which resides in the 32-bit shared directory of SQL Server. Therefore, if SQL Server is
installed with default paths, the fully qualified path would be C:\Program Files (x86)Microsoft SQL Server\150\Tools\DReplayController\DReplayClient.config. You
can use this configuration file to configure the settings detailed in Table 21-1.

Listing 21-2 displays an edited version of the DReplayClient.config file, which has
been configured to point to our controller server and the Working and Results folders.
Listing 21-2.  DReplayClient.config
<?xml version="1.0" encoding="utf-8"?>
<Options>
  <Controller>controller</Controller>
  <WorkingDirectory>C:\DistributedReplay\WorkingDir\</WorkingDirectory>
  <ResultDirectory>C:\DistributedReplay\ResultDir\</ResultDirectory>
  <LoggingLevel>CRITICAL</LoggingLevel>
</Options>
Tip  You must start the Distributed Replay Controller service before you start the
Distributed Replay Client service on each client.
Table 21-1.  DReplayClient.config Options
Option
Description
Controller
The name of the server hosting the Distributed Replay Controller. It is
important to note that this is the server name, not the server\instance
name.
WorkingDirectory
The location on the client where dispatch files are stored. If this option
is not included in the configuration file, then the folder where the
configuration file is stored is used.
ResultsDirectory
The location where the results file from the replay is stored. If this
option is not specified in the configuration file, then the folder that the
configuration file is stored in is used.
Logging
Use this option to control the logging level. The possible options are
•  INFORMATIONAL, which logs all messages to the controller log.
•  WARNINGS, which filters out informational messages, but logs all
errors and warnings.
•  CRITICAL, which logs only critical errors. This is the default value.

Because we are using two clients, we need to repeat these activities on each. In our
environment, these clients are the servers, Client1 and Client2.
Tip  In regard to firewall configuration, the client and the target communicate
using the SQL Server ports. In a standard configuration, this means using TCP
1433 and UDP 1434 for the browser service, if you are using a named instance.
If you are using nonstandard ports, configure the firewall accordingly.
Configuring the Replay
The replay is started using the Administration Tool, which relies on two configuration
files, DReplay.exe.preprocess.config and DReplay.exe.replay.config. The first of
these configuration files controls the building of the intermediate files and the second
controls replay options and output options. Both files are located within the 32-bit
shared directory, so with a default installation, the fully qualified paths are C:\Program
Files (x86)\Microsoft SQL Server\150\Tools\Binn\DReplay.exe.preprocess.
config and C:\Program Files (x86)\Microsoft SQL Server\150\Tools\BinnDReplay.exe.replay.config, respectively. Table 21-2 details the options you can
configure in DReplay.exe.preprocess.config.
Table 21-2.  DReplay.exe.preprocess.config Options
Option
Description
IncSystemSession
Specifies if activity captured from system sessions should be included
in the replay.
MaxIdleTime
Specifies a limit for the amount of idle time in seconds.
•  -1 specifies that the idle time between activities should be the
same as the original trace.
•  0 specifies that there should be no idle time between activities.
The default contents of the DReplay.exe.preprocess.config file are shown in
Listing 21-3.

Listing 21-3.  DReplay.exe.preprocess.config
<?xml version="1.0" encoding="utf-8"?>
<Options>
    <PreprocessModifiers>
        <IncSystemSession>No</IncSystemSession>
        <MaxIdleTime>-1</MaxIdleTime>
    </PreprocessModifiers>
</Options>
Table 21-3 details the options you can configure within ­DReplay.exe.replay.config.
Table 21-3.  DReplay.exe.replay.config Options
Option
Category
Description
Server
Replay options
The server\instance name of the target server.
SequencingMode
Replay options
Specifies the mode to be used for scheduling
events. The possible options are
•  Synchronization, which indicates that the
order of transactions is subject to time-
based synchronization, across the clients,
which is useful for performance testing.
•  Stress, which is the default option, indicates
that transactions are fired as quickly as
possible, without time-based synchronization.
This is useful for load testing.
StressScaleGranularity
Replay options
When the SequenceMode is set to Stress,
StressScaleGranularity determines how to
scale activity on a SPID.
•  SPID indicates that connections on a
single SPID should be scaled as if they
were a single SPID.
•  Connection indicates that connections
on a single SPID should be scaled as if
they were separate connections.
(continued)

Table 21-3.  (continued)
Option
Category
Description
ConnectTimeScale
Replay options
A percentage value that indicates if connection
time should be reduced during the replay when
the SequenceMode is Stress. 100 indicates
100% of connection time is included. Lower
values reduce the simulated connection times
accordingly.
ThinkTimeScale
Replay options
A percentage value that indicates if the user
thinks time should be reduced during the replay
when SequenceMode is Stress. 100 indicates
100% of think time is included, so transactions
replay at the speed they were captured.
Specifying lower values reduces the interval.
UseConnectionPooling
Replay options
Specifies if connection pooling should be used
on the clients. A connection pool is a cache of
connections that subsequent connection can
reuse.
HealthmonInterval
Replay options
When SequenceMode is set to
Synchronization, HealthmonInterval
determines how often the health monitor runs,
specified in seconds. -1 indicates that the health
monitor is disabled.
QueryTimeout
Replay options
Specifies the query timeout value in seconds.
-1 indicates that it is disabled.
ThreadsPerClient
Replay options
Specifies the number of threads to use for the
replay on each client.
RecordRowCount
Output options
Specifies if you should include a row count for
each result set.
RecordResultSet
Output options
Specifies if you should save the contents of each
record set.

Listing 21-4 shows an example of the DReplay.exe.replay.config file, which
has been modified for our environment to allow us to perform a performance test by
simulating multiple connections.
Listing 21-4.  DReplay.exe.replay.config
<?xml version="1.0" encoding="utf-8"?>
<Options>
    <ReplayOptions>
        <Server>Target</Server>
        <SequencingMode>synchronization</SequencingMode>
        <HealthmonInterval>60</HealthmonInterval>
        <QueryTimeout>3600</QueryTimeout>
        <ThreadsPerClient>255</ThreadsPerClient>
        <EnableConnectionPooling>No</EnableConnectionPooling>
     </ReplayOptions>
    <OutputOptions>
        <ResultTrace>
            <RecordRowCount>Yes</RecordRowCount>
            <RecordResultSet>No</RecordResultSet>
        </ResultTrace>
    </OutputOptions>
</Options>
Working with Distributed Replay
Now that the Distributed Replay utility is configured, we create a trace using Extended
Events before we synchronize the target. We then use Distributed Replay to replay the
trace in order to test performance tweaks, simulating concurrent activity on the two
clients. Before we do this, however, we create the Chapter21 database on the controller
using the script in Listing 21-5.

Listing 21-5.  Creating the Chapter21 Database
--Create the database
CREATE DATABASE Chapter21 ;
GO
USE Chapter21
GO
--Create and populate numbers table
DECLARE @Numbers TABLE
(
        Number        INT
)
;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;
--Create and populate name pieces
DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
);

INSERT INTO @Names
VALUES('Peter', 'Carter'),
                 ('Michael', 'Smith'),
                 ('Danielle', 'Mead'),
                 ('Reuben', 'Roberts'),
                 ('Iris', 'Jones'),
                 ('Sylvia', 'Davies'),
                 ('Finola', 'Wright'),
                 ('Edward', 'James'),
                 ('Marie', 'Andrews'),
                 ('Jennifer', 'Abraham');
--Create and populate Addresses table
CREATE TABLE dbo.Addresses
(
AddressID        INT        NOT NULL        IDENTITY        PRIMARY KEY,
AddressLine1        NVARCHAR(50),
AddressLine2        NVARCHAR(50),
AddressLine3        NVARCHAR(50),
PostCode        NCHAR(8)
) ;
INSERT INTO dbo.Addresses
VALUES('1 Carter Drive', 'Hedge End', 'Southampton', 'SO32 6GH')
        ,('10 Apress Way', NULL, 'London', 'WC10 2FG')
        ,('12 SQL Street', 'Botley', 'Southampton', 'SO32 8RT')
        ,('19 Springer Way', NULL, 'London', 'EC1 5GG') ;
--Create and populate Customers table

CREATE TABLE dbo.Customers
(
     CustomerID            INT          NOT NULL   IDENTITY   PRIMARY KEY,
     FirstName             VARCHAR(30)  NOT NULL,
     LastName              VARCHAR(30)  NOT NULL,
     BillingAddressID      INT          NOT NULL,
     DeliveryAddressID     INT          NOT NULL,
     CreditLimit           MONEY        NOT NULL,
     Balance               MONEY        NOT NULL
);
SELECT * INTO #Customers
FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID())
FirstName,
                 (SELECT TOP 1 LastName FROM @Names ORDER BY NEWID())
LastName,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID())
BillingAddressID,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID())
DeliveryAddressID,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
                  FROM @Numbers ORDER BY NEWID()) CreditLimit,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
                  FROM @Numbers ORDER BY NEWID()) Balance
        FROM @Numbers a
        CROSS JOIN @Numbers b
        CROSS JOIN @Numbers c
) a;
INSERT INTO dbo.Customers
SELECT * FROM #Customers;
GO