SQL Server Replication on Linux

SQL Server 2017 (14.x) (CU18) and later support SQL Server Replication for instances of SQL Server on Linux.

Configure replication on Linux with SQL Server Management Studio (SSMS) replication stored procedures.

An instance of SQL Server can participate in any replication role:

  • Publisher
  • Distributor
  • Subscriber

A replication schema can mix and match operating system platforms. For example, a replication schema may include an instance of SQL Server on Linux for publisher and distributor, and the subscribers include instances of SQL Server on Windows as well as Linux.

SQL Server instances on Linux can participate in any type of replication.

  • Transactional
  • Snapshot

For detailed information about replication, see SQL Server replication documentation.

Supported features

The following replication features are supported:

  • Snapshot replication
  • Transactional replication
  • Replication with non-default ports
  • Replication with AD authentication
  • Replication configurations across Windows and Linux
  • Immediate updates for transactional replication

Limitations

The following features are not supported:

  • Merge replication
  • Peer-to-Peer replication
  • Oracle publishing

Configure SQL Server Replication on Linux

SQL Server 2019 (15.x) introduces SQL Server Replication for instances of SQL Server on Linux.

Configure replication on Linux with either SQL Server Management Studio (SSMS) or Transact-SQL stored procedures.

  • To use SSMS, follow the instructions in this article.

Use SSMS on a Windows operating system to connect to instances of SQL Server.

Prerequisites

Before configuring publishers, distributors, and subscribers, you need to complete a couple configuration steps for the SQL Server instance.

  1. Enable SQL Server Agent to use replication agents. On all Linux servers, run the following commands in the terminal.

Bash

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

sudo systemctl restart mssql-server

  • Configure the SQL Server instance for replication. To configure the SQL Server instance for replication, run sys.sp_MSrepl_createdatatypemappings on all instances participating in replication.

SQL

USE msdb

GO

exec sys.sp_MSrepl_createdatatypemappings;

GO

  • Create a snapshot folder. The SQL Server agents require a snapshot folder to read/write to. Create the snapshot folder on the distributor.

To create the snapshot folder, and grant access to mssql user, run the following command:

Bash

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

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

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

Configure and monitor replication with SQL Server Management Studio (SSMS)

Configure the distributor

To configure the distributor:

  1. On SSMS connect to your instance of SQL Server in Object Explorer.
  2. Right-click Replication, and click Configure Distribution....
  3. Follow the instructions on the Configure Distribution Wizard.

Create publication and articles

To create a publication and articles:

  1. In Object Explorer, click Replication > Local Publications> New Publication....
  2. Follow the instruction on the New Publication Wizard to configure the type of replication, and the articles that belong to the publication.

Configure the subscription

To configure the subscription in Object Explorer, click Replication > Local Subscriptions> New subscriptions....

Monitor replication jobs

Use Replication Monitor to monitor replication jobs.

In Object Explorer, right-click Replication, and click Launch Replication Monitor.

Configure replication snapshot folder with shares

The snapshot folder is a directory that you have designated as a share; agents that read from and write to this folder must have enough permissions to access it.

Replication Snapshot Folder Share Explained

Before the examples, let’s walk through how SQL Server uses samba shares in replication. Below is a basic example of how this works.

  1. Samba shares are configured that files written to /local/path1 by the replication agents on publisher can be seen by the subscriber
  2. SQL Server is configured to use share paths when setting up the publisher on the distribution server such that all instances would look at the //share/path
  3. SQL Server finds the local path from the //share/path to know where to look for the files
  4. SQL Server reads/writes to local paths backed by a samba share

Configure a samba share for the snapshot folder

Replication agents will need a shared directory between replication hosts to access snapshot folders on other machines. For example, in transactional pull replication, the distribution agent resides on the subscriber, which requires access to the distributor to get articles. In this section, we’ll go through an example of how to configure a samba share on two replication hosts.

Steps

As an example, we will configure a snapshot folder on Host 1 (the distributor) to be shared with Host 2 (the subscriber) using Samba.

Install and start Samba on both machines

On Ubuntu:

Bash

sudo apt-get -y install samba

sudo service smbd restart

On RHEL:

Bash

sudo yum install samba

sudo service smb start

sudo service smb status

On Host 1 (Distributor) Set-up the Samba share

  1. Set-up user and password for samba:

Bash

sudo smbpasswd -a mssql

  • Edit the /etc/samba/smb.conf to include the following entry and fill in the share_name and path fields

Bash

 <[share_name]>

 path = </local/path/on/host/1>

 writable = yes

 create mask = 770

 directory mask

 valid users = mssql

Example

Bash

[mssql_data]    <- Name of the shared directory

path = /var/opt/mssql/repldata <- location of directory we wish to share

writable = yes  <- determines if the share is writable from other hosts

create mask = 770  <- Linux permissions for files created

directory mask = 770 <- Linux permissions for directories created

valid users = mssql   <- list of users who can login to this share

On Host 2 (Subscriber) Mount the Samba Share

Edit the command with the correct paths and run the following command on machine2:

Bash

sudo mount //<name_of_host_1>/<share_name> </local/path/on/host/2> -o user=mssql,uid=mssql,gid=mssql

Example

Bash

mount //host1/mssql_data /var/opt/mssql/repldata_shared -o user=mssql,uid=mssql,gid=mssql

user=mssql <- sets the login name for samba

uid=mssql   <- makes the mssql user as the owner of the mounted directory

gid=mssql   <- sets the mssql group as the owner of the mounted directory

On Both Hosts Configure SQL Server on Linux Instances to use Snapshot Share

Add the following section to mssql.conf on both machines. Use wherever the samba share for the //share/path. In this example, it would be //host1/mssql_data

Bash

[uncmapping]

//share/path = /local/path/on/hosts/

Example

On host1:

Bash

[uncmapping]

//host1/mssql_data = /local/path/on/hosts/1

On host2:

Bash

[uncmapping]

//host1/mssql_data = /local/path/on/hosts/2

Configuring Publisher with Shared paths

  • When setting up replication, use the shares path (example //host1/mssql_data
  • Map //host1/mssql_data to a local directory and the mapping added to mssql.conf.

Configure replication with non-default ports (SQL Server Linux)

You can configure replication with SQL Server on Linux instances listening on any port configured with the network.tcpport mssql-conf setting. The port needs to be appended to the server name during configuration if the following conditions are true:

  1. Replication set-up involves an instance of SQL Server on Linux
  2. Any instance (Windows or Linux) is listening on a non-default port.

The server name of an instance can be found by running @@servername on the instance. Do not use the IP address instead of the server name. Using the IP address for the publisher, distributor, or subscriber may result in an error.

Examples

‘Server1’ listens on port 1500 on Linux. To configure ‘Server1’ for distribution, run sp_adddistributor with @distributor. For example:

SQL

exec sp_adddistributor @distributor = ‘Server1,1500’

‘Server1’ listens on port 1500 on Linux. To configure a publisher for the distributor, run sp_adddistpublisher with @publisher. For example:

SQL

exec sp_adddistpublisher @publisher = ‘Server1,1500’ ,  , 

‘Server2’ listens on port 6549 on Linux. To configure ‘Server2’ as a subscriber, run sp_addsubscription with @subscriber. For example:

SQL

exec sp_addsubscription @subscriber = ‘Server2,6549’ ,  , 

‘Server3’ listens on port 6549 on Windows with server name of Server3 and instance name of MSSQL2017. To configure ‘Server3’ as a subscriber, run the sp_addsubscription with @subscriber. For example:

SQL

exec sp_addsubscription @subscriber = ‘Server3/MSSQL2017,6549’,  ,