In a previous post I went through how to run SQL Server in Docker Swarm and in this post I want to continue delving into the Docker Swarm platform by looking at secrets.

Like Kubernetes, Docker Swarm has functionality to store sensitive data in secrets. When running SQL Server in containers we usually have to manually type out the environment variable for the MSSQL_SA_PASSWORD which isn’t great if we want to store that code in a public repository so being able to obfuscate that password is great!

Let’s have a look how it works. I’m going to use my lab detailed in the previous post.

First thing to do is create the secret: –

printf "Testing1122" | docker secret create mssql_sa_secret -

And confirm that the secret has been created: –

docker secret ls

WARNING – the first docker service create statement below doesn’t work! I’ve included it to show how I ended up at my end solution!

Ok, now we can reference the secret in our docker service create statement by using the –secret flag. This mounts the secret in the container at /run/secrets/secret_name which we can then reference in a –env flag.

Now we can create a service in Docker Swarm to run SQL Server, referencing that secret: –

docker service create \
--name sqlswarm1 \
--replicas 1 \
--publish 15789:1433 \
--secret source=mssql_sa_secret \
--env MSSQL_SA_PASSWORD=/run/secrets/mssql_sa_secret \
--env ACCEPT_EULA=Y \
mcr.microsoft.com/mssql/server:2022-CU4-ubuntu-20.04

Confirm that the service is up: –

docker service ls

And let’s connect to SQL from our local machine using the IP address of the manager node in the swarm and the port we specified in the docker service create statement (15789): –

mssql-cli -S 10.0.0.40,15789 -U sa -P Testing1122 -Q "SELECT @@VERSION AS [Version];"

Ah, that didn’t work! Let’s have a look why…

The password should be stored as an environment variable in the container so let’s find the node in the swarm that the container is running on: –

docker service ps sqlswarm1

And then run the following on the node: –

docker exec $(docker ps -aq) printenv

DOH!

It hasn’t taken the values within the file, it’s just taken the text that we entered into the container run statement!

Urgh, this is my fault. I’m referencing the documentation here but the docker service run statement I’m using as a guide is for a mysql container. MySQL has an environment variable that can reference a password file, no such luck with SQL Server.

So how do we reference the password within the file?

NOTE – there might be a better way to do this but this is the way I’ve come up with 🙂

Ok, so let’s get rid of that service: –

docker service rm sqlswarm1

Create an environmentvars.sh file: –

#!/bin/bash

export MSSQL_SA_PASSWORD=$(cat /run/secrets/mssql_sa_password)

In this file we reference the password file and set the environment variable.

Now create a custom image from a dockerfile: –

FROM mcr.microsoft.com/mssql/server:2022-CU4-ubuntu-20.04

USER root

COPY environmentvars.sh /var/opt/mssql/

RUN chown mssql /var/opt/mssql/environmentvars.sh

RUN chmod +x /var/opt/mssql/environmentvars.sh

USER mssql

CMD . /var/opt/mssql/environmentvars.sh && /opt/mssql/bin/sqlservr

Build the custom image: –

docker build -t customsqlimage .

NOTE – As this is a custom image not in a repository we’ll have to build the image on each of the nodes in the cluster

Ok, now we can recreate the service: –

docker service create \
--name sqlswarm1 \
--replicas 1 \
--publish 15789:1433 \
--secret source=mssql_sa_secret,target=mssql_sa_password \
--env ACCEPT_EULA=Y \
customsqlimage

NOTE – We’re removing the ENV for the MSSQL_SA_PASSWORD as it’s handled in the environmentvars.sh file. We also get a warning that the customsqlimage is not available in a registry so Docker will look for local images on each node (which could lead to a mismatch of image versions).

We have our service up so let’s try connect to SQL again: –

mssql-cli -S 10.0.0.40,15789 -U sa -P Testing1122 -Q "SELECT @@VERSION AS [Version];"

Ah ha! Success!

OK, I admit…not the greatest solution! BUT, we could use that environmentvars.sh file to create more environment variables than just MSSQL_SA_PASSWORD so it could (possibly) be useful?

Thanks for reading!

There are a whole bunch of environment variables that can be used to configure SQL Server when run in a Docker container. You can check out the full list here.

So we could end up with a docker container run statement like this: –

