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 an 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 anymore.

You might also be interested in:  Collection: SQL Server Sample Databases

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

xxxxxxxxxx

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:

xxxxxxxxxx

##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:

xxxxxxxxxx

docker logs Demo19New

The output will look like this:

xxxxxxxxxx

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, I'm going to fix 2017 by running this before I stop the 2017 container:

xxxxxxxxxx

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 initial solution.

Further Reading

The Top 5 Most Common SQL Server Performance Problems

Easily Extract Data From SQL Server for Fast and Visual Analytics With OmniSci