How to update a SQL Server container on Docker for Windows (spoiler – there is a catch)

With the 1st CU for SQL 2019 released just yesterday, and Microsoft updating the docker image right away, the only natural response for me was to update the docker instance that I showed you how to deploy a few months back.

The theory

In theory, a docker container can’t be really “updated”, they’re meant to be stateless machines that you spin up and down responding to changes in demand; what we’re technically doing is creating a new container, based on a new image, that has the same configuration and uses the same persistent storage as the old one.

In short, the process should be:

• Pull the new image version from the repository
• stop the old container
• remove the old container
• configure a new container mapping the same persistent storage as the old one
• start the new container
• ??
• profit

The catch

There is a catch. Only for Docker on windows, there is a catch.
While in Linux systems you could just map the entire mssql folder of the container to persistent storage, this is not possible for windows.

This is why in the previous article I configured the persisted mapped volume as:

docker run `
--name sql2019 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=This is a password,ok?" ` 
-v D:\SQL:/sql `
-d mcr.microsoft.com/mssql/server:2019-latest

as opposed to what you see in the guides that are based on Linux operating systems:

docker run `
--name sql2019 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=This is a password,ok?" ` 
-v D:\SQL:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2019-latest

Mapping the whole mmsql folder to a persisted folder in windows allows in-place upgrades (as described for example here by Jeremiah Peschka )
As detailed in this Microsoft Documentation page, on Docker for windows we cannot do it, yet.

What now?

Not all hopes are lost, we have fewer options than our Linux friends, but we still have some:

1. Have your Database files in the persisted storage and re-attach them after creating the updated container
2. Use unmapped docker volumes to persist /var/opt/mssql

Reattacching Databases

If you don’t care about the instance configuration (aka “it’s ok as-is” or “I can restore all my settings with a simple script”), the easiest way is to just be sure to save/move the database files into the persisted storage and attach the files after updating:

docker pull mcr.microsoft.com/mssql/server:2019-latest

docker stop sql2019
docker rm sql2019

docker run `
-v D:\SQL:/sql `
--name sql2019 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=This is a password,ok?" `
-d mcr.microsoft.com/mssql/server:2019-latest

docker start sql2019

After the update, you’ll find yourself with an empty instance, the database files previously saved in the persisted storage can be reattached like so:

USE [master]
GO
CREATE DATABASE [AdventureWorksDW2017] ON 
( FILENAME = N'/sql/Data/AdventureWorksDW2017.mdf' ),
( FILENAME = N'/sql/Data/AdventureWorksDW2017_log.ldf' )
 FOR ATTACH
GO

Using (unmapped) docker volumes

While host volume mapping doesn’t work on windows, volumes work just fine; the idea is to persist the main SQL Server directory to a generic volume, while still enjoying the volume mapping in order to easily get-push files into the container.

docker pull mcr.microsoft.com/mssql/server:2019-latest

docker stop sql2019
docker rm sql2019

docker run `
-v sqlvolume:/var/opt/mssql `
-v D:\SQL:/sql `
--name sql2019 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=This is a password,ok?" `
-d mcr.microsoft.com/mssql/server:2019-latest

docker start sql2019

Where sqlvolume is a generic volume that will be created if it doesn’t exist.
The volume can be inspected using docker:

The drawback is that as opposed to the mapped folder, we have no means of checking and modifying the contents of the volume outside Docker; a decent Docker configuration to save volumes and containers where you actually want them is highly recommended.

Wrap Up

SQL Server on containers is even more cattle-like that the herd you’re used to manage, instances come and go in a matter of seconds; there is no upgrade, only throw away and replace: a true SQL Server Capitalism (Made in the USA, not by chance)