Skip to content

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’,  ,