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.