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.
- Let us make sure that the Publisher database is in Full recovery mode
- Start ApexSQL Log
- Click New
- In the Select Database, type in the SQL Server instance and database details
-
Click Next
-
In the Select data sources, by default, the online transaction log is enabled. Leave the default setting and Click Next.
-
Next, create redo script by selecting Undo/Redo option.
-
Now, enable continuous auditing feature. This option maintains transactional integrity by internally remembering the LSN values.
-
We are at the final step. We’ve everything setup and configured. Click Save
-
We’ll save as replication.ps1
-
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
-
Convert the batch file to a PowerShell script
-
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
-
-
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' - As we are generating the replay SQL file with a timestamp value, the parameters $filepath is prepared to accept date and timestamp.
- The variable $filepath is also passed as a string in the $args.
-
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
-
We can see that the batch file has two parameters
-
Prepare PowerShell script replication.ps1
- This step has load SQL Server module
- Execute the ApexSQL CLI commands using Invoke-Expression (Invocation operator)
- Manage SQL Server on Linux using PowerShell commands Invoke-SQLCMD
-
Execute the replay SQL file on the subscription database using Invoke-SQLCMD
12345678910Import-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-ExpressionInvoke-Sqlcmd -ServerInstance 10.2.6.30 -Username sa -Password thanVitha@2015 -Database "AdventureWorks2016Report" -InputFile $filepathThe fully converted batch command to replication.ps1 is shown below
-
Execute the PowerShell script
-
We can see that the redo_<Datetimestamp>.SQL files are generated as we change the data at the source.
- To schedule a job, You can refer the article How to setup SQL Server database replication for a reporting server
-
Validate the data between publisher and Subscriber. In the following T-SQL, replace the <Source> and < LinuxTarget> instances to validate the data.
123456789101112131415161718192021222324252627282930:CONNECT <source>GOUSE AdventureWorks2016GOSELECT @@SERVERNAME ServerNameSELECT GETDATE() [Datetime]SELECT TOP (1000) [C_ID],[C_Name],[C_StateProvinceID],[C_Location],[C_LatestRecordedPopulation],[C_LastEditedBy]FROM dbo.CitiesGO:CONNECT <LinuxTarget>GOUSE AdventureWorks2016ReportGOSELECT @@SERVERNAME ServerNameSELECT 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 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
- 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
- How to perform Azure SQL database Import/Export operations using PowerShell - January 14, 2021