SQL Server Transactional Replication on Linux Docker containers
*I posted this a few weeks ago, but it’s very applicable to T-SQL Tuesday so it’s been repurposed so I can join in!
Today’s article is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a blog party on the second Tuesday of each month and everyone is welcome to participate.
This month’s T-SQL Tuesday is hosted by Tracy Boggiano ([b]|[t]), is all about Linux.
As a DBA primarily of Microsoft SQL Server, I like to keep an eye on the latest features – it’s safe to say when it was announced that it supported Linux I was probably among the first to get it installed and play with it – it was missing some core functionality, namely the SQL Server Agent, but fast forward a bit and we now have the agent (required for replication) and with that, Microsoft SQL Server Transactional Replication support! Lets dive straight in… almost, there is 1 thing I want to point out before we start this, and that is that I set this replication up ~ 8 weeks ago and on firing up the docker containers to start writing this article I can confirm that the replication just continued to work! If you’re a DBA then you know replication can be a little flaky at times, so you’ll forgive me for assuming my container replication might have died in the last 8 weeks – but no, I started the containers and everything was fine! This blew my mind, to think I have a docker setup just waiting to continue working if I ever need to test something – ok, let’s carry on.
Pre-requisites
So obviously your going to need to install docker if you don’t have it already, the fantastic thing about docker is it doesn’t matter if you’re on Linux, Windows or MacOSX! That’s right, we can install and setup MS SQL Server on a mac and setup replication!! (strange times indeed) – I am on windows but once you get docker running the rest will be the same or very similar.
Lets do this!
I will clean up my environment, and we’ll be on a level pegging from here in. First let’s spin up 3 containers, 1 for our publisher, 1 as a subscriber and 1 to act as the distributor. If you’re not sure on replication topology then check out the resources at the end, but for this were making assumptions that you know what SQL Server replication is and you can find your way around docker.
Create 3 Containers:
docker run -h publisher --name publisher -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Pa55w0rd!' -p 1451:1433 -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
docker run -h subscriber --name subscriber -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Pa55w0rd!' -p 1452:1433 -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
docker run -h distributor --name distributor -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Pa55w0rd!' -p 1453:1433 -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
So that’s our 3 containers running, you will notice if you didn’t already have the latest image “2019-CTP2.3” then that will have gone ahead and downloaded it, so the first command will take a while but the next 2 commands should open up instantly.
If we look at the commands, things to note are:
- We specified the SA account password, this is required but it can be changed later.
- We accepted the end user licence agreement for install SQL Server, again required.
- We specified the host name (important when setting up replication) – imagine trying to remember which host is which by using container IDs.
- We specified the same default port of 1433 but exposed it externally to a different port so we can connect into the containers from our host.
So now let’s run the docker ps command to list our running containers:
Now let’s confirm we can connect to them all, remember we set the ports up so that looks like this:
192.168.99.100,1451 (publisher)
192.168.99.100,1452 (subscriber)
192.168.99.100,1453 (distributor)
Your docker machine IP might differ from mine, to check that use the command:
Docker-machine ip
Connect to each container and verify it is working.
Now the observant among you may have noticed that the agent isn’t running, by default that is the case. Lets fix that now:-
Connect to your container or send the command straight to the container:
docker exec -it distributor /opt/mssql/bin/mssql-conf set sqlagent.enabled true
docker exec -it publisher /opt/mssql/bin/mssql-conf set sqlagent.enabled true
docker exec -it subscriber /opt/mssql/bin/mssql-conf set sqlagent.enabled true
And now we need to restart the service, I simply do this by stopping and start the container.
Docker stop publisher
Docker stop subscriber
Docker stop distributor
Docker start publisher
Docker start subscriber
Docker start distributor
Run docker ps and make sure they came back, then connect again and you should see you have agents!
Ok, the next thing we need to think about before we start replication is the network, by default these containers cannot talk to each other. We must fix that, so let’s create a network and connect each of them to it.
Docker network create replication
Docker network connect replication publisher
Docker network connect replication distributor
Docker network connect replication subscriber
Nice! Now we have a network and the containers connected. Run “docker network ls” to list your networks. And run “docker network inspect replication” to see the connected containers and other info on the network we named “replication”
Ok, so now for the tricky part, setting up SQL Server replication! If you’re a big fan of GUIs, particularly SSMS then this is where you get a little sad, because of the way SSMS works it’s just not possible to setup replication from it to a container using the GUI, we are connecting using the IP and not the machine names so SSMS just can’t deal with it, so time to break out the T-SQL.
First we need to configure the distributor:-
-- make sure your on the right server
SELECT @@version AS Version
SELECT @@SERVERNAME AS Server_Name
-- step 1, tell this server it is a distributor
EXEC sp_adddistributor @distributor = 'distributor', @password = 'Pa55w0rd!'
-- step 2, create the distribution db
EXEC sp_adddistributiondb @database = 'distribution';
-- step 3, tell the distributor who the publisher is
-- NOTE! (make the directory '/var/opt/mssql/ReplData',
-- it doesn't exist and this command will try and verify that it does)
-- docker exec -it distributor bin/bash
-- mkdir /var/opt/mssql/ReplData
-- CTRL+Z get back out
EXEC sp_adddistpublisher @publisher = 'publisher', @distribution_db = 'distribution'
-- let's check the DB
USE distribution;
GO
-- see the repl commands table
SELECT *
FROM [dbo].[MSrepl_commands]
-- and let's see the jobs we made
SELECT name, date_modified
FROM msdb.dbo.sysjobs
ORDER by date_modified descExcellent! Now let’s go and setup our publisher:-
-- make sure were on the right server
SELECT @@version AS Version;
SELECT @@SERVERNAME AS Server_Name;
-- tell the publisher who the remote distributor is
EXEC sp_adddistributor @distributor = 'distributor',
@password = 'Pa55w0rd!';
-- create a test database
CREATE DATABASE Sales;
GO
-- create a test table
USE [Sales];
GO
CREATE TABLE CUSTOMER
(
[CustomerID] [INT] NOT NULL,
[SalesAmount] [DECIMAL] NOT NULL
);
GO
-- add a PK (we can't replicate without one)
ALTER TABLE CUSTOMER ADD PRIMARY KEY (CustomerID);
-- let's insert a row
INSERT INTO CUSTOMER
(
CustomerID,
SalesAmount
)
VALUES
(0, 100);
-- lets enable the database for replication
USE [Sales];
EXEC sp_replicationdboption @dbname = N'Sales',
@optname = N'publish',
@value = N'true';
-- Add the publication (this will create the snapshot agent if we wanted to use it)
EXEC sp_addpublication @publication = N'SalesDB',
@description = N'',
@retention = 0,
@allow_push = N'true',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true';
-- now let's add an article to our publication
USE [Sales];
EXEC sp_addarticle @publication = N'SalesDB',
@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';
-- now let's add a subscriber to our publication
use [Sales]
exec sp_addsubscription
@publication = N'SalesDB',
@subscriber = 'subscriber',
@destination_db = 'sales',
@subscription_type = N'Push',
@sync_type = N'none',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
-- and add the push agent
exec sp_addpushsubscription_agent
@publication = N'SalesDB',
@subscriber = 'subscriber',
@subscriber_db = 'Sales',
@subscriber_security_mode = 0,
@subscriber_login = 'sa',
@subscriber_password = 'Pa55w0rd!',
@frequency_type = 64,
@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
-- by default it sets up the log reader agent with a default account that won’t work, you need to change that to something that will.
EXEC sp_changelogreader_agent @publisher_security_mode = 0,
@publisher_login = 'sa',
@publisher_password = 'Pa55w0rd!';
Now go and stop and start the log reader job, and start the subscription job. And hey presto, rows will start appearing on the other side!
A few things to note here:-
- None of what I showed you is for production, were playing around – please use real passwords and caution if you try to implement this – also note that (at the time of writing) AD integration isn’t available yet in SQL Server on docker.
- Although at first, that might seem painful and a little complex, it’s best to understand the steps involved, but this is docker! So now there is nothing stopping you from putting all this in your own custom images which you can then instantly deploy and have replication up and running in seconds.
- It’s worth noting I didn’t use the snapshot agent, I chose the sync type of “none” which basically means I am responsible for synchronising the 2 tables and then letting replication run, which is what we did.