Prashanth Jayaram

Using transactional data replication to replay and test production loads on a staging server

August 9, 2018 by


In this article, you’ll see how to simulate production loads on a test server with a “record and replay” type situation using the transaction log, batch scripting, PowerShell and a SQL Server agent job.

We’ll be walking through the scenario in the following steps

  1. Record the production load and write the transactions to disk by generating a timestamped replay script
  2. Create a batch file to automate the task at an interval of every 1 minute
  3. Create a SQL Server agent job to schedule the batch file
  4. Replay the production workload to the target/test database by running a PowerShell script to open and execute the scripts at the same interval as they were created, every 1 minute
  5. Validate the data between the source and the target databases to make sure our job works
  6. Monitor the load with a monitoring tool, solution of your choice


In short, transactional replication is a process to transfer uni-directional data from the source (Publisher) database to the target (Subscriber) database. This particular example uses a log reader agent to examine the associated transaction entries in the log files and then those changes can be synchronized immediately with the target database or the synchronization can also be scheduled. The production load can be re-run on the same test database multiple times and run on multiple other test databases as well.

In this article, you’re going to see how to record production loads on a production database and persisting to file, then replaying later on a test database

You can then monitor the performance of the test server, with a tool like Spotlight, to see how it handles the production load

In this method, using ApexSQL Log tool, the batch file is created. The batch file is invoked to generate the redo_<*datetimestamp*>.sql at a frequency of 1 minute and those files archived so they can be consumed and replayed later, perhaps multiple times on multiple servers. Finally, we’ll iterate through the files and process them against the target server, at the same cadence.

Initial requirements

If you followed the previous article and performed all the steps mentioned in them, you should be good to go and it’s going to be really that simple.

  1. Install ApexSQL Log
  2. Select the source database aka Publisher and the target database aka Subscriber. If a copy of the source database hasn’t been provisioned, do so now
  3. Review the basic concepts of batch scripting as needed
  4. Run the PowerShell console with an elevated permission (Run as Administrator)

Configuring our replication agent

Our principal technology is ApexSQL Log, which will do most of the work recording, persisting and even replaying the production load.

What we initially need is a replay script that is the recorded transactions from production.

To generate the redo script, run through the following screens

  1. Open the ApexSQL Log
  2. Click New

  3. Type in, Server and Database details and click Next
  4. In the Select data sources, the online transaction log is enabled by default. Click Next

  5. Select Undo/Redo,

  6. Click Continuous auditing, and click Next

  7. Copy or Save the generated script to the replication.bat file

Automating the replication agent

To automate the job is as simple as using the Batch script feature to save the Windows Shell script to a batch (.BAT) file. That will allow us to rerun this job manually, at will, or schedule it to run unattended.

We’ve created a batch file directly from ApexSQL Log’s Batch script feature, but we’ll want to make a tweak to name the files, dynamically, just as we want them named

  1. First, prepare the dynamic file, the name should be in the format <directory path> followed by redo_yyyy-MM-dd_hhmmss then the SQL extension. For example, g:\DBA\redo_2018-08-10_162233.sql. In this case, the files are created in “g:\DBA” directory with the name “redo_2018-08-10_162233.sql”
  2. To edit the replication.bat batch file, Select the file, right-click, and open in notepad.
  3. The first statement is to assign the redo filename to a variable and pass its value to the /redo switch of ApexSQL Log executable.
  4. The content of the Replication.bat file is given below

Note that carriage returns have been added for readability

Schedule the replication task

Now let us go over the steps to create a SQL job. The SQL job is run every minute to capture the production load into the redo_<datetimestamp>.sql scripts and persist them to file

  1. Schedule a job using SQL Server agent
    1. In the job step
      1. Browse Jobs
      2. Right-click and create a new job

      3. In Steps, type in the Step name, choose Operating system as its type and enter the batch file full name in the command section. In this case, the batch file is created under G:\DBA\.

      4. In Schedules, Enter the schedule name. Select Daily as its frequency of occurrence and run at every 1 minute.

      5. Now, the job is created.
      6. Let us take a look at the redo*.sql path

Replay the previously recorded transaction load

In this section, I’ll take you through the PowerShell script (Replication.ps1) that is used.

It will read through all of our SQL files in the sorted order and execute those files sequentially one after the other with a delay of 60 seconds. In this way, we will simulate the production load on the test database This ensure and preserves the consistency between the transactions.

Prepare the replication

  1. Load the SQL Server module
  2. Get a list of the generated files, run the following command.

  3. The files are listed based on the time of its creation.
  4. Now, sequentially execute the sql files in the order in which they were created with a delay of 60 seconds using Invoke-SQLCMD and Start-Sleep cmdlets. Save the below content in the Powershell script and run the script


Let us verify the data using the following SQL. In this case, the Application.cities table is the used data comparison. Open SSMS, then browse query pane, select SQLCMD mode and run the following SQL.

In the first run, the below sample output gives an overview of the initial data set

Next, manually run the PowerShell Replication.ps1

Now, verify the output. You can see production data on the target database instance because the row counts are the same.

Wrapping Up

In this article, we looked at a method to implement transactional replication for simulating production loads for stress testing a target database. This method will allow you to record once and replay many times, even on different staging servers.

The automation allows for fast and easy integration into any continuous integration pipeline where newly created or provisioned databases can be automatically tested with production loads, simulating real world use. If the load causes the server to fail or generates an excessive number of alerts, when you are monitoring it, that can be an early indicator that the build may have broken something and save you from dealing with problems in production.

Table of contents

SQL Server replication: Overview of components and topography
SQL Replication: Basic setup and configuration
How to Add/Drop articles from existing publications in SQL Server
How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal
SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup
How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases
How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases
How to set up a DDL and DML SQL Server database transactional replication solution
How to setup cross-platform transactional SQL Server replication for database reporting on Linux
SQL Server database migrations with zero data loss and zero downtime
Using transactional data replication to replay and test production loads on a staging server
How to setup SQL Server database replication for a reporting server
SQL Server transactional replication: How to reinitialize a subscription using a “Replication support only” –TBA
SQL Server Replication Monitoring and setting alerts using PowerShell –TBA

Prashanth Jayaram