SQL Server & Containers – Part One

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.

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: –

server2016_enablecontainers2

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?

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.

Advertisements

10 thoughts on “SQL Server & Containers – Part One

  1. 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 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s