docker container run -d `
--publish 15789:1433 `
--env MSSQL_PID=Developer `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env ACCEPT_EULA=Y `
--env MSSQL_AGENT_ENABLED=True `
--env MSSQL_DATA_DIR=/var/opt/sqlserver/sqldata `
--env MSSQL_LOG_DIR=/var/opt/sqlserver/sqllog `
--env MSSQL_BACKUP_DIR=/var/opt/sqlserver/sqlbackups `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU12-ubuntu-18.04

Do we really want to be typing all that out every time we run a container? Ok, we could drop this into a script and execute that but another option is to use environment variable files.

Nice and simple, we take all the environment variables in the statement above and drop them into a file on our Docker host: –

MSSQL_PID=Developer
ACCEPT_EULA=Y
MSSQL_AGENT_ENABLED=True
MSSQL_DATA_DIR=/var/opt/sqlserver/sqldata
MSSQL_LOG_DIR=/var/opt/sqlserver/sqllog
MSSQL_BACKUP_DIR=/var/opt/sqlserver/sqlbackups

And now just reference that file in the docker container run statement (I saved my file as C:\Temp\var.env): –

docker container run -d `
--publish 15789:1433 `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env-file C:\temp\var.env `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU12-ubuntu-18.04

To confirm the variables have been set: –

docker exec sqlcontainer1 printenv

And there they are! We can also use multiple files referenced using the –env-file flag.

Another way of doing is (probably better?) is to use Docker Compose. If you want to learn more about compose, check out the SQL Server & Containers Guide!

Thanks for reading!

When running multiple SQL Server containers on a Docker host we should always be setting CPU and Memory limits for each container (see the flags for memory and cpus here). This helps prevent the whole noisy neighbour situation, where one container takes all the host’s resources and staves the other containers.

But what if we forget to set those limits? Well, no worries…we can update them on the fly!

Let’s have a look. First let’s run a container with no memory limits: –

docker container run -d \
--publish 15789:1433 \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU12-ubuntu-18.04

Confirm the container is up and running: –

docker container ls

Now have a look at the stats of the container: –

docker stats

This container is running on a host with 4GB of RAM so pretty much no limit in place. Let’s add a limit: –

docker update sqlcontainer1 --memory 2048MB

N.B. – Thanks to Anthony Nocentino (b|t) for pointing this out to me, I was under the (wrong) impression that limits couldn’t be changed on a running container…so this is really cool.

In fact, if you want a better explanation on container limits I recommend you check out Anthony’s post Container Limits and SQL Server

Anyhoo, now let’s have a look at the limits in place: –

docker stats

Cool! So we’ve set a limit for our running container!

BUT! What if we know how those limits are enforced…can we enforce a limit on a running container without using docker update?

Let’s have a go! First remove the existing container and re-deploy: –

docker container rm sqlcontainer1 -f

docker container run -d \
--publish 15789:1433 \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU12-ubuntu-18.04

Confirm, no limit in place: –

docker stats

Same as before.

Right, limits on containers are enforced by control groups (cgroups)…when processes are placed in cgroups those cgroups (for memory, cpu etc.) limit the amount of the host’s resources that a process can use.

And that’s all containers really are…just processes running on the host.

So let’s find the cgroup for the memory limit for the container.

First grab the container ID (have to set the limit as root so switching here): –

sudo su

CONTAINERID=$(docker ps -aq) && echo $CONTAINERID

Then have a look at the memory control group

find /sys/fs/cgroup/ -name *$CONTAINERID*

MEMORYCGROUP=$(find /sys/fs/cgroup/ -name *$CONTAINERID* | grep memory) && echo $MEMORYCGROUP

ls $MEMORYCGROUP

cat $MEMORYCGROUP/memory.limit_in_bytes

So no limit in place. Let’s set a limit and confirm: –

echo 2147483648 > $MEMORYCGROUP/memory.limit_in_bytes

cat $MEMORYCGROUP/memory.limit_in_bytes

Looks good, now let’s check docker stats: –

docker stats

And there’s the limit in place! Kinda cool…working out how Docker is placing resource limits on containers by using cgroups. If you fancy reading more about how container works, check out my SQL Containers From Scratch blog here.

Thanks for reading!