Using SQL Server on Docker

This is the second post on series related to the installation and configuration of Docker containers on the Microsoft Windows platform.
This series is for professionals who start their journey with the containers and use them with Microsoft SQL Server.

The goals that we would like to achieve today are as follows:

• Download SQL Server images to be used with Docker
• Run SQL Server on Docker
• Configure more than one SQL Server instance on Docker
• Access SQL Server instance using tools like SQL Server Management Studio and Azure Data Studio
• Restore database to SQL Server on Docker
• Perform database upgrade between different Cumulative Updates levels and upgrade from SQL Server 2017 to SQL Server 2019

Preparations

We are starting with the following assumptions:

1. All steps described in this first blog post has been configured.
2. The computer, where Docker is installed, has access to broadband Internet (needed for downloading Docker images)

Download SQL Server images to be used with Docker

As a first step, we need to check what SQL Server images are available on the Microsoft website. To check this I will run following PowerShell command:

invoke-webrequest https://mcr.microsoft.com/v2/mssql/server/tags/list

After running above command, you will get the following output:

As a second step, I will assign the output of the command to a variable and check Content property:

$repository = invoke-webrequest https://mcr.microsoft.com/v2/mssql/server/tags/list
$repository.Content

Following output should be shown:

If you want to download a specific version of SQL Server binaries (2017 with Cumulative Update 11 in our case), you need to run following command:

docker pull mcr.microsoft.com/mssql/server:2017-CU11

After some time (depending on your network connection), you should get the following output:

As we have images downloaded already, we can go to the next step – running first SQL Server on Docker

Run SQL Server on Docker

To run SQL Server on Docker, you need to provide a list of parameters that allows SQL Server to use proper settings and communicate with the external world outside Docker.

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=SecretP@ssw0rd' `
--hostname sql2017cu10 `
-p 1401:1433 `
-v sqlvolume:/var/opt/mssql `
--name sql2017cu10 -d mcr.microsoft.com/mssql/server:2017-CU10-ubuntu

Below there is an explanation of used parameters:

• -e (or -env) – sets environment variables, in our case we accept End User License Agreement and sets a password for “sa”
• –hostname – sets container hostname, we can omit this, then docker will generate its own name
• -p (or –publish) – publishes a container’s port to the host. In our case default SQL port (1433) is published on port 1401
• -v (or –volume) – binds mount a volume. It is the location of persistent storage for SQL Server databases
• –name – assigns a name to the container
• -d (or–detach ) – runs the container in background and print container ID

After a few seconds, a new container with SQL Server should be up and running.
To check the status of containers you can run the following command:

docker ps -a

You should see the following output:

Let’s check if T-SQL commands can be executed against Docker container:

sqlcmd '-Usa' '-Slocalhost,1401' '-Q"SELECT @@VERSION"' '-PSecretP@ssw0rd'

You should see the following output:

As you can see we have created a container with a version of SQL Server that we have specified is up and running.

Restore database to SQL Server on Docker

As a next step is to copy database backup to Docker container and restore this backup.
For coping database backup, I am running docker cp command:

docker cp C:\SQLBackup\WideWorldImporters-Full.bak sql2017cu10:/var/opt/mssql

After a few seconds, a backup should be successfully copied to the container:

Having a backup inside the container we can run T-SQL command to restore a backup to the volume specified during the start of container:

docker exec sql2017cu10 /opt/mssql-tools/bin/sqlcmd `
-S localhost `
-U sa `
-P 'SecretP@ssw0rd' `
-Q"`
RESTORE DATABASE WideWorldImporters `
FROM DISK = '/var/opt/mssql/WideWorldImporters-Full.bak' `
WITH MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf', `
MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_userdata.ndf', `
MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters.ldf', `
MOVE 'WWI_InMemory_Data_1' TO '/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1'"

After a few seconds, the familiar output should be visible saying that database backup has been successfully restored.

At this point, we have a container running SQL Server 2017 CU10 with database WideWorldImporters restored.

Configure more than one SQL Server instance on Docker

Next step is to run the second container of SQL Server with the latest version of SQL Server (CU17 at the time of creating this blog post)

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=SecretP@ssw0rd' `
--hostname sql2017latest `
-p 1402:1433 `
-v sqlvolume2:/var/opt/mssql `
--name sql2017latest `
-d mcr.microsoft.com/mssql/server:2017-latest

There are few changes comparing to the first container:

• hostname is different to match SQL Server binaries
• port is set to 1402, so we can connect to both instances if needed
• volume is configured to use different that first container
• name is different to match SQL Server binaries

The second container has been created, let’s check what is running on Docker (we expect to have to containers running):

docker ps -a

You should see the following output:

Our environment is up running, the next step is to check if both containers behave the same as “old-school” SQL Server on Windows. To check that we are going to use SQL Server Management Studio and Azure Data Studio

Access SQL Server instance using tools like SQL Server Management Studio and Azure Data Studio

The first tool that I am going to use is SQL Server Management Studio. After starting SSMS, I am trying to connect to the first container configured to listen on port 1401

After clicking on Connect, you are connected to the container with SQL Server

Probably you have noticed that icon of the instance is different (with the penguin as Docker is Linux based) and version of the binaries (after checking on this page you will find it is CU10).

Let’s try to run some basic queries on the instance and database levels:

Using T-SQL to check the version of SQL Server:

Let’s check WideWorldImporters database:

Till now all queries behave the same way as on the SQL Server installed within Microsoft Windows environment.
The only difference that you can find (except different icon) is visible within properties of the instance:

Let’s check the Azure Data Studio. As a first step, let’s connect to the container:

Now create a new notebook:

Create the first Text cell with description and add code with T-SQL statements

