Types of Replication in SQL Server on Linux

SQL Server Replication enables you to copy and distribute database objects and data to multiple databases distributed across different locations. It allows you to select data or objects and only send the relevant data to a specific replicated database, and data synchronization can happen at regular intervals or continuously. For example, you might require a different subset of data on two different remote servers with continuous data synchronization.

This post will explore the different replication types available in SQL Server on Linux.

Types of Replication in SQL Server 

SQL Server has the following replication types:

• Snapshot replication
• Transaction replication
• Merge replication
• Peer-to-Peer replication

Before moving forward, let’s understand a few terminologies associated with replication.

• Article: The article is a table, stored procedure, and view involved in the replication. We can scale the article horizontally and vertically using a filter option.
• Publication: The publication is a logical collection of articles on the source database. It allows defining and confining article properties at a higher level for replication data flow. The source database is called the publisher database.
• Subscriber: The target database collection consumes SQL Server replication data from a publication. The target database is called the subscriber database. 
• Distributor: The distributor’s role is to distribute data from the publisher database to the subscriber database. It consists of a distributed database (database name – Distribution) that identifies and stores the publication’s replication metadata and status information. You can configure multiple distribution databases, creating a distribution database for each publisher.
• Replication agents: SQL Server replication consists of various replication agents to carry out the associated task. This replication agent uses scheduled SQL Server agent jobs. SQL Server has the following replication agents:
  • Snapshot agent: The Snapshot agent is responsible for initial data synchronization from the publisher to the subscriber database. It generates schema and data for the articles in the snapshot files.
  • Distribution agent: The distribution agent applies the initial snapshot and subsequent data changes to the subscription database. 
  • Log reader agent: The log reader agent moves the transactions from the publisher to the distribution database in the transaction replication.

Replication features in SQL Server on Linux

SQL Server on Linux supports the following replication features:

• Snapshot replication
• Transaction replication

Note: SQL Server on Linux does not support Merge replication and Peer-to-Peer replication. (Reference: Microsoft docs)

The replication supports cross-platform operating system platforms. For example, you can configure replication between SQL Server on Windows and Linux instances. 

Configure Replication in SQL Server on Linux

This section covers the SQL Server snapshot replication on Linux with two instances of SQL Server using Transact-SQL.

Assume we have the following requirements for replication:

• SQL Instance 1: Publisher and distributor
• SQL Instance 2: Subscriber

Step 1: Enable Replication agents on SQL Instances.

The replication uses SQL Server agent jobs for configuring and replication data from Publisher to the subscriber. Therefore, use the following commands to enable the SQL Agent and restart the SQL service. 

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 
sudo systemctl restart mssql-server

Step 2: Create a snapshot folder on the distributor instance.

SQL Server generates the initial snapshot files and stores them on the distributor SQL instance in a snapshot folder. The following code creates a ReplData directory and assigns access to the mssql user. 

sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/

Step 3: Configure the distributor.

The following code configures the distributor on the publisher SQL Server Linux instance. 

DECLARE @distributor AS sysname
DECLARE @distributorlogin AS sysname
DECLARE @distributorpassword AS sysname
-- Specify the distributor name. Use the 'hostname' command on in terminal to find the hostname
SET @distributor = N'<distributor instance name>'--in this example, it will be the name of the Publisher
SET @distributorlogin = N'<distributor login>'
SET @distributorpassword = N'<distributor password>'

-- Specify the distribution database. 

use master
exec sp_adddistributor @distributor = @distributor -- this should be the hostname

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
exec sp_adddistributiondb @database = N'distribution', @log_file_size = 2, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 0, @login = @distributorlogin, @password = @distributorpassword
GO

DECLARE @snapshotdirectory AS nvarchar(500)
SET @snapshotdirectory = N'/var/opt/mssql/data/ReplData/'

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
       create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
       EXEC sp_updateextendedproperty N'SnapshotFolder', @snapshotdirectory, 'user', dbo, 'table', 'UIProperties' 
else 
      EXEC sp_addextendedproperty N'SnapshotFolder', @snapshotdirectory, 'user', dbo, 'table', 'UIProperties'
GO

Step 4: Configure the publisher.

The following t-SQL script configures the publisher database using the snapshot directory created in step 1. 

DECLARE @publisher AS sysname
DECLARE @distributorlogin AS sysname
DECLARE @distributorpassword AS sysname
-- Specify the distributor name. Use the 'hostname' command on in terminal to find the hostname
SET @publisher = N'<instance name>' 
SET @distributorlogin = N'<distributor login>'
SET @distributorpassword = N'<distributor password>'
-- Specify the distribution database. 

