MSSQL Replication By T-SQL & Docker containers on Linux

Ahmad Bashir — Wed, 07 Aug 2024 21:15:51 +0000

MSSQL replication is easy to implement with user friendly UI ready steps, but what about Linux machine.

This article will provide an overview of the configuration steps on a Linux machine, without delving into the specifics of replication.

Achievements:

By the time you've finished reading this article, you'll be able to set up MSSQL database replication for multiple instances of Docker containers on an Ubuntu machine (Linux distribution).

Requirements:

Ubuntu (Linux distribution)
Docker
MSSQL (you can find here how to setup MSSQL on Linux by docker container).

Now it's time for the fun part! Let's dive in and configure a simple SQL Server snapshot replication on Linux using two instances of SQL Server by Transact-SQL (T-SQL). Note that the publisher and distributor are going to live on the same server, while we'll have a separate server for the subscriber. Let's get to it!

Configuration steps:

Setup servers' environment by docker containers and configure network sharing.
Configure publisher database.
Configure replication for publisher and distributor.
Configure publication job.
Specify tables to replicate.
Configure subscriber server and database.
List replication jobs and apply replication.
Monitoring.

#Setup servers' environment by docker containers and configure network sharing

If you're running multiple servers on a single machine, you need to link them together(docker containers by default are isolated). It's possible to do this by defining a host network, even if the servers are already running in Docker containers.

docker network create mssqllink

Check if the network exists:

docker container ls

Configure publisher server

sudo docker run -e "ACCEPT_EULA=Y" 
   -e "MSSQL_SA_PASSWORD=P@ssw0rd" \
   -p 1433:1433 
   --net mssqllink 
   --name publisher 
   --hostname publisher \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

The code above specify how we install new mssql docker container with these options:

--name and --hostname of the container
mssql container image
-p the port connection of the container
and the most important option we need to define is the NETWORK option --net this option enables the communication between containers that shares same network.

Now for the replicated database server(subscriber).

sudo docker run -e "ACCEPT_EULA=Y" 
   -e "MSSQL_SA_PASSWORD=P@ssw0rd" \
   -p 1443:1433 
   --net mssqllink 
   --name subscriber 
   --hostname subscriber \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

Editing -p 1443:1433 port option, name and hostname, finally..
DON'T FORGET THE NETWORK OPTION.

You must also create a directory for the replicated data on the MSSQL data folder at the publisher container.:

docker exec -it publisher mkdir /var/opt/mssql/data/ReplData/

#Configure publisher database

After connecting to the publisher server run the next script:

USE master
CREATE DATABASE Sales;
GO

USE [Sales];
GO

CREATE TABLE Customer (
    [CustomerID] INT NOT NULL,
    [SalesAmount] DECIMAL NOT NULL
);
GO

INSERT INTO Customer (CustomerID, SalesAmount)
VALUES (1, 100), (2, 200), (3, 300);
GO

#Configure replication for publisher and distributor

For configuring distributer server and database we need to run T-SQL stored procedure:

--- CONFIGURE DISTRIBUTOR ------------
DECLARE @distributor AS SYSNAME;
DECLARE @distributorlogin AS SYSNAME;
DECLARE @distributorpassword AS SYSNAME;
SET 
  @distributor = N 'publisher’';
--it will be the name of the publisher
SET 
  @distributorlogin = N 'sa';
SET 
  @distributorpassword = N 'P@ssw0rd';
USE master EXEC sp_adddistributor @distributor = @distributor GO 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/';
USE [distribution];
GO 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

After defining the distribution server and database, now we need to define publisher server and database for replication:

--- CONFIGURE PUBLISHER ------------
DECLARE @publisher AS SYSNAME;
DECLARE @distributorlogin AS SYSNAME;
DECLARE @distributorpassword AS SYSNAME;
DECLARE @folderPath AS NVARCHAR(500);
SET 
  @publisher = N 'publisher';
