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.

docker containers are open for business!

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:

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:

  1. We specified the SA account password, this is required but it can be changed later.
  2. We accepted the end user licence agreement for install SQL Server, again required.
  3. We specified the host name (important when setting up replication) – imagine trying to remember which host is which by using container IDs.
  4. 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:

docker ps

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:

And now we need to restart the service, I simply do this by stopping and start the container.

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.

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:-

Excellent! Now let’s go and setup our publisher:-

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:-

  1. 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.
  2. 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.
  3. 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.

Resources:

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-ver15&pivots=cs1-bash

https://www.docker.com/get-started

Leave a Reply

Your email address will not be published. Required fields are marked *