Behind the scenes : Creating a Microsoft SQL Server as a Windows / Docker Container

Introduction

This post is the first of a series in my journey to build a flexible / production ready MSSQL windows container. I thought this would have been a breeze with my experience on Docker for Linux, though I must admit running into multiple issues… This post will not provide you with a working container, as I’m still developing that one.

2016-07-06 14_23_15-kvaeschost01 - 104.45.23.120_3389 - Remote Desktop Connection

Once I deem it as production ready, it’ll be released to the community to be used freely. Though I want it to meet my personal quality standards, being that it should be stable and flexible enough to run in production mode.

 

Blueprint Braindump

For those who have been following me for a while (real life, twitter, yammer, linkedin, …); you probably know I’ve been preaching about MSSQL as a container for way too long. My personal vision was to have a MSSQL run in a container. The data should be located outside of the container, which would enable a (more/relative) easy path for the changes you want to implement.

kvaes-mssql-container-blueprint-docker-persistent-storage-identity-repository

So where volume mapping would be an option… I was also considering an integration with an external storage service. As an Azure fanatic, I (also) want to leverage the option of storing my data/temp files on Azure storage. This would provide my with total host independent storage persistence on Docker! For those who have been playing with Docker for a while, this is truly a powerful combination.

As a long term goal, I would like to see this running on a “serverless” platform. From what I have seen in the market, this is still an unreachable utopia/Walhalla at this point. So my current objective in that areas is to investigate the option of deploying this setup on a Service Fabric or to leverage the power of Rancher with Windows containers.

 

Why build another MSSQL docker image

Why did I build another MSSQL container image? To be honest… I did not like the ones I saw out there already. Not because I had a bunch of fanatic / religious reasons. I respect a lot of the contributors who create docker images. These guys/girls spend a lot of timing building them and giving back to the community!

Though in the windows ecosphere of containers, I noticed a lot of people who wanted to be “quick to the market”. Their interest wasn’t in building a good docker image. The premise was to show that it was possible. Where that kind of evangelism is good… My personal intake was more to create an image that was in line with what I would want to see on docker hub when looking for an MSSQL image.

 

Learning by being a copycat

Let’s check out the mysql container on docker hub… Here you can immediately see that everything revolves around versioning and having a flexible container. A lot of implementations I saw used environment variables for the install part, but did not consider that someone who deploys a container would want to tweak certain settings on deployment. A good example here is the password of the database, or the location where the files are stored.

Another thing I noticed, is that only a handful of people grasp the concept of the layers in docker. In short; Running multiple commands one-by-one, with a cleanup as last command will still result in a big image. The “trick” is to use a single command, which includes the cleanup.

On that area, I also saw a lot of people copying in the binaries via the “ADD”-command. This will directly violate the previous stated objective, and… it will annoy the hell out of you in CI/CD deployments. Do you really want the ISO/EXE files to be part of your source control? Whatever you are thinking, the answer is no!

 

Dockerfile : MSSQL on a Windows Container

From a high level, this is a copy of my current working copy for the MSSQL container.

# Software : MSSQL Server 
# Version  : 2016 Full (BYOL) 
FROM windowsservercore
MAINTAINER Karim Vaes
WORKDIR /

# Environment Parameters that are only relevant to build time
ENV sqlPort 1433
ENV sqlInstanceName SQLEXPRESS
ENV storageLocation "C:\MSSQLVOL"

# Expose Listening Port (TCP)
EXPOSE $sqlPort

# Location where MSSQL data is stored (in relation to data persistence)
VOLUME $storageLocation

# Install SQL
COPY sqlInstall.ps1 /
RUN powershell /sqlInstall.ps1 -sqlStaticPort %sqlPort% -sqlInstanceName %sqlInstanceName%

# Environment Parameters that can be changed at runtime
ENV sqlSaPassword MyNotSoSecretPassword!
ENV sqlDatabaseName myDatabase
ENV storageType "Local"
ENV storageToken "sv=2015-04-05&sr=c&si=kvaesmssql2016container&sig=HowAboutYouPutYourOwnKeyHere?"
ENV storageUrl "https://kvaesmssql2016container.blob.core.windows.net/sqlfiles"

# Startup Command + Initial Configuration
COPY sqlStart.ps1 /
CMD powershell /sqlStart.ps1 -sqlSaPassword %sqlSaPassword% -sqlInstanceName %sqlInstanceName% -sqlDatabaseName %sqlDatabaseName% -storageToken %storageToken% -storageUrl %storageUrl% -storageType %storageType% -storageLocation %storageLocation%

As you can see, I have several environment variables which relate to the build process. Apart from the $sqlPort, I could also move those to the sqlInstall script… Anyhow, these parameters are used at the moment the container is build. They are not used at runtime, so they are only relevant to someone who is building the container from the Dockerfile.

The environment parameters down below are the ones who will be used for initialization. So the user who wants to spin up this container will still have a set of parameters to tweak the deployment (like database name, sa password, etc). So I’ll pass them on to the CMD-command, which will have the needed logic to do an initialization upon first startup.

 

Issue : Port Exposure on Windows Containers

Something which caused me some headaches was that the port exposure on Windows works fundamentally different than on a Linux system! Some technical references ;

  • port exposure on windows = ? #15740
  • Windows Container port binding not working on Windows Server 2016 TP4 (using Docker) #21558
  • Windows Container with Docker: Cannot reach httpd in container from host (Win2016 TP5) #253

So what’s the issue? When exposing a port from a container to the outside world, underneath the system will do a NAT mapping. Though, for some odd reason, the windows implementation will not allow local connections to that port. However…, it will alow remote connections!

There is a workaround, where you can create a tunnel between the container address + container port towards your localhost + published port. Though this will come back to haunt you in the end… If you are considering this, trust me, you do not want to follow this path!

 

TL;DR

  • Building containers is easy. Building a production ready container which is usable to the entire community is much more difficult!
  • Networking in Docker has a different implementation when comparing Linux & Windows.
  • Notice the syntax in regards to variables in the Docker file example shown above… Those are no errors. It’s a mix of Docker native syntax & typical powershell syntax.
  • Containers are coming to the windows landscape! Do not be afraid and embrace them. Really… I’ve encountered the situation way too many times. Experiencing containers is believing. Once you see a lifecycle flow for the first time, you’ll start to wonder where that has been all your life!

2 thoughts on “Behind the scenes : Creating a Microsoft SQL Server as a Windows / Docker Container

  1. Thanks for the tip with the args! Wasn’t aware of that… One learns everyday. 🙂

    Microsoft has published one themselves indeed. What am I missing with that one?
    – MSSQL 2014 => not 2016
    – MSSQL Express => not standard/enterprise, where express has limitations
    – No volume for data persistence
    And I had mixed results building that image… Oddly enough.

    On another note, various streams have indicated that Microsoft will provide container images for their own software. So I’m not under the illusion that the one I’m building will be a heavily used one. For me it’s more a learning path and exploring the boundaries. Because, let’s face it… Databases are amongst the hardest things to get working in a dockerized world.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.