Introduction

As a DBA, do you ever wonder if there is a way to test different SQL Server versions/editions and on different OS without going through length preparations and installations? There is, and Docker provides an easy way.

This article describes how to run docker containers on Windows 10 for SQL Server 2017/ SQL Server 2019 on Linux, and SQL Server 2017 on Windows. It also covers how to share a data directory with the host for databases and how to start SQL Server Agent on containers.

Install Docker on Windows 10

Download Docker Desktop for Windows from https://www.docker.com/docker-windows. Simply follow the instruction to install the software. Once completed, on the taskbar, we should see the docker icon. (It could be in the hidden icons tray.)

By default, Docker is running in Linux container mode. If you want to run Windows container, switch it to Windows containers mode by right clicking the docker icon.

Click on the docker icon, and the docker window pops up. We can check current available images, running containers, etc. Also, use it to start/stop/delete containers, etc. I will not cover the docker GUI. In this article I will mainly use command lines to control containers.

For Linux containers

By default, Docker desktop runs in Linux container mode. Let us start with Linux then.

Download SQL Server Linux images

Open a cmd window, run following to download both SQL Server 2017 image and SQL Server 2019 image from Microsoft docker hub.

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

Run the docker images for SQL Server 2017 and 2019

Let’s map port 1433 on the host for SQL Server 2017, and port 1436 on the host for SQL Server 2019. We will also specify enterprise edition with the "MSSQL_PID=Enterprise" option. Without this setting, by default, a container runs developer edition.

A few caveats. First, make sure to set a complex password otherwise you will not be able to connect later as the container will not start. Second, make sure to use double quotation ("") for parameters, especially for SA_PASSWORD. I noticed, for Linux, the container exits in seconds if I use single quotation ('') or no quotation marks.

Here is the command to start a container:

docker run --name sql_2017 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=1Secure*Password1" -e "MSSQL_PID=Enterprise" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

To test connection, we can try to connect to the container interactively. This command will do that and run the sqlcmd.exe program:

docker exec -it sql_2017 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

Let's repeat this for the 2019 instance:

docker run --name sql_2019_1436 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=1Secure*Password1" -e "MSSQL_PID=Enterprise" -p 1436:1433 -d mcr.microsoft.com/mssql/server:2019-latest
docker exec -it sql_2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

Once this is run, we can run "docker ps -a" to see a list of containers. We can see both sql_2017 and sql_2019_1436 are running.

Connect to SQL Server instance using SSMS

Connect to the SQL Server 2017 instance using the default port, 1433, on the localhost.

Let’s check SQL Server version, it shows:

Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64) Jun 25 2021 14:02:48 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Linux (Ubuntu 16.04.7 LTS)

Connect to the SQL Server 2019 instance using port 1436 on the localhost.

Let’s check SQL Server version, it shows:

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Linux (Ubuntu 20.04.2 LTS) <X64>

Now, we have both SQL Server 2017 and SQL Server 2019 on Linux up and running. Congratulations!

For Windows containers

We first need to get ready for Window containers. Run the following code in a command window to remove the two containers we started earlier.

docker stop sql_2017
docker rm sql_2017
docker stop sql_2019_1436
docker rm sql_2019_1436

We can run "docker ps -a" to check if any containers are running. or simply check in the Docker window.

Next, let’s switch it to Windows containers mode by right click the docker icon and choose "Switch to Windows Containers...". Now we are ready to test Windows containers.

Note: Microsoft has pulled support for SQL Server on Windows containers. This code may not work at some point as the images are gone. If you still want SQL Server support on Windows, WinDocks, a third party, provides this.

Download the Windows image

Run the following in a cmd window to download the SQL Server 2017 image on Windows.

docker pull microsoft/mssql-server-windows-developer:2017-latest

You might notice that this Windows image is from GitHub, and the previous Linux images were from Microsoft docker hub (https://hub.docker.com/_/microsoft-mssql-server).

Run a Windows Container

Since I plan to share a folder on the host to the container, the folder c:\Pauline\database has been prepared on the host. This folder will be mapped to c:\data in the container.

Now, let’s start the Windows container by running the following:

docker run -d -p 1433:1433 --name sql-win -e sa_password=1Secure*Password1 -e ACCEPT_EULA=Y -e "MSSQL_AGENT_ENABLED=true" -v c:\Pauline\database:c:\data microsoft/mssql-server-windows-developer:2017-latest

If you are interested in knowing the IP of the container, run:

docker inspect --format '{{.NetworkSettings.Networks.nat.IPAddress}}' sql-win

Want to test your SQL Server connection using sqlcmd? I prepared a file, query.txt, under c:\Pauline\database (which is c:\data in the container). In the file, there is a simple query: select name from sys.databases.

Now let’s run the query using sqlcmd:

docker exec sql-win cmd.exe /C "sqlcmd -U sa -S localhost -P 1Secure*Password1 -i c:\data\query.txt"

It returns the list of database names as expected.

Connect to SQL Server using SSMS

As we did before, we can use SSMS to connect. I have started this container with the default port, 1433, mapped, so we can just enter "localhost" as the Server name to connect.

Let’s check the version. It returns:

Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64) Oct 19 2017 02:42:29 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Use SSMS to create a test database and specify data files with the path, c:\data. The data files show under the folder (c:\Pauline\database in my case) on the host.

Start SQL Server Agent

By default, the SQL Server Agent is disabled (Agent XPs disabled) even though we added  "MSSQL_AGENT_ENABLED=true" in the docker run command. Let’s enable the agent. In SSMS login as the sa user. Run the following:

EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'Agent XPs',1
GO
RECONFIGURE
EXEC SP_CONFIGURE 'show advanced options',0
GO
RECONFIGURE
GO

Now we are ready to start SQL Server agent by running the following:

docker container exec sql-win cmd.exe /C "net start sqlserveragent"

If you have question about this part, please google "SQL Server Agent XPs disabled". There are plenty of explanations.

Conclusion

This article shows how to get started with Linux and Windows containers running SQL Server. Hope this helps and have fun :)!

Editor's note: SQL Server Central has a stairway on using containers.