Prashanth Jayaram

How to set up a DDL and DML SQL Server database transactional replication solution

August 22, 2018 by

In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.

As we know, transactional replication propagates changes according to transaction boundaries and transactions from the publisher database can be replicated to the one or more subscription databases. There are many reasons why you want to look for alternatives from so-called “native” replication in SQL Server including:

  1. Transactional replication requires a primary key which is mandatory to publish an article
  2. The snapshot agent acquires locks on articles during the initial stage of snapshot generation
  3. In some cases, the size of the transaction-log affects the performance of the replication
  4. Performing maintenance tasks can be “daunting”
  5. To add or drop an article from the publisher or subscriber is simple if the setup and configuration are simple. When you’ve got multiple publishers and/or subscribers, adding and dropping of articles becomes more difficult
  6. Consider a scenario where the replication is setup using a backup, adding an article to replication is not a straight-forward approach.

Alternatives

In this example, we are going to use some 3rd party tools for DDL and DML replication as an alternative to traditional replication. After creating the jobs to replicate the data and schema changes we’ll automate them with a combination of a batch file and PowerShell scripting and then schedule a job to run at every 30 seconds using a SQL Server Agent job. This can be done in two ways which we’ll cover in two parts of this article.

  1. Use a SQL Server transaction log reader to replicate both Schema (DDL) and Data (DML) changes
  2. Use a SQL Server schema compare tool for Schema (DDL) changes and a SQL Server transaction log reader for Data (DML) changes

Using a SQL Server transaction log reader

In this section, we will walk-through the steps required to setup dynamic transactional replication using just a SQL Server transaction log reader for both DDL and DML transactions.

In general, when adding new and/or changing existing table(s) in the source database in any replicated system, the changes must be synchronized to the Subscriber database.

For this task we’ll use ApexSQL Log, a SQL Server transaction log reader, to act as our DDL and DML replication agent. We’ll create a job to read the transaction log and write it to a script, then a batch file and a PowerShell script to automate the job. We will simulate synchronous data transfers between publisher and subscriber with latency as close to 30 seconds.

Here is a schematic view of our setup in which we’ll use a batch file and a PowerShell script, in combination, to poll a SQL Server, on a schedule and create a script to replay both DDL (schema) and DML (data) scripts that we’ll replay on the publisher database

Reading the Publisher transaction log

To begin, let’s set up ApexSQL Log to read the publisher database with the following steps.

  1. Open ApexSQL Log
  2. Click New and connect to the source database, WideWorldImporters

  3. Use the default configuration, ApexSQL Log to use the Online log

  4. Now, create Undo/Redo script

  5. Next, in the filter setup window, click Time range, select the Continuous auditing option; this will automatically manage the LSN sequences

  6. In the operations tab, on your right, in the DDL pane, Click Alter Table and Create Table and then click Next

    In this option, we are going configure filters such as Alter Table and Create Table. These two are the exact operations will be included to capture DDL changes in the auditing task

Automation

Now that we’ve created a project to read the transaction log as per our specifications, let’s create a batch file to run this task unattended via the ApexSQL Log console application and command line interface (CLI).

  1. Let’s save the content into a batch (.BAT) file

  2. Now, save the batch script content as replication.bat
  3. Next, edit the replication.bat to accept a filename as its parameters as shown below

    @echo off

    SET “filename=%1”

    “E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com”

    /server:HQDBT01 /database:WideWorldImporters /trusted

    /redo:%filename%

    /operations:DMLALL CREATETABLE ALTERTABLE /transactions:COMMIT BEGIN UNKNOWN

    /continuous:G:\Replication\WideWorldImporters.axtr

That’s the entire configuration we need to setup a highly enhanced transactional replication model for both DDL and DML changes/additions.

As we plan to automate the schema and data export task, a PowerShell script is prepared. It helps to gather all the command in one place and it is possible to manipulate the way we want and use it more than once in its entirety.

Now, save the content in a replication.PS1. The script tasks include the following four steps:

  1. Load SQL Server
  2. Invoke batch file replication.bat
  3. Generate a SQL file, replication_<*datetimestamp*>.sql
  4. Replay the generated SQL file into the subscriber database

Scheduling

Now let us review the steps to create SQL Server Agent job that runs every 30 seconds to generate the data from the source and insert into the target using our PowerShell script. To do this, create the job steps through SSMS. Script the job and edit parameters.

Let us see the steps on how to schedule a job to run at every 30 seconds.

  1. Browse SQL Server Agent and locate Jobs folder
  2. Right-click and select New
  3. Type in the job Name
  4. Uncheck the Enabled option

  5. In the job step
    1. Type in the Step name
    2. Select the PowerShell
    3. In the command, enter the full path. If the path to the script contains a space, enclose the full path in quotation marks and prefix the entire thing with an ampersand (for example: &”G:\DBA\ApexSQL Log\Replication.ps1″)

  6. In the Schedule
    1. In the Frequency section, choose Daily
    2. In the Daily frequency section, choose to run every 1 minutes( depends on your requirement)
    3. Click Ok
  7. Job is created

  8. Right-click and generate the “Drop And Create To” script

  9. In the generated script, search for schedule section, change the value of @freq_subday_type from ‘4’ to ‘2’ and @freq_subday_interval from ‘1’ to ‘30’ as shown below

  10. Execute the script
  11. Now, we can see that Daily frequency is scheduled to run at every 30 seconds

  12. Enable the job
  13. The replication job is all set and we see that the job ran at every 30 seconds

