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)
The post Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes – Another Method appeared first on Centino Systems Blog.