You can execute T-SQL statements by clicking on Run cells or by selecting a single cell and pressing F5

As of now, we have created and started a container where SQL Server is running with the user database.
The next step is to perform database update to a higher version of SQL Server binaries

Perform database update between different Cumulative Updates levels and upgrade from SQL Server 2017 to SQL Server 2019

Till now we have used two containers with two versions of SQL Server binaries (CU10 and CU17). The aim of this paragraph is to show a situation when you would like to perform compatibility tests of your database with a higher version of SQL Server binaries – testing before actual update to the latest version.
High-level steps look as follows:

1. Stop container with the current version of SQL Server binaries
2. Stop container with the latest version of SQL Server binaries
3. Attach volume with a database to the container with the latest version of SQL Server binaries
4. Perform tests on the database that is running on the latest version of SQL Server binaries

Let’s start with stopping the container with SQL Server 2017 CU10. To achieve this run following command:

docker stop sql2017cu10

Let’s create a new container for our test

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=SecretP@ssw0rd' `
--hostname sql2017test `
-p 1401:1433 `
-v sqlvolume:/var/opt/mssql `
--name sql2017test `
-d mcr.microsoft.com/mssql/server:2017-latest

Process of the startup of a new container takes maximum few seconds

However, when you try to connect to an instance, you will get the following message:

This message clearly says why you can’t connect to the instance – just after the startup of SQL Server, an update of the database is performed to the same version as the version of the master database (thus version of the database instance). After some time (in my case it was about 40 seconds), you can normally connect to the instance:

Version 14.0.32381.1 clearly indicates that SQL Server is running on Cumulative Update 17 binaries.

After examination of SQL Server Log, you will discover that upgrade script has been executed to an instance to update it to mentioned CU level

As you can see, performing an update from one CU level to the other is quite straight-forward.

Now, let’s check if it is possible to upgrade our database from SQL Server 2017 to SQL Server 2019. To achieve this, high-level plans look similar to the previous one, with the exception that a new container will be using SQL Server 2019.

Let’s start with stoping currently running container:

docker stop sql2017test

There is no output, as shown below

Let’s run a container with SQL Server 2019 by executing the following command

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=SecretP@ssw0rd' `
--hostname sql2019 `
-p 1402:1433 `
-v sqlvolume:/var/opt/mssql `
--name sql2019 `
-d mcr.microsoft.com/mssql/server:2019-latest

So far, so good, the new container has started:

However, when you check the status of the container, you will notice that container with SQL Server 2019 is stopped:

Would be nice to have the option to check what is the reason for this situation. There is a handy command to check logs of the container:

docker logs sql2019

After execution of this command, it starts to be more clear why SQL Server has been stopped just after start:

In the output of the command, there is a link to Microsoft’s website with a possible solution to the issue.

I have found that solution below fixed my issue:
1. Run the previous version of a container (SQL Server 2017 CU 17 in my case)
2. Change ownership of the directory where databases are stored to allow users in the root group to access them in the built image
3. Copy access permissions on the directory where databases are stored from user to group

docker start sql2017test
docker exec -it sql2017test "bash"
chgrp -R 0 /var/opt/mssql
chmod -R g=u /var/opt/mssql
exit

Commands should be executed with any issues

Let’s try to upgrade to SQL Server 2019

docker stop sql2017test
docker start sql2019
docker ps -a

The expected output is shown below

Everything looks good, let’s check our instance and database using notebook created earlier

Query executed on instance level show correct build version (expected 2019), let’s check our database:

Here also everything works as expected.
As a last try let’s check if it is possible to downgrade our database from SQL Server 2019 to SQL Server 2017. To achieve this we need to execute the following steps:

1. Stop container with SQL Server 2019
2. Run container with SQL Server 2017
3. Try to connect to instance and query instance and database

Following commands needs to be executed

docker stop sql2019
docker start sql2017test
docker ps -a

When you have look at the output, you will see that the container was up and running for a few seconds and then stopped.

As learned earlier, let’s check what is within logs of the container by running this command

docker logs sql2017test

At the beginning of the log, everything looks normally

But at the very end, there is an explanation of the container shutdown

As stated in the message, you can only upgrade the database, a downgrade is not possible.

Clean-up

After all activities that we have performed on the Docker, sometimes it is required to clean-up resources (both running containers burning CPU and images taking disc space). As a first step let’s list all containers

docker ps -a

In my case all containers are stopped:

You can remove containers by providing name or container-id

docker rm sql2017downgrade
docker rm c182dcc1fa36

No output means that commands have been executed successfully

In my case, there were three more containers to be removed

docker rm sql2017cu10
docker rm sql2017latest
docker rm sql2017test

After executing these commands, I don’t have any container on my Docker.
Next step is to remove volumes created during container startup

docker volume rm sqlvolume
docker volume rm sqlvolume2

Execution of the command should take less than a second

The last step is to remove images downloaded from the repository. Let’s check if there are any images downloaded already

docker images

On my machine, there are the following images

To remove image just run following command

docker rmi ba266fae5320

Output looks following

You can repeat the same command for the other images.

Summary

As you can see using containers available within Docker gives many possibilities of the rapid development of code on a different version of SQL Server binaries without a need to the tedious process of setting up the virtual machine and configuration of SQL Server instance. These possibilities allow developers to use their own workstation and develop required changes at a much faster pace compared to the previous approach with requesting a refresh of the lower region using the latest Production backup (that can take days instead of minutes on Docker).  Docker gives also much lower overhead compared to a virtual machine thus is a perfect solution that should be considered by every developer.

Links

SQL Server 2017 build versions
Build and run non-root SQL Server 2017 containers