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. [caption id=“attachment_1074” align=“alignright” width=“300”] An image of containers. Get it? Image… Container… Just keep reading.[/caption]

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 'SA\_PASSWORD=P@55w0rd' \\
               -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 'SA\_PASSWORD=P@55w0rd' \`
               -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.