Containers: Upgrading SQL Server from 2017 to 2019 RTM

Throughout the pre-release of SQL Server 2019, I was demoing an effectively instant, and magical, container upgrade from 2017 to 2019. However, when I finally downloaded the release bits in a new image, the magic went away. In fact, I got errors, so what happened?

Non-root User

In SQL Server 2017, the containers were running as root. The thing is, you’re basically setting up your instance to run as administrator of the system. We all know that’s a no-no. So, in SQL Server 2019, Microsoft fixed this and now the SQL Server instance within the Linux container runs as mssql, much better.

However, this immediately causes issues when we’re doing an in-place upgrade using a volume on a 2017 container to move to 2019. We’re not root any more.

Let’s say we start a 2017 container like this:

docker run -e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=$cthulhu1988' `
-p 1450:1433 `
--name Demo17vol `
-v sqlvol:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2017-latest

In the past, we could stop this container, and start another using 2019, like this:

##stop the running container
docker stop Demo17vol

## create a new container using the same volume
docker run -e 'ACCEPT_EULA=Y' `
    -e 'SA_PASSWORD=$cthulhu1988' `
    -p 1450:1433 `
    --name Demo19New `
    -v sqlvol:/var/opt/mssql `
    -d mcr.microsoft.com/mssql/server:2019-latest

Reusing the same volume between the two containers means any databases created would update. However, if we check the log like this:

docker logs Demo19New

The output will look like this:

SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
sqlservr: Unable to open /var/opt/mssql/.system/instance_id: Permission denied (13)
/opt/mssql/bin/sqlservr: Unable to open /var/opt/mssql/.system//instance_id: Permission denied (13)

There are a few ways to fix this. It depends on how you make the adjustment. Here is one, somewhat clunky, solution.

Safe Container Upgrade

The idea is, you can fix 2017, do some sort of interim fix, or fix 2019. Initially here, I’m going to fix 2017 by running this, before I stop the 2017 container:

docker exec -it Demo17vol "bash"
##bash commands
chgrp -R 0 /var/opt/mssql
chmod -R g=u /var/opt/mssql

Now, when I stop the 2017 container and start the 2019 container, the upgrade occurs normally because of the changes to permissions.

Conclusion

While I’m very much in favor of the changes made by Microsoft here, it takes just a tiny bit of the magic away from showing off containers. For a more detailed description of the problem and some more sophisticated information on the solution, I recommend reading Anthony Nocentino’s blog post here. By the way, Anthony is also the one who pointed me to the intial solution.