SET 
  @distributorlogin = N 'sa';
SET 
  @distributorpassword = N 'P@ssw0rd';
SET 
  @folderPath = N '/var/opt/mssql/data/';
USE master EXEC sp_adddistpublisher @publisher = @publisher, 
@distribution_db = N 'distribution', 
@security_mode = 0, 
@login = @distributorlogin, 
@password = @distributorpassword, 
@working_directory = @folderPath, 
@trusted = N 'false', 
@thirdparty_flag = 0, 
@publisher_type = N 'MSSQLSERVER';

#Configure publication job

Publication job is the processing of specify who will pushes the data to replicate.
Each time we want to replicate data we must call these jobs.

--- CONFIGURE Publication Job ------------
DECLARE @replicationdb AS SYSNAME;
DECLARE @publisherlogin AS SYSNAME;
DECLARE @publisherpassword AS SYSNAME;

SET @replicationdb = N'Sales';
SET @publisherlogin = N'sa';
SET @publisherpassword = N'P@ssw0rd';

USE [Sales];
GO

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''.',
    @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;

The first generated job publishes the data, and the second pushes it through the distribution server.

#Specify tables to replicate

On this step we need to specify the tables we want to replicate.

Run the T-SQL script below for each table you want to replicate. Pass in the table name you want to replicate in the script.

--- Add Article ------------
USE [Sales];
GO

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';

#Configure subscriber server and database

Configuring the subscription is the final step in the replication process. First, we will set up the server subscription.

DECLARE @subscriber AS SYSNAME;
DECLARE @subscriber_db AS SYSNAME;
DECLARE @subscriberLogin AS SYSNAME;
DECLARE @subscriberPassword AS SYSNAME;

SET @subscriber = N'subscriber';
SET @subscriber_db = N'Sales';
SET @subscriberLogin = N'sa';
SET @subscriberPassword = N'P@ssw0rd';

USE [Sales];
GO

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

All scripts are run on the distributor server (publisher server in our case).

Set up the subscriber server database to receive the
replicate.

Connect to subscriber server and apply database creation.

USE master
CREATE DATABASE Sales;
GO

USE [Sales];
GO

CREATE TABLE Customer (
    [CustomerID] INT NOT NULL,
    [SalesAmount] DECIMAL NOT NULL
);
GO

INSERT INTO Customer (CustomerID, SalesAmount)
VALUES (1, 100), (2, 200), (3, 300);
GO

The replication environment is ready. We just need to call the jobs we created before and monitor the state for any errors.

#List replication jobs and apply replication

The listed server jobs provide us with the replication jobs we need to run.

SELECT name, date_modified 
FROM msdb.dbo.sysjobs 
ORDER BY date_modified DESC;

After running this script, you will recognize that generated jobs contain the same name we create before.

And now for our case:
The job that holds the name PUBLISHER-PUBLICATION-SnapshotRepl-1 will define the publication who pushes the data.
And the job that holds the name DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER will push the data through distribution server to the subscriber server.

Apply replication by running the next scripts:

USE msdb;
GO

--generate snapshot of publications
EXEC dbo.sp_start_job N'PUBLISHER-PUBLICATION-SnapshotRepl-1';
GO

--distribute the publication to subscriber
EXEC dbo.sp_start_job N'DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER';
GO

Go and check Sales database at subscriber server and you will find the same data you have on the publisher server.

#Monitoring

Run the script below at the distribution server to check for any errors.

USE distribution
GO
SELECT time, error_text FROM MSrepl_errors ORDER BY time DESC
GO

Summary

This article shows how to replicate data on a Linux machine with multiple servers using Docker containers and T-SQL. The main challenge is working with multiple containers and MSSQL replication. We solve this by setting the container network option.

Microsoft Configure Replication with T-SQL

Quickstart: Run SQL Server Linux container images with Docker

DEV Community: Ahmad Bashir