Windows containers: installing SQL server

This blog post is a quick introduction to Containers in the Windows world, and a walk-through on installing SQL Server in a Windows Container.

Introduction

As many of you have heard, Microsoft is jumping into containers with native support for Docker containers in Windows 2016. Containers are the current big thing in virtualization, Linux, and DevOps, because the are very light-weight and allow you to quickly create a new environment without having to wait for a VM to be deployed and provisioned by the server team. I expect them to be just as useful and ubiquitous in the Windows world very soon.

Hypervisors are based on emulating hardware, and so they can be very resource intensive. At a minimum, they’re required to have an entire Operating System, CPU, RAM, and some drives assigned before they’re useable, and that’s often overkill for a VM running a single application. Containers, by contrast, virtualize only the OS level and share the kernel libraries between them, and you don’t need to worry about the rest. Containers are small and light-weight enough, that you can expect to run 4 to 6 times as many containers vs VMs on one host.

This MSDN Blog Post goes into detail on containers and their differences from VMs.

It’s important to note that containers are meant to run a single application and do not have GUI interfaces. So, everything must be run via the command line or a remote connection.

Why use containers for SQL Server?

1. You need to quickly create a set of SQL Server instances for development or testing.
2. Your company runs a Software-as-a-Service and wants to separate clients into different environments while squeezing everything they can from their hardware.
3. You want to be able to share development environments without everyone getting in each others way.
4. Your VM or Server team just isn’t very good, and they take forever to get you what you need.

Installing SQL Server in a Windows Container

The following is a walk-through for installing SQL Server in a Windows Container. You might want to reference the Docker documentation for more details on the commands I use.

When you’re done with the tutorial, try to get multiple containers and their instances of SQL Server running on the same box.

Step 1: Create a New Server

The server should be running Windows Server 2016 Technical Preview 3 (or higher) Core with the Container role enabled.

I used Azure’s “Windows Server Container Preview” VM for this tutorial, which luckily has the Host OS all setup for me.

* A quick note for anyone who hasn’t used Windows Server Core before: Open Task Manager and use File–Run New Task to get new CMD windows.

At this point, you should also create a new directory structure in your VM:

C:\mssql\install\registrykeys

Creating an Azure VM

Step 2: Configure Azure Security Rules

If you’re using Azure, you need to define the Inbound Security Rule for this port. To get there in Azure:
From the VM’s main blade click: All Settings — Network Interfaces — [Interface Name] — Network Security Group — All Settings — Inbound Security Rules.

The default rule to allow RDP traffic will be there. Create another rule to allow SQL Server traffic. For reasons I don’t understand, setting the port to 1433 here doesn’t work. You need to open it up, and hope your firewall is up to date.

Step 3: Configure Windows Firewall

Run the following in Powershell on your host to open the right ports. I’ll be using the default port 1433:

if (!(Get-NetFirewallRule | where {$_.Name -eq "SQLServer 1433"})) {
New-NetFirewallRule -Name "SQL Server 1433" -DisplayName "SQL Server 1433" -Protocol tcp -LocalPort 1433 -Action Allow -Enabled True
}

Step 4: Enable .Net 3.5 Framework

This is a hassle. The base Windows image that Microsoft provides does not have .Net Framework 3.5 enabled. So, you need to enable it in the container which should be easy enough, and we’ll get to that. Unfortunately, for reasons that I do not understand, when attempting to install .Net 3.5 in the container, it doesn’t use WindowsUpdate and fails. If you have a Windows .iso file (which I don’t), you can theoretically point the below command at it from within the container, and it should work.

The “fix” is to enable .Net 3.5 on the host, export the registry keys, and then import them into the Container’s registry. This tricks the SQL Server installer into thinking you have it enabled. Does SQL Server 2016 need anything in the .Net 3.5 SP1 Framework? Probably!

Seriously, I spent hours banging my head against this thing and if you can figure out how to get out to https://update.microsoft.com from your container, please let me know.

Enable and upgrade the .Net 3.5 Framework on the host server by running the following commands within Powershell. You don’t need to do this if you have a Windows .iso file because we’ll be installing it in the container later.


get-windowsfeature -name NET-Framework-Features | install-windowsfeature
get-windowsfeature -name NET-Framework-Core | install-windowsfeature


Using regedit, export the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v3.5 registry keys and save them as C:\mssql\install\registrykeys\registry.reg

Step 5: Download and Extract SQL Server Installation Files

Run the following commands in Powershell on your host to download the SQL Server installers. Change URLs as needed…


wget -uri 'https://download.microsoft.com/download/9/2/C/92C2988E-A75A-40D1-9804-F8B11BE3AA7F/SQLServer2016-x64-ENU.exe' -outfile 'SQLServer2016-x64-ENU.exe'
wget -uri 'https://download.microsoft.com/download/9/2/C/92C2988E-A75A-40D1-9804-F8B11BE3AA7F/SQLServer2016-x64-ENU.box' -outfile 'SQLServer2016-x64-ENU.box'