Verifying results

Create a sample table at the source database and run the aforementioned SQL to validate the table and number of rows.

Next, insert some sample data into the ExportCitiesDemo table and measure the output. The output errors out due to the absence of the table at the target.

Test the data. You can see that the tables, as well as rows, are populated at the target.


As you can see both the new table and all of the new data have been successfully replicated.

Wrapping Up

This article is an effort to show how easy it is to set up a fully enhanced version of SQL Server database transactional Replication using ApexSQL Log. The replication job is scheduled to run at every 30 seconds. The data propagation is tested between Publisher and Subscriber.

After working with SQL Server for such a long time, I feel it is very difficult to simulate a near-production database with the available SQL Server native techniques. If you’re thinking of out-of-the-box solution with no coding, except for a couple lines of PowerShell, you should consider this automation to create effective replication model.

Using a schema comparison tool for DDL replication

Now that we’ve shown how to do this using just ApexSQL Log, in this section, we will walk-through the steps required to setup a dynamic transactional replication using ApexSQL Diff in combination with ApexSQL Log for schema and data replication respectively.

Add new table(s), alter the existing objects in the source database in any replicated system, these changes must be synchronized to the Subscriber database. To do that you need to perform a schema comparison.

For this, we’ll use ApexSQL Diff, a tool used to compare and synchronize schema differences between source and target databases or in our case, Publisher and Subscriber databases. Using ApexSQL Diff, schema changes that are made in the source database are captured and compared with the target. It is very easy to generate CLI commands and integrate the resulting batch (.bat) file with PowerShell to synchronize the schemas.

We’ll still use ApexSQL Log, a SQL Server transaction log reader, to act as our DML replication agent. We’ll create a job to read the transaction log and write it to a script, then a batch file and a PowerShell script to automate the job.

Here is a schematic view of our setup in which we’ll use a batch file and a PowerShell script, in combination, to poll a SQL Server, on a schedule and create a script to replay both DDL (schema) and DML (data) scripts that we’ll replay on the publisher database

Schema change replication

To replicate schema changes with ApexSQL Diff, follow the steps:

  1. Open ApexSQL Diff
  2. Click New

  3. In the Data sources tab, type in the data source and destination details

  4. In the object filters, by default, all objects will be checked. By using filters, we have an option to narrow down comparison. Just check the filters on Difference, Missing and Additional options to list only the affected objects

  5. After setting up the needed options, click the save dropdown option and click “Save as batch
  6. Copy or save the CLI command into a batch file

  7. Prepare the batch file. Now, pass the filename as parameter to the output name switch

    @echo off

    SET “filename=%1”

    “E:\Program Files\ApexSQL\ApexSQL Diff\ApexSQLDiff.com”

    /server1:”HQDBT01″ /database1:”WideWorldImporters” /server2:”HQDBT01\SQL2017″ /database2:”WideWorldImporters”

    /ignore_general:iws ic iso idep iio /ignore_table_attributes:ico ifc /struct_sync_options:iddo ith sud /encoding:Unicode

    /output_elements:cmt ps /output_type:sql /output_name:%filename%

    /force /verbose

    @echo ApexSQL Diff return code: %errorlevel%

Data replication

The part of our task is now successfully created. Next, generate the job to replicate the data changes. The only difference is that this time we’ll exclude DDL auditing by having only DMLALL as a variable in the /operations switch.

@echo off

SET “filename=%1”

“E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com”

/server:HQDBT01 /database:WideWorldImporters /trusted

/redo:%filename%

/operations:DMLALL

/transactions:COMMIT BEGIN UNKNOWN /continuous:G:\DBA\ApexSQLLog\WideWorldImporters.axtr

That’s the entire configuration we need to setup a highly enhanced transactional replication model for both DDL and DML changes/additions

Automation

As we plan to automate the synchronization and data export task, PowerShell script is prepared. It helps to gather all the command in one place and it is possible to manipulate the way we want and use it more than once in its entirety.

Next, save the below content in a replication.PS1 file. The script has following tasks:

  1. Load the SQL Server module
  2. ApexSQL Diff batch file is invoked to generate a DDLreplication_<*datetimestamp*>.sql file
  3. ApexSQL Log batch file is invoked to generate a DMLreplication_<*datetimestamp*>.sql
  4. Now, we’ve two sets of the input file to execute based on the input flag
  5. Invoke SQL files to run at the subscriber database using the Invoke-SQLCMD cmdlet

Then this script can be scheduled the same way as the previous use case.

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

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
385 Views