Windows containers: installing SQL server

Posted in: Microsoft SQL Server, Site Reliability Engineering, Technical Track

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

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.

Creating an Inboud Security Rule

 

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 http://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 'http://download.microsoft.com/download/9/2/C/92C2988E-A75A-40D1-9804-F8B11BE3AA7F/SQLServer2016-x64-ENU.exe' -outfile 'SQLServer2016-x64-ENU.exe'
wget -uri 'http://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.

email

Interested in working with Scott? Schedule a tech call.

20 Comments. Leave new

How big was the image when you were finished!

Reply
Scott McCormick
November 4, 2015 10:21 am

It was about 12 GB, I think. Not too bad, but I can’t remember if I removed the installer now…

Reply

Is the image on dockerhub somewhere we could give it a try?

Reply
Scott McCormick
November 6, 2015 8:43 am

No. Unfortunately, as of Windows 2016 CTP 3, which is what I used for this post, DockerHub connectivity isn’t supported.

Reply

great article,
as to including framework 3.5 try this approach, copy source\sxs folder from win server media to your container, for example in dockerfile using ADD
after building the container, within it go to powershell and Install-WindowsFeature -Name NET-Framework-Features -IncludeAllSubFeature -Source C:\mssql\source\sxs\ <—— this is where I copied sxs folder.

Reply

A few things:

Stuck on Step #4 since install-windows feature can’t connect to Windows Update and there is no easy way to attach ISOs to an Azure VM.

The link in Step #5 is dead, use thse instead:
http://care.dlservice.microsoft.com/dl/download/F/D/1/FD1242AC-06FF-4D95-A827-DE5B5978DE08/SQLServer2016-x64-ENU.exe
http://care.dlservice.microsoft.com/dl/download/F/D/1/FD1242AC-06FF-4D95-A827-DE5B5978DE08/SQLServer2016-x64-ENU.box

Reply
Scott McCormick
November 19, 2015 6:11 pm

Hey Byron –

Thanks for the links.

I’m not sure why you can’t get to Windows Update from an Azure VM. I didn’t have any issues with that piece. Maybe your firewall is configured to stop the connections?

Scott

Reply

This is great, thanks Scott. Here is my next question, where do we store our database files (MDF) on an external volume or inside the container? How do manage that data if we change the container or god forbid delete the container? How do you manage users access to the databases sql users? AD users? I can’t seem to find anything on this anywhere online. All this is great demoware but when we want to use it in a real world scenario it seems to fall apart.

Thanks for replying.

Reply
Scott McCormick
December 8, 2015 11:38 am

Hi Etienne –

I’ve forwarded your question to one of our MySQL experts here and we’ll see if he has anything to add. Docker’s been around a lot longer for Linux, so hopefully we can avoid their growing pains somewhat.

In the meantime, I’d recommend checking out the documentation on data volume containers. These allow you to keep your data separate from the base container image, and can used to backup/restore/migrate the data as well.

As far as users, I’m not aware of any differences in managing them over a ‘normal’ instance, so I guess the best practice would be to use AD users wherever possible. In my testing, I used just SQL Logins. At the end of the day, this is a Windows Core server, so you should be able to connect it to your local domain or Azure’s AD for authenticating logins. Here’s the powershell cmdlets for AD connections.

Reply

Thanks again Scott. I’ve read it wasn’t a good idea to tinker with AD in containers since they are volatile (don’t stay around) I haven’t tried attaching one to a domain yet. I’ll try it. If we can’t “attach” it to a domain then we need to use local users which is not great for connecting to SQL or any other service that require AD authentication.
For the data, I’ve read that article and was thinking that it was the way to do it also, but also reading the fact that stopping a container sometimes stops not nicely makes me scared about the data. Seeing that we need to attach the database to SQL it also feels that not keeping the data in the container is dangerous if we kill the container without unattaching the DB.
Things to ponder on it this brave new world of containers. Thanks for the answers. I like this conversation.
Cheers,
ET

Reply

The .NET 3.5 step is way easier now, using the on demand .cab file.

https://msdn.microsoft.com/en-us/virtualization/windowscontainers/examples/dotnet35

Reply

I am getting error while building the docker image. any ideas?

C:\>docker build -t mssql2016 c:\mssql
Sending build context to Docker daemon 3.028 GB
Step 1 : FROM windowsservercore
—> 6801d964fda5
Step 2 : LABEL Description “SQL Server” Vendor “Microsoft” Version “13.00.500.53”
—> Using cache
—> eb083e9c5ce5
Step 3 : ADD installfolder/SQLServer2016-x64-ENU /mssql/install
GetFileAttributesEx installfolder\SQLServer2016-x64-ENU: The system cannot find the file specified.

Reply
Scott McCormick
February 18, 2016 8:43 am

You’re certain the installfolder\sql… folders all exist? They don’t have an extension?

Reply

Thanks. figured it out. i didn’t have right folder name there

Reply

another question though. I am able to connect to sql server from ssms after step 14. But, after i commit image and re-run the container, i am not able to connect to server. yes. i already ran this
“sc config MSSQLSERVER obj=LocalSystem”

Reply

got this error when building docker image. any ideas?

PS C:\mssql> docker build -t mssqlserver2016 c:\mssql
Sending build context to Docker daemon 3.188 GB
Error response from daemon: Syntax error – can’t find = in “Server\u0094”. Must be of the form: name=value

Reply

I found the issue is double quotation marks
LABEL Description=”SQL Server” Vendor=”Microsoft” Version=”13.00.500.53?

should be correct to :

LABEL Description=”SQL Server” Vendor=”Microsoft” Version=”13.00.500.53″

Reply

I mean if you copy the dockerfile from the website directly, please remember to correct the double quotation marks.

Reply
Wayne Bradney
June 9, 2016 12:58 pm

Is this supposed to work with the RTM version of SQL 2016? When I run the silent install on a clean microsoft/dotnet35:windowsservercore container i get:

(01) 2016-06-09 10:43:34 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys. —> Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException: The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.

Reply

got all the way through this article and attempted to connect to the sql container via the ip address of the local host. Get the following error: Cannot connect to -> Verify that the instance name is correct and that SQL Server is configured to allow remote connections. -> the system cannot find the file specified

I have all the firewall rules set up, and double checked. Not sure how to resolve this one.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *