Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes – Another Method

Yesterday in this post I described a method to correct permissions when upgrading a SQL Server 2017 container using Data Volumes to 2019’s non-root container on implementations that use the Moby or HyperKit VM. My friend Steve Jones’ on Twitter wondered if you could do this in one step by attaching a shell (bash) in the 2017 container prior to shutdown. Absolutely…let’s walk through that here in this post.  I opted to use an intermediate container in the prior post out of an abundance of caution so that I was not changing permissions on the SQL Server instance directory and all of the data files while they were in use. Technically this is a-ok, but again…just being paranoid there.

Start Up a Container with a Data Volume

Start up a container with a Data Volume (sqldata1) using the 2017 image. This will create the directories and files with root as the owner and group.

docker run \
    --name 'sql1' \
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
    -p 1433:1433 \
    -v sqldata1:/var/opt/mssql \
    -d mcr.microsoft.com/mssql/server:2017-latest
597652b61b22b27ff6d765b48196621a79dd2ffd7798328868d2296c7e953950 

Create a Database

Let’s create a database and confirm it’s there.

sqlcmd -S localhost,1433 -U sa -Q 'CREATE DATABASE TestDB1' -P $PASSWORD
sqlcmd -S localhost,1433 -U sa -Q 'SELECT name from sys.databases' -P $PASSWORD -W

name
----
master
tempdb
model
msdb
TestDB1

(5 rows affected) 

Get a Shell into the Container

Now, let’s get a shell into our running container. Logging in as root is great, isn’t it? :) 

docker exec -it sql1 /bin/bash
root@ed9051c6b5f3:/# 

Adjust the Permissions

Now while we’re in the running 2017 container we can adjust the permissions on the instance directory. The user mssql (uid 10001) doesn’t have to exist in the 2017 container. The key to the permissions is using the uid directly.

ls -laR /var/opt/mssql
chgrp -R 0 /var/opt/mssql
chmod -R g=u /var/opt/mssql
chown -R 10001:0 /var/opt/mssql
ls -laR /var/opt/mssql
exit

Stop our Container

Now to start the process of upgrading from 2017 to 2019, we’ll stop and remove the existing container.

docker stop sql1
docker rm sql1
sql1 

Start up a 2019 non-root Container

docker run \
    --name 'sql1' \
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
    -p 1433:1433 \
    -v sqldata1:/var/opt/mssql \
    -d mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04 

Is Everything OK?

Are our database there? Yep! 

sqlcmd -S localhost,1433 -U sa -Q 'SELECT name from sys.databases' -P $PASSWORD
name
----
master
tempdb
model
msdb
TestDB1

(5 rows affected)