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:
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.
For detailed information about replication, see SQL Server replication documentation.
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
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.
Before configuring publishers, distributors, and subscribers, you need to complete a couple configuration steps for the SQL Server instance.
- Enable SQL Server Agent to use replication agents. On all Linux servers, run the following commands in the terminal.
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.
- 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:
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:
- On SSMS connect to your instance of SQL Server in Object Explorer.
Replication, and click
- Follow the instructions on the
Configure Distribution Wizard.
Create publication and articles
To create a publication and articles:
- In Object Explorer, click
- Follow the instruction on the
New Publication Wizardto configure the type of replication, and the articles that belong to the publication.
Configure the subscription
To configure the subscription in Object Explorer, click
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.
- Samba shares are configured that files written to /local/path1 by the replication agents on publisher can be seen by the subscriber
- 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
- SQL Server finds the local path from the //share/path to know where to look for the files
- 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.
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
sudo apt-get -y install samba
sudo service smbd restart
sudo yum install samba
sudo service smb start
sudo service smb status
On Host 1 (Distributor) Set-up the Samba share
- Set-up user and password for samba:
sudo smbpasswd -a mssql
- Edit the /etc/samba/smb.conf to include the following entry and fill in the share_name and path fields
path = </local/path/on/host/1>
writable = yes
create mask = 770
valid users = mssql
[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:
sudo mount //<name_of_host_1>/<share_name> </local/path/on/host/2> -o user=mssql,uid=mssql,gid=mssql
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
//share/path = /local/path/on/hosts/
//host1/mssql_data = /local/path/on/hosts/1
//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:
- Replication set-up involves an instance of SQL Server on Linux
- 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.
‘Server1’ listens on port 1500 on Linux. To configure ‘Server1’ for distribution, run sp_adddistributor with @distributor. For example:
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:
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:
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:
exec sp_addsubscription @subscriber = ‘Server3/MSSQL2017,6549’, ,