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.

Now your server is fully patched you need to enable the Containers feature: –

Then once that’s done the Docker engine needs to be installed. Open up an administrative powershell prompt and run the following: –

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
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/

Once the server has restarted you will have the Docker Engine running as a service. This can be verified by running:-

docker version

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-2016-express-windows

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 sa_password=Testing11 microsoft/mssql-server-2016-express-windows

N.B.- Notice the values after the -p flag. What this is doing is allowing SQL to listen on port 1433 (the default) in the container but be available on the host server on port 15789.

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?

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 –format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ 6dc

N.B. – You don’t have to type out the entire ID to refer to a container, the first three digits are all that’s needed as I’ve done above with 6dc

So my container has a private IP address of 172.30.47.32 on the host server. To connect via SSMS I just enter 172.30.47.32,1433 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.