Persisting data for SQL Server on Docker Swarm with Portworx

~

In my last couple of blog posts (here and here) I talked about how to get SQL Server running in Docker Swarm. But there is one big (and show-stopping) issue that I have not covered. How do we persist data for SQL Server in Docker Swarm?

Docker Swarm, like Kubernetes, has no native method to persist data across nodes…so we need another option and one of the options available to us is Portworx.

So how can we use Portworx to persist SQL Server databases in the event of a node failure in Docker Swarm?

There are three steps to get this working: –

  1. Install a key value store that can be accessed by Portworx on each cluster node
  2. Install and configure Portworx on each of the nodes
  3. Deploy a volume for SQL Server using the Portworx driver

Let’s run through each of those steps.

Installing etcd

Portworx needs a key value store to be installed and reachable from all the nodes in the Docker Swarm cluster.

So what we’re going to do is install ETCD on the manager node of the cluster and configure it so that all the other nodes in the cluster can talk to it.

First thing to do is download a sample config file and the etcd binaries: –

ETCD_VER=v3.4.27
DOWNLOAD_URL=https://github.com/etcd-io/etcd/releases/download

curl -L https://raw.githubusercontent.com/etcd-io/etcd/main/etcd.conf.yml.sample -o /tmp/etcd-download-test/etcd.conf.yml
curl -L ${DOWNLOAD_URL}/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz -o /tmp/etcd-${ETCD_VER}-linux-amd64.tar.gz
tar xzvf /tmp/etcd-${ETCD_VER}-linux-amd64.tar.gz -C /tmp/etcd-download-test --strip-components=1

Move files to a location in our PATH environment variable: –

sudo cp /tmp/etcd-download-test/etcd /tmp/etcd-download-test/etcdctl /tmp/etcd-download-test/etcd.conf.yml /usr/local/bin

Confirm versions: –

etcd --version
etcdctl version

Create etcd user and group: –

sudo groupadd --system etcd
sudo useradd -s /sbin/nologin --system -g etcd etcd

Create etcd data and wal directories and configure permissions: –

sudo mkdir -p /var/lib/etcd/wal
sudo chown -R etcd:etcd /var/lib/etcd
sudo chmod -R 700 /var/lib/etcd

Update the sample configuration file: –

sudo vim /usr/local/bin/etcd.conf.yml

Here we are updating the following: –

  • line 4 – change name of etcd member to the server name
  • line 7 – add /var/lib/etcd as the data-dir
  • line 10 – add /var/lib/etcd/wal as the wal-dir
  • line 26/29/42/46 – change localhost to server IP address
  • line 62 – add initial-cluster: SERVERNAME=http://IPADDRESS:2380

The next thing to do is create a service to run etcd. So create a etcd.service file: –

echo "[Unit]
Description=etcd key-value store
Documentation=https://github.com/etcd-io/etcd
After=network.target

[Service]
User=etcd
Type=notify
ExecStart=/usr/local/bin/etcd --config-file=/usr/local/bin/etcd.conf.yml
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target" > etcd.service

And move it to the correct location: –

sudo mv etcd.service /etc/systemd/system

Reload systemd: –

sudo systemctl daemon-reload

Start etcd: –

sudo systemctl enable etcd
sudo systemctl start etcd

Confirm service: –

sudo systemctl status etcd

To check the endpoint and status of etcd: –

etcdctl --endpoints=10.0.0.40:2379 endpoint health
etcdctl --endpoints=10.0.0.40:2379 endpoint status

Great! That’s etcd up and running. Now we can install and configure Portworx.

Installing portworx

In order to get Portworx up and running, there are a couple of pre-requisities we need to consider: –

  • Swap is recommended to be disabled on each of the nodes
  • Each node should have a min of 4GB RAM
  • A block device available to be used as a storage pool for Portworx

Right, now we can install the Portworx binaries on our nodes To do this, we run a container with /opt/pwx and /etc/pwx mounted: –

REL="/3.0"  # Portworx v3.0 release

latest_stable=$(curl -fsSL "https://install.portworx.com$REL/?type=dock&stork=false&aut=false" | awk '/image: / {print $2}' | head -1)

sudo docker run --entrypoint /runc-entry-point.sh \
--rm -i --privileged=true \
-v /opt/pwx:/opt/pwx -v /etc/pwx:/etc/pwx \
$latest_stable

Once that’s complete we can finish the install with: –

sudo /opt/pwx/bin/px-runc install -k etcd://10.0.0.40:2379 -c AP-SWARM-01 -s /dev/sdb

Breaking this statement down: –

  • -k etcd://IPADDRESS:2379 – endpoint for our etcd cluster that we configured earlier
  • -c AP-SWARM-01 – name that we’re going to call our cluster in Portworx
  • -s /dev/sdb – block device on each node that will be used as a Portworx storage pool

When I created my hyper-v VMs I added a 20GB disk, /dev/sdb, that will be used as the storage pool.

You can see the available devices on a server by running: –

sudo lsblk

So on my server: –

OK. Once the above statement has completed on all nodes of our cluster, we need to restart the Portworx service: –

sudo systemctl restart portworx

Then confirm the service status: –

sudo systemctl status portworx

Then enable the service to start on server reboot: –

sudo systemctl enable portworx

And finally, confirm the status of Portworx itself: –

sudo /opt/pwx/bin/pxctl status

NOTE – Portworx will take around 5 minutes to come online.

Fantastic stuff! We have etcd and Portworx up and running. Now we can look at deploying SQL Server.

Deploying SQL Server

Ok phew…we’ve done all the hard work! Let’s have a look at deploying SQL Server using storage provisioned via Portworx in our Docker Swarm cluster.

So we need a volume to persist our data on: –

docker volume create -d pxd --name mssql_vol --opt repl=3 --opt fs=ext4 --opt sharedv4=true

Here we’re deploying a volume using the Portworx driver and using a replica count of 3 so that our volume is being replicated to each node in the cluster.

To confirm this, run: –

sudo /opt/pwx/bin/pxctl volume inspect mssql_vol

Here we can see the replica sets of the volume on each node and the node that the volume is currently attached to.

Now we can deploy SQL Server referencing that volume in the –mount flag: –

docker service create \
--name sqlswarm1 \
--replicas 1 \
--publish 15789:1433 \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--mount type=volume,src=mssql_vol,dst=/var/opt/mssql,volume-driver=pxd \
mcr.microsoft.com/mssql/server:2022-CU4-ubuntu-20.04

We’re mounting the volume under /var/opt/mssql in the container, which is the default location for database data and log files.

So let’s create a database! I’m going to use the mssql-cli to do this but you can do this in SSMS using an IP address of a node in the cluster (I usually use the manager node’s IP address) and port 15789: –

mssql-cli -S 10.0.0.40,15789 -U sa -P Testing1122 -Q "CREATE DATABASE [testdatabase];"

Confirm the database: –

mssql-cli -S 10.0.0.40,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

OK, now that we have a database in our SQL instance…let’s test failover! What we’re going to do is shut down the node that the container is running on and see what happens.

To find the node that the container is running on: –

docker service ps sqlswarm1

It’s running on AP-NODE-02, so let’s shut that node down!

Let’s see what happened to our SQL container: –

docker service ps sqlswarm1

It’s now running on AP-NODE-03! But has our database survived?

mssql-cli -S 10.0.0.40,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

Yes! It’s there! We have persisted our database from one node to another in our Docker Swarm cluster!

Thanks for reading!

Leave a comment