Containers are a hot topic at the moment, there seems to be a new article about them on Hacker News every day and now that Microsoft is supporting containers on Windows Server 2016 I thought it was time to have a look for what that means for us DBAs.

The question is can SQL Server run in a container?

Well, yes! SQL Server can run in containers. I have to admit that the ability to quickly create new instances of SQL Server without having to run through a lengthy (no matter how much you automate it, come on…it’s quite lengthy) install process is very appealing. This would be very useful for dev/qa environments where they need new instances spun up on a regular basis.

So how do you create a SQL container?

The first thing to do is get yourself a copy of Windows Server 2016, install it in a dev environment and get it fully patched. Btw Windows Updates are no longer located in the Control Panel options in Windows Server 2016, go to Settings > Update & Security (same as Windows 10 wouldn’t you know?).

For simplicity I’m going to be using an installation of Windows Server 2016 with the Desktop. You can do this on a core installation but as i’m used to a GUI, I’ll use the GUI.

WARNING! For some reason certain commands fail when copying and pasting. If a command fails try typing it out manually and re-running.

EDIT – If running on Windows 10 you’ll need to install via the .msi from the Docker Store

So the first thing to do is install the docker engine, this only requires two powershell scripts to be run and then a restart of the server. Open up an administrative powershell prompt and run the following: –

Install-Module -Name DockerMsftProvider -Force
Install-Package -Name docker -ProviderName DockerMsftProvider -Force
Restart-Computer -Force

Code source – https://blog.docker.com/2016/09/build-your-first-docker-windows-server-container/

The last line of code will restart the server. Once it’s back up you can verify that the containers feature has been enabled: –

And then you can verify that the Docker Engine is up and responding to request by running: –

docker version

So now let’s find an image in the Docker Hub that we want to use to build containers. To do this, run: –

docker search microsoft/mssql

Now it’s time to build a container that’s running SQL Server 2016 (I’m going to use the SQL 2016 Express Edition image for this demo). Docker requires images to build containers so first thing is to get the image: –

docker pull microsoft/mssql-server-windows-express

Once this is complete you can view the image downloaded:-

docker images

Now we can create a container by running:-

docker run -d -p 15789:1433 --env ACCEPT_EULA=Y ––env sa_password=Testing11@@ --name MyFirstContainer microsoft/mssql-server-windows-express

N.B.- Notice the values after the -p flag. What this is doing is mapping port 1433 (the default) in the container to port 15789 on the host. For more information have a look here.

When that completes, you can view details of the new container by running:-

docker ps

So now we have a container running SQL Server 2016, but how on earth are we going to connect to it?

Update – April 2018
Loopback has now been enabled for Windows containers, so we can use localhost,15789 to connect. You can read more about it here

Well there’s different ways of connecting depending where you are connecting from. If connecting locally you need to find the private IP address assigned to the container. This can be found by running:-

docker inspect MyFirstContainer 

So my container has a private IP address of 172.26.58.109 on the host server. To connect via SSMS I just enter 172.26.58.109 into the connection, enter the user sa & password and boom! I’m in:-

But what about connecting remotely? This isn’t going to be much use if we can’t remotely connect!

Actually connecting remotely is the same as connecting to a named instance. You just use the server’s IP address (not the containers private IP) and the non-default port that we specified when creating the container (remember to allow access to the port in the firewall).
Easy, eh?

Hmmm, I imagine you’re saying to yourself. That’s all well and good but it’s a bit involved and I don’t really see what benefit I’m going to get from setting this up. Well, don’t worry, I’ll cover actually using the software and what benefits it brings in Part Two.