Run the executable and save the files at C:\mssql\install. You should delete or move the .exe & .box files as well.

Step 6: Create SQL Server Configuration File

As mentioned earlier, containers don’t allow any GUI interfaces, so SQL Server has to be installed silently. In addition, not everything in SQL Server is supported on Windows Server Core.

I used this configuration file. If you use the same one, make sure you change the password (search for CHANGEME).

Please put your configuration file at C:\mssql\install\configurationfile.ini.

Step 7: Create your dockerfile

Docker uses the dockerfile as a configuration file and to ensure images are built exactly the same every time.

Take the below code and save it in a text file as c:\mssql\dockerfile

The lack of extension is on purpose. This isn’t a new folder. If Windows insists on saving the file with a .txt extension, which happened to me a couple of times, use the Powershell rename-file command and remove the extension.


#Define the base image we'll be building everything else off of...
FROM windowsservercore

#Give it a label
LABEL Description=”SQL Server” Vendor=”Microsoft” Version=”13.00.500.53″

#
#These files and folders will be imported into the docker image and be available for us to use.
#
ADD install/SQLServer2016-x64-ENU /mssql/install
ADD install/configurationfile.ini /mssql/install/configurationfile.ini
ADD install/registrykeys/registry.reg /mssql/registrykeys/registry.reg

Step 8: Build Docker Image

At this point, everything you need to install SQL Server should be staged somewhere underneath the c:\mssql directory and you should have a reference to each file or the folder in your dockerfile.

To build the docker image, run this:

docker build -t mssql2016 c:\mssql

This command tells docker to build an image with a name of mssql2016, and that the dockerfile is located in the c:\mssql folder.

While it’s running, open up Task Manager and watch how much CPU & RAM it uses. Also, get some coffee and check your email. You’ve got time.

Step 9: Verify

After the build completes, run the below command to see all of the images you have available. It should be a magical rainbow of three choices.

docker images

Step 10: Run your image

This command will run your image

docker run -it --name mssqlContainer -p 1433:1433 mssql2016 cmd

Let’s walk through each of these parameters:

• it | Runs an interactive psuedo-terminal.
• name | The name of your running container.
• p 1433:1433 | This binds port 1433 on the host to port 1433 on the container process.
  • In other words, any traffic coming into the host on port 1433 will be forwarded to the container’s port 1433.
• mssql2016 | The name of the image you want to run.
• cmd | The utility that you’ll be running.

Step 11: Enable .Net 3.5 Framework

As mentioned back in Step 4, this is a hassle.

We need to import the registry keys into the Container’s registry to trick the SQL Server installer into thinking we have .Net 3.5 SP1 installed. IN ADDITION, we need to enable as much as possible of the actual .Net 3.5 framework so it’s at least sort of usable. So, run the following commands to enable .Net 3.5 and import the registry keys.


DISM /online /enable-feature /featurename:NetFx3ServerFeatures
reg import C:\mssql\registrykeys\registry.reg


Step 12: Install SQL Server in a Windows Container

Navigate to C:\mssql\install and run the below command to install SQL Server using the values setup in your configuration file.

setup /IAcceptSQLServerLicenseTerms /ConfigurationFile=configurationfile.ini


Step 13: Fix the installation

At this point, the SQL Server instance should be up and running. Unfortunately, there’s a good chance the next time you start the container that the instance will not come up.

Here’s a blog post talking all about what happens. It appears to be due to how the container shuts down the underlying processes (or doesn’t).

The quick fix is to go against every best practice document and run SQL Server under LocalSystem.

sc config MSSQLSERVER obj=LocalSystem

Step 14: Connect to SQL Server

As a test of the instance, you can use OSQL from the command line to verify it’s up and running.
C:\Program Files\Microsoft SQL Server\130\Tools\Binn>osql -E

From your local machine, connect to the SQL Server instance. You should use your host server’s IP address (the Public IP address in Azure).

Congratulations! (but you’re not done yet)

Step 15: Save your work

Boy, it sure would be a shame if something happened to that nice, new SQL Server installation you’ve got.

Once you’re done playing with the instance, head back to the command window with access to your container. I recommend attempting to cleanly stop the container. From another command window on the host, run:


docker ps
## The output from docker ps will give you a ContainerID. Use it in the stop command.
docker stop [ContainerID]

Alternatively, just type exit as many times as necessary to get back to the host’s command line, and the container will shut down very poorly.

Type this to commit the new image to your repository
docker commit [ContainerID] mssql2016:Installed

This will save your container locally and give it a new tag of Installed. This will also take some time.

To start it again, use:
docker run -it --name mssqlcontainer mssql2016:Installed cmd

Discover more about our expertise in SQL Server.