{{announcement.body}}
{{announcement.title}}

Containers: Upgrading SQL Server From 2017 to 2019 RTM

DZone 's Guide to

Containers: Upgrading SQL Server From 2017 to 2019 RTM

In this article, see how to upgrade SQL Server from 2017 to 2019 RTM.

· Database Zone ·
Free Resource

Person with headphones coding at monitor with

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:

Java


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

Java


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

Java


The output will look like this:

Java


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:

Java


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

Topics:
database ,tutorial ,containers ,upgrading sql server ,sql server

Published at DZone with permission of Grant Fritchey , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}