Prashanth Jayaram

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

August 31, 2018 by

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.

So far, we’ve discussed a lot about replication and its internals. There are numerous factors that contribute to the overall configuration of creating and managing database replication. You can refer to the previous article How to setup SQL Server database replication for a reporting server to know more about replication and its internals.

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

    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.

  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

      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.


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 3rd 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

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

Latest posts by Prashanth Jayaram (see all)

294 Views