If you read my blog regularly, you may have noticed that I installed SQL in Docker on my new laptop a couple of months ago (incidentally, I think it must still be new, because I still make mistakes when doing various shortcuts around the special keys like the arrow keys, End, PgUp, etc). Anyway, that was a while back now, and I recently wanted to try Cumulative Update.
Now, the thing with running SQL in containers is that the concept of downloading a patch file doesn’t work in the same way. If it were regular Linux, the commands would be very simple, such as ‘sudo yum update mssql-server’ in RHEL. But Docker doesn’t quite work the same way, as reflected by the Microsoft documentation which mentions Docker for installing but not in the Update section.
The trick to this is to remember that the Docker container is just a container running an image. So blowing it away and starting again is easy, as is spinning up different containers based on separate images. The database files are (hopefully) in separate volumes – accessed by the container, but not part of the image that’s running.
So we just grab the latest image, stop and remove the old container, and run it up again using the new image. So I run this PowerShell code to do that. It’s worth noting that the pull won’t actually pull anything if the 2019-latest image hasn’t changed. I could be more specific and pull the image based on the tag (which is unique), but the code I’m using will just grab whatever is marked as 2019-latest. Specific tags is what I want for testing against a variety of versions, so that I can be really sure which one is which, but if I’m just patching my local machine, 2019-latest is good.
docker pull mcr.microsoft.com/mssql/server:2019-latest
docker stop sql2019
docker rm sql2019
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SAPasswordGoesHere" -p 1433:1433 --name sql2019 -v C:/dockerfiles/data:/var/opt/mssql/data -v C:/dockerfiles/log:/var/opt/mssql/log -d mcr.microsoft.com/mssql/server:2019-latest
docker start sql2019
A container on this image looks for the master database files in /var/opt/mssql/data and /var/opt/mssql/log, so by having those folders reference folders outside the container, nothing changes when I blow away the sql2019 container. When the new one starts, it sees the master database, which tells it where to find the user databases. As is the case with any upgrade, it’ll apply any required changes when the SQL process spins up and loads the databases.
So this ends up being a very simple process. I can just have that block of code run whenever I want to make sure I’ve got the latest version. And if I forget, I’ll come back here and grab the code again!