Containers: Upgrading SQL Server From 2017 to 2019 RTM
In this article, see how to upgrade SQL Server from 2017 to 2019 RTM.
Join the DZone community and get the full member experience.Join For Free
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?
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:
In the past, we could stop this container and start another using 2019, like this:
Reusing the same volume between the two containers means any databases created would update. However, if we check the log like this:
The output will look like this:
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:
Now, when I stop the 2017 container and start the 2019 container, the upgrade occurs normally because of the changes to permissions.
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.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.