Fun with SQL Server Containers

After attending Eton Stoneman’s session at Techorama in the Netherlands I decided to have some fun with SQL Server Containers on Windows.

The first thing I did was install the latest version of Docker for Windows. As of this post I was using Version 18.06.1-ce-win73 (19507).

I also switched it to run Windows containers. By default, it is configured to run Linux containers. Simply right-click on the Moby icon in your System Tray and select Switch to Windows containers…

With Docker ready, I pulled the image with the 2016-sp1 tag to play with.

docker pull microsoft/mssql-server-windows-express:2016-sp1

That command might take a while as the image is very big. Once the image is pulled you can start spinning up SQL Servers.

docker run -d -p 1433:1433 -e ACCEPT_EULA=Y -e SA_PASSWORD=P2ssw0rd --name mssql microsoft/mssql-server-windows-express:2016-sp1

To connect to the SQL Server using SQL Server Management Studio (SSMS) you first must find the IP Address of the container. If you are using PowerShell you can do that using the command below:

(docker inspect mssql | ConvertFrom-Json).NetworkSettings.Networks.nat.IPAddress

If you are not using PowerShell just issue an inspect command and find the IP Address in the JSON returned.

With the IP Address open SSMS and enter in the IPAddress followed by “\SQLExpress”. Switch the Authentication dropdown to SQL Server Authentication. Enter “sa” as the Login and the password used during the run command.

Clicking Connect will connect you to your new instance of SQL Server!

If you want to run multiple instances of SQL Server on the same machine, simply update the port mapping in the run command. For example, the command below will start a second instance for SQL Server with a container port of 1466.

docker run -d -p 1466:1433 -e ACCEPT_EULA=Y -e SA_PASSWORD=P2ssw0rd --name mssql2 microsoft/mssql-server-windows-express:2016-sp1

When connecting with SSMS you still only need to provide the IP Address. You do not have to provide the port. During my testing trying to provide the port SSMS would fail to connect.

Using SQL Server in a Docker container opens up a world of possibilities when it comes to creating datasets for integration testing and testing schema updates.

Comments (1) -

  • Nilesh Gule

    11/14/2018 5:08:47 AM | Reply

    Docker images for Windows containers are usually bigger in size. I tried to use the Linux versions mostly due to its lightweight images. I built a demo app with SQL Server 2017 Linux as database in a multipart series which also showcased the capabilities of Azure Kubernetes service for deploying the containers on cloud.

    As part of this series we also looked at advanced concepts like state management using Persistent Volumes and Persistent Volume Claims, Azure Disk based storage and secrets management.

Add comment