How do I update my SQL Server Docker container?

Hooray, we can run SQL Server on Linux inside of a Docker container. That certainly makes it easy to try out SQL Server on Linux and for developers to run SQL Server, regardless of their chosen operating system. But what if we want to update that SQL Server container?

There’s an easy way to update SQL Server inside a container.

A lot of containers

An image of containers. Get it? Image… Container… Just keep reading.

Docker Update?

There’s a docker update command, but it’s used to change CPU and memory settings.

If you want to read more about docker update, there’s great documentation online. Rather than rehash the documentation, I’ll move on to the better approach.

Step by Step Approach: A New Image

There’s no one built-in command that will let us update a docker image and push that to all of our containers, so we’ll have to build this up step by step.

We can use docker pull to download the newest version of the image. This gets us an updated version of the image. In our case, the command will be: docker pull microsoft/mssql-server-linux.

Once docker pull has finished, we’ll have a new copy of the SQL Server image. Docker’s storage model is interesting – multiple layers of file system diffs are combined to create a unified view of the OS. The image layers are read only – any changes that happen through a container are made through a copy on write process.

Why doesn’t the new image work for our existing containers? Each of the layers is referenced by a unique identifier. Even if we docker pull a new image, all of our existing containers are going be pointing to the original image. Once we’ve got the new image, we need to replace our existing containers.

Replacing the Container

The next step is to stop all of the containers using the SQL Server image using docker stop. Once we’ve stopped the containers, we delete the containers with docker rm.

Before deleting, we can use docker inspect mssql to examine the parameters for a container (assuming the container is named mssql, of course). This produces a bunch of JSON that tells us everything we need to know about our container. For one VM, this isn’t necessary, we can script that manually, but if there are a lot of containers (say you have an AG), docker inspect can be combined with docker ps -a -f name=whatever and OS scripting tools to change all of your docker instances that match some query.

Start it all Back Up

So far we’ve pulled the latest image, stopped the old container, and deleted the old container. There’s one thing left to do: create a new container!

Creating a new container is pretty simple. I’ll provide a sample, but I highly recommend Aaron Bertrand’s excellent VSCode on Mac meets SQL Server on Linux (in Docker). Well, that and the docker run documentation.

The important thing is that we use the -v option to create a storage volume outside of the container. Otherwise, when we use docker rm to delete the container, all of your changes and storage would be deleted, too. Thankfully, we’ll used the -v flag to create persistent storage for our SQL Server on Linux container.

Putting it all Together

update_sql() {
    docker pull microsoft/mssql-server-linux:latest
    docker stop mssql-test
    docker rm mssql-test
    docker run -v /opt/docker/volumes/mssql-test:/var/opt/mssql \
               --name mssql-test \
               -e 'ACCEPT_EULA=Y' \
               -e '[email protected]' \
               -p 1433:1433 \
               -d microsoft/mssql-server-linux
}

This combines all of the commands we’ve been talking about into a single shell command that you could run anywhere. In my case, this runs on Linux, just through it in your .bashrc or .zshrc and reload your shell. It’ll also run on OS X because OS X is UNIX-y under the hood, just like Linux.

If you want this to work under PowerShell you could do something like…

function Update-MssqlContainer() {
    docker pull microsoft/mssql-server-linux:latest
    docker stop mssql-test
    docker rm mssql-test
    docker run -v /opt/docker/volumes/mssql-test:/var/opt/mssql `
               --name mssql-test `
               -e 'ACCEPT_EULA=Y' `
               -e '[email protected]' `
               -p 1433:1433 `
               -d microsoft/mssql-server-linux
}

Summary

There you have it, an easy way to keep your SQL Server test containers up to date.

If you are feeling really sassy, you could create a copy of this function that checks the output of the first command and doesn’t do anything at all if the image is already up to date. After all, why delete and re-create the Docker container if nothing is different?


Photo by Igor Ovsyannykov, licensed under CC0.

2 Comments. Leave new

  • Okay, so I’m confused/curious. Can you use this as a way to handle server OS migrations? It doesn’t seem like it (since your script appears to perform a brand new install of SQL Server), but is that possible? More than anything, we know that we’re going to at some point migrate Operating Systems, as well as SQL Server. I can manage the SQL Server upgrade (bonus question: could you use this somehow as some sort of big in-place-upgrade agent, where you’re detaching/attaching all the databases to the new instance?), but because of connection strings, etc we’re tied to the OS folks, who can be slow to upgrade.

    Reply
    • I’m not a Docker expert, so take this with a big grain of salt. I believe that you could use this as a way to handle OS and SQL Server updates. The idea is that you would mount storage for the files that you want to persist between reboots. In my case, I just care about a few files in a folder, so I leave everything in there. If you need additional container configuration, you configure the container with Docker’s configuration mechanism because the container itself is stateless&emdash;changes are lost on reboot.

      Connection strings shouldn’t be a problem because you can solve this problem with DNS aliasing. The more complex you make this, the more you should be looking into container organization software like Kubernetes. I will admit that I only know of this and have never tried it myself.

      The idea behind containers is that you turn servers into repeatable stateless configurations; all state that must persist is stored in configuration and that configuration is applied on the container restart. Anything that isn’t made persistent through configuration is ephemeral. This goes hand in hand with the Pets vs Cattle discussion about server configuration.

      Reply
  • Leave a Reply

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

    Fill out this field
    Fill out this field
    Please enter a valid email address.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Menu