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: –
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:-
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:-
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).
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.
10 thoughts on “SQL Server & Containers – Part One”
Please can you explain that if you get
Error response from daemon: repository microsoft/mssql-server-2016-express-windows not found: does not exist or no pull access
its the wrong image name and change the image name in your example as that confused me for a while until I understood how to search so probably add in the instructions for logging into Docker to enable searching
PS get a domain name 😉
Yep, microsoft have renamed the images so have dropped a comment in so that you can search for an image that you can use. Thanks!
Thank you – Makes it easy for people like me to follow along 😉
No problem. I’m chuffed that you’re reading it!