SQL SERVER – Docker Volume and Persistent Storage

I have received some rave reviews of my article on SQL SERVER – How to Get Started with Docker Containers with Latest SQL Server?. It was amazing to hear that people really get started after reading my article on docker. One of the most popular questions I keep on receiving was what happens when we have to patch or upgrade SQL Server. Well, that is a fantastic question. In today’s blog, I will explain to you how we can get Docker Volume and Persistent Storage.

Docker and Setup

Before you start reading the blog post, I strongly suggest that you read my previous blog post in detail as I am not going to repeat any steps from the previous blog post about Getting Started with Docker Containers.

In my previous blog post, I provided script where we have storage which is mapped to my Windows Drive and when I remove my container all the data, etc will go away and whenever I install new container, I will have to repeat all the steps.

Patching and Upgrading Challenge

For example, when I wrote the blog post at the time SQL Server 2019 CTP 2.3 was available. Now SQL Server 2019 CTP 2.4 was available. The challenge was if I upgrade to SQL Server 2019 CTP 2.4 I will have to reinstall all the sample databases.

Think about it, whenever we are upgrading SQL Server installed on Windows, we have a very long and tedious process where we take the database from the previous server and restore them in the newer server. Additionally, we have to move all the security, etc stuff again and the process is not that simple.

However, with the help of persistent storage of Docker which is commonly known as Volume, the problem is actually resolved. Let us a demonstration of the same.

Docker Volume

First of all, follow all the steps until we install SQL Server from the blog post here.

Now whenever we are going to install SQL Server, instead of mapping local Windows Drive in SQL Server, let us map a volume name, which we will be referring to the future installations of SQL Server for Docker.

To test out first we will download two of the latest images to docker for SQL Server.

docker pull mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

docker pull mcr.microsoft.com/mssql/server:2019-CTP2.4-ubuntu

If you have already downloaded the image, you will get messages as displayed in the image below.

Next, install SQL Server 2019 CTP2.3 with the help of the following command. Please make sure that you have mapped the local drive with the name, in our case we will name that as sqlvolume.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Pass@Word1" `
-p 21143:1433 --name sql19-23t `
-v sqlvolume:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu 

Now the command above will create a Volume Storage with the local drive. You can check the same with the following command.

docker volume ls -a

From the above result, we got the name of our container as 8617cc9fab1b. We will use that container name in our copy command as a prefix to the folder where we want to move our backup files.

Next, we will copy our sample databases from our local drive to container. However, before we do that we need the name of our container. Let us get that by running following command.

docker cp D:\Docker\AdventureWorks2017.bak 8617cc9fab1b:/var/opt/mssql/data/

docker cp D:\Docker\WideWorldImporters-Full.bak 8617cc9fab1b:/var/opt/mssql/data/

When you run above command the backup files are moved to Docker containers at the path which is mentioned over the link.

Now we will run following two SQL Commands to install our sample databases. Please note that following two commands we will run in the SQL Server Management Studio and hence first we will connect to our SQL Server on the port 21143.

Here are two PowerShell script to install our sample databases.

RESTORE DATABASE [AdventureWorks2014]
FROM DISK = N'/var/opt/mssql/data/AdventureWorks2017.bak'
WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks.mdf',
MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks_log.ldf',
NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [WideWorldImporters]
FROM DISK = N'/var/opt/mssql/data/WideWorldImporters-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',
NOUNLOAD, STATS = 5
GO

The above commands will successfully install SQL Server sample databases. Now go to SSMS and expand the database node in Object Explorer and here you will see two databases.

Now our task is to upgrade our database to SQL Server 2019 CTP 2.4. Let us see how fast and efficiently we can do the same. Remember in the traditional SQL Server method, this would require lots of logistics but in our case, it will be very quick.

First, we will stop the SQL Server which is running currently.

docker stop sql19-23t

Next, we will install start up SQL Server 2019 CTP 2.4 docker image by running the following command which will be using the same storage like SQL Server 2019 cTP 2.3.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Pass@Word1" `
-p 21143:1433 --name sql19-24t `
-v sqlvolume:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2019-CTP2.4-ubuntu 

Once the process is complete. Go to back to SQL Server Management Studio and disconnect from the previous connection. Wait for about 30 to 60 seconds and attempt to connect again. During the time, SQL Server will internally automatically upgrade SQL Server to the latest version of SQL Server which we just started.

Once connected, expand the database note and you will see that the same database which you had earlier installed in SQL Server 2019 CTP 2.3 now exists in SQL Server 2019 CTP 2.4. I have also tested by creating user objects in the same database and they all exist after upgrading to the latest version of SQL Server.

I guess that’s it. We learn how we can build a Persistent Storage with Docker Volumes for SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)