In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

In this guide, we are going to walk-through the steps to the setup of transactional replication between cross-platform SQL Servers instances. You heard it right; yes, we are going to setup a cross-platform transactional replication between Windows and Linux machines using a custom solution and an integrated SQL Server transaction log reader.

Objective

As you may know, at the time of writing, replication features are still an unsupported feature and services in SQL Server 2017 on Linux. So, at the moment, replication is not supported on Linux. However, according to the release notes, replication support will be added in a future release. We aren’t going to wait though, as we’re going to create a Linux based replication system for SQL Server now.

We’ll walk-through the steps to setup a cross-platform transactional replication. Let us discuss the following topics in detail:

• Defining Replication
• High-level overview of the cross-platform transactional replication setup
• Describe how to use a transaction log reader to generate CLI commands—generate replay SQL transaction script
• Automate with Batch and PowerShell scripts
• Discuss various job schedule options
• Replay the transactions at the target database (Subscriber)
• Validate the data between the Publisher and Subscriber
• And more…

Replication

Database replication is the process of copying of data from a publisher database from one server to a subscriber database in another so that the data at the subscriber can be transformed at various levels by using the same level of information at the publisher.

In a distributed replication setup, the data is accessed from various data sources or data is fed to various data sources without interfering with the work of one other. In all cases, data replication design is a balancing act between managing system workload, performance, consistency, and integrity. With database replication, the focus is mainly going to be on the database scale out for transactional queries.

Implementation

In this setup, the publisher database is on the SQL Server 2016 on Windows and the subscriber is a SQL Server 2017 on Linux host. The Linux system receives the initial copy using a traditional backup and restores method or using sqlpackage.exe and then periodic updates as the data changes at the source.

Technology

For this configuration, we’ll use ApexSQL Log as our SQL Server transaction log reader of choice, as the lynchpin of our custom replication system

Setup

Let’s not take a deep-dive into the step by step details to setup a cross-platform transactional replication.

1. Let us make sure that the Publisher database is in Full recovery mode
2. Start ApexSQL Log
3. Click New
4. In the Select Database, type in the SQL Server instance and database details

5. Click Next

   

6. In the Select data sources, by default, the online transaction log is enabled. Leave the default setting and Click Next.

   

7. Next, create redo script by selecting Undo/Redo option.

   

8. Now, enable continuous auditing feature. This option maintains transactional integrity by internally remembering the LSN values.

   

9. We are at the final step. We’ve everything setup and configured. Click Save

   

10. We’ll save as replication.ps1

    

11. Edit the replication.ps1 file

    “C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com” /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:c:\replication\redo.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr

12. Convert the batch file to a PowerShell script
    1. We can see that the batch file has two parameters

       • Path of the executable file

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

       • Argument details

         /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:c:\replication\redo.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr

    2. Let us assign the arguments to a variable named $args

       1	$args = '/server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:$filepath /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr'

    3. As we are generating the replay SQL file with a timestamp value, the parameters $filepath is prepared to accept date and timestamp.
    4. The variable $filepath is also passed as a string in the $args.

    5. Call the Invoke-Expression. The call operator is also known as the “invocation operator”, this allows us to run commands that are stored in variables and represented by strings.

       1	"&'C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com' $args" | Invoke-Expression

13. Prepare PowerShell script replication.ps1
    1. This step has load SQL Server module
    2. Execute the ApexSQL CLI commands using Invoke-Expression (Invocation operator)
    3. Manage SQL Server on Linux using PowerShell commands Invoke-SQLCMD

    4. Execute the replay SQL file on the subscription database using Invoke-SQLCMD

       1 2 3 4 5 6 7 8 9 10

       Import-Module SQLServer   $datetime = (get-date).ToString("yyyyMMdd-HHmmss")   $filepath = "c:\replication\redo_$datetime.sql"   $args = '/server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:$filepath /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr' "&'C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com' $args" | Invoke-Expression   Invoke-Sqlcmd -ServerInstance 10.2.6.30 -Username sa -Password thanVitha@2015 -Database "AdventureWorks2016Report" -InputFile $filepath

       The fully converted batch command to replication.ps1 is shown below

       

14. Execute the PowerShell script

    

15. We can see that the redo_<Datetimestamp>.SQL files are generated as we change the data at the source.

    

16. To schedule a job, You can refer the article How to setup SQL Server database replication for a reporting server

17. Validate the data between publisher and Subscriber. In the following T-SQL, replace the <Source> and < LinuxTarget> instances to validate the data.

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

    :CONNECT <source> GO USE  AdventureWorks2016 GO SELECT @@SERVERNAME ServerName SELECT GETDATE() [Datetime] SELECT TOP (1000) [C_ID]       ,[C_Name]       ,[C_StateProvinceID]       ,[C_Location]       ,[C_LatestRecordedPopulation]       ,[C_LastEditedBy] FROM dbo.Cities GO :CONNECT <LinuxTarget> GO USE  AdventureWorks2016Report GO SELECT @@SERVERNAME ServerName SELECT GETDATE() [Datetime] SELECT TOP (1000) [C_ID]       ,[C_Name]       ,[C_StateProvinceID]       ,[C_Location]       ,[C_LatestRecordedPopulation]       ,[C_LastEditedBy] FROM dbo.Cities

    
    

    

Wrap Up

In this article, we looked at a method to implement cross-platform transactional replication with a production database (publisher), a SQL Server instance on a Windows machine. to a target database (subscriber), a SQL Server instance on Linux machine.

It is very simple to setup and configure transactional replication. If you’re thinking of an out-of-the-box solution using 3^rd party tools then ApexSQL Log is a very good option for cross-platform replication. Give a try and let me know what you think in comments…

Table of contents

• Author
• Recent Posts

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

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021