-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = @publisher, 
@distribution_db = N'distribution', 
@security_mode = 0, 
@login = @distributorlogin, 
@password = @distributorpassword, 
@working_directory = N'/var/opt/mssql/data/ReplData', 
@trusted = N'false', 
@thirdparty_flag = 0, 
@publisher_type = N'MSSQLSERVER'
GO

Step 5: Configure the publication job.

This step adds the snapshot publication using the stored procedure sp_addpublication and sp_addpublication_snapshot. 

DECLARE @replicationdb AS sysname
DECLARE @publisherlogin AS sysname
DECLARE @publisherpassword AS sysname
SET @replicationdb = N'Sales'
SET @publisherlogin = N'<Publisher login>'
SET @publisherpassword = N'<Publisher Password>'

use [Sales]
exec sp_replicationdboption @dbname = N'Sales', @optname = N'publish', @value = N'true'

-- Add the snapshot publication
exec sp_addpublication 
@publication = N'SnapshotRepl', 
@description = N'Snapshot publication of database ''Sales'' from Publisher ''<PUBLISHER HOSTNAME>''.',
@retention = 0, 
@allow_push = N'true', 
@repl_freq = N'snapshot', 
@status = N'active', 
@independent_agent = N'true'

exec sp_addpublication_snapshot @publication = N'SnapshotRepl', 
@frequency_type = 1, 
@frequency_interval = 1, 
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 8, 
@frequency_subday_interval = 1, 
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@publisher_security_mode = 0, 
@publisher_login = @publisherlogin, 
@publisher_password = @publisherpassword

Step 6: Add an article to the publication.

This step adds the customer table in the publication for the snapshot. 

use [Sales]
exec sp_addarticle 
@publication = N'SnapshotRepl', 
@article = N'customer', 
@source_owner = N'dbo', 
@source_object = N'customer', 
@type = N'logbased', 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509D,
@identityrangemanagementoption = N'manual', 
@destination_table = N'customer', 
@destination_owner = N'dbo', 
@vertical_partition = N'false'

Step 7: Configure the subscription.

This step adds a subscription to the configured publisher database. 

DECLARE @subscriber AS sysname
DECLARE @subscriber_db AS sysname
DECLARE @subscriberLogin AS sysname
DECLARE @subscriberPassword AS sysname
SET @subscriber = N'<Instance Name>' -- for example, MSSQLSERVER
SET @subscriber_db = N'Sales'
SET @subscriberLogin = N'<Subscriber Login>'
SET @subscriberPassword = N'<Subscriber Password>'

use [Sales]
exec sp_addsubscription 
@publication = N'SnapshotRepl', 
@subscriber = @subscriber,
@destination_db = @subscriber_db, 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0

exec sp_addpushsubscription_agent 
@publication = N'SnapshotRepl', 
@subscriber = @subscriber,
@subscriber_db = @subscriber_db, 
@subscriber_security_mode = 0, 
@subscriber_login = @subscriberLogin,
@subscriber_password = @subscriberPassword,
@frequency_type = 1,
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 0, 
@active_start_date = 0, 
@active_end_date = 19950101
GO

Step 8: Start SQL agent jobs.

Once we have configured both publication and subscriber, we need to start the jobs for generating the snapshot and distribute it to the subscriber.

USE msdb;   
--generate a snapshot of publications, for example
EXEC dbo.sp_start_job N'PUBLISHER-PUBLICATION-SnapshotRepl-1'
GO
--distribute the publication to the subscriber, for example
EXEC dbo.sp_start_job N'DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER'
GO

Step 9: Compare article on the publisher and subscriber databases.

At this point, you can connect to both the publisher and subscriber databases to check the articles that are part of the replication. 

Benefits of SQL Server Replication

• The replication can transfer a whole or a subset of data from the publisher to the subscriber.
• You can filter data and columns to transfer only relevant articles. 
• You can connect and query the subscriber database. 
• The replicated database availability does not depend on the publisher database. You can still connect to the subscriber database if the publisher database is unavailable. 
• You can use both SQL Server standard and enterprise edition for it.
• You can combine Windows and Linux SQL instances for cross-platform database replication. 

SQL Server Replication Drawbacks

• The replication process is not suitable for disaster recovery.
• It requires a primary key on all tables in the publisher database.
• The initial data synchronization might be resource and time-consuming. 

Wrapping Up

SQL Server on Linux supports snapshot and transaction replication and can replicate data in a one-way direction from the publisher to the subscriber. It’s great for transferring a whole or subset of data from the publisher to the subscriber.

SQL Server replication isn’t, however, a good option for disaster recovery. In that case, check out this article on setting up Log Shipping in SQL Server as a high availability and disaster recovery solution.

For more updates on SQL Server best practices, follow us on Twitter and LinkedIn and subscribe to our newsletter.