How to run SQL Server Express 2016 in a Windows Container (Docker)

• Author
• Recent Posts

Follow me:

Anil Erduran

Anil Erduran is a principal consultant and subject matter expert for Hitachi Data Systems EMEA, based in London, UK. He is also a dual category Microsoft Most Valuable Professional in Cloud and Datacenter Management and Microsoft Azure. Anil can be found on Twitter @anil_erduran.

Follow me:

Latest posts by Anil Erduran (see all)

• Scalability and availability for Azure Web Apps - Wed, Jun 28 2017
• Managing Azure Virtual Machine scale sets - Wed, May 31 2017
• Azure virtual machine scale sets - Mon, May 8 2017

Container technology (the original Linux containers commonly known as LXC) has been around for a long time but became popular with the adoption of Docker. Docker brought significant changes to the existing container technology and automated the deployment of applications in containers by providing an additional layer of abstraction.

Running SQL Server in Docker

Some people call it the next version of virtualization; others think VMs and Containers have different use cases and it’s better to run them side-by-side. Microsoft also wants to be in this game and recently announced support for containers on Windows operating systems and Hyper-V VMs. Redmond didn’t only introduce the capability to run containers in Windows but also provided native Docker shell support. If you are familiar with Docker, you can keep on using it as before with native Docker commands, or you can chose our lovely PowerShell cmdlets for container tasks.

Microsoft is also doing a great job of contributing to communities and DockerHub. If you browse DockerHub for "Microsoft," you will find plenty of images ready to use. They also recently uploaded a SQL Server Express container image and made it publicly available on DockerHub.

You may ask why they are offering only Express edition rather than full SQL Server. Well, my answer would be "licensing issues." As you are probably aware, SQL Server Express is the free edition of SQL Server and does not create any licensing conflict in container scenarios. The full edition of SQL Server, however, requires you to have licenses in place. Microsoft is still looking for possible ways to license SQL Server hosted in containers. (This might not be happen anytime soon.)

The second question you may ask is whether running a database inside a container is a good idea or not. The discussion about which applications are a good fit for containers has been running for a long time. Some say that only stateless applications make sense for container scenarios as they don’t care about data. You can fire up hundreds of containers, do what you want, and then destroy them.

On the other hand, interest in stateful applications is growing, and people are looking at ways to leverage container technology for deploying stateful applications in order to improve bare metal efficiency. There are also some options to provide persistent volumes to containers. Therefore, this is not a basic question to which we can give an answer in one sentence.

Installing Docker

To begin installing SQL Server in a Windows container, you need to prepare your host for the container feature. To install Docker we'll use the OneGet provider PowerShell module. This provider will enable the container feature on your machine and install Docker—this will require a reboot.

Install-Module -Name DockerMsftProvider -Repository PSGallery -Force
Install-Package -Name docker -ProviderName DockerMsftProvider

Install container package

Now we are ready to use native Docker commands on our Windows box. Docker has a "search" command to search all publicly available images on DockerHub. Let me search for all available images related to "Microsoft".

Docker search microsoft

Searching Microsoft images on DockerHub

The search command returned several images with repository Name, Description, Stars, Official, and Automated columns. The Stars column is a way to measure the popularity of a particular image. The Automated column tells us if this image was built by Docker Hub’s Automated Build Process. The official column tells us if this image is managed by the upstream developer.

You can also use https://hub.docker.com to search for available images using a GUI.

Creating SQL Server Express container

In our case we are looking for a SQL Server Express 2016 image. The next command is Docker Pull which pulls the image from DockerHub to our container host.

docker pull microsoft/mssql-server-2016-express-windows

Pulling SQL Server Express image from DockerHub

The downloaded SQL Server Express image includes everything we need in order to run SQL Server Express in containers.

Now we are ready to build a new container using this image. The Docker Run command is our friend here. But along with Docker Run, we have to provide some additional parameters to customize and configure our SQL Server Express deployment. First, let me show you the full command:

docker run -d -p 1433:1433 --env sa_password=Password1* microsoft/mssql-server-2016-express-windows

The -d flag detaches the created container to the background.

The -p flag is for port mapping. In container scenarios, there are several network topologies and one of the most used one is the NAT network. In NAT networks, each created container gets a private IP address (172.16.0.0./12) and accesses the outside world through NAT on the host. In this case, port mappings need to be created between containers and host. You can configure port mappings with the –d flag at container creation time or you can specify them later on. For the above command, I’m creating a static mapping between port TCP:1433 of the host and TCP:1433 of the container. So the first part is for the host and the second part is for the container.

If you don’t provide a port number for the host, Docker will assign a random port (between 32768 and 61000) which will be mapped to the specified port number on the container. In my case, I wanted to specify 1433 for the host as I want my external endpoints to access my database within the container using the default SQL port.

The --env parameter is required for this particular image, and we can provide additional parameters, for example sa_password.

There is one more parameter here I didn’t use for my example: -isolation=hyperv

This parameter tells Docker to run this particular container as a Hyper-V container rather than a Windows container. As you may know, Hyper-V containers provide an additional layer of kernel isolation and separation from the host.

Building SQL Server Express container

Now you can check running containers using the Docker Ps command. You can specify the -a flag to the Docker Ps command in order to retrieve both running and stopped containers.

Our first SQL Server Express container is created in seconds and is ready to accept connections.

Working with SQL Server in a container

Containers are pretty isolated runtimes and it’s always hard to interact with applications inside the container. The first thing you need to figure out is which container you are actually in. The best way to learn this is to run the Hostname command and check the name of the container.

There is also a very useful command, Docker Exec, which helps you to run additional processes inside the container. You can simply run:

docker exec –d <container name> <command>

This command creates a background task inside the container without any interaction which is especially useful for maintenance or management scenarios.

You can also use the -it parameter along with Docker Exec in order to run an interactive command inside a container. As the name implies, this command will create interactive processes inside the container with which you can interact. We often use this in Windows containers to start PowerShell or CMD processes.

In our case, we would like to see if our database server is really working as expected inside the container. The best command to test this is sqlcmd. So we can simply run Docker Exec in interactive mode and call sqlcmd.

docker exec -it <containerID> sqlcmd

Now we are now using the SQLCMD command line inside the container. Let’s try to create a couple of databases.

CREATE DATABASE <DBNAME>
GO

Creating test databases inside the container

Now we can exit from SQLCMD and use Docker Exec again to list available databases:

docker exec -it <containerID> sqlcmd -q "exec sp_databases"

Along with the default databases, you can see the custom databases that I just created.

Checking databases using SQLCMD

We now have SQL Server running inside the container and have some databases created in it. The next step would be to test port mappings and external access.

As we briefly discussed, we are using the default NAT network. You can also leverage other networking options for containers such as Transparent, L2 Bridge, or L2 Tunnel. Each of them has different use cases and requirements. NAT networking is the default option and network traffic from containers to external services is routed through WinNAT with address translation applied.

For my setup, I’m using a nested Hyper-V environment to run containers. Therefore, I disabled the firewall on my nested host VM and then tried to access the nested host IP address with the 1433 port number from another computer. The nested VM host should redirect my request to the container.

There are several ways to test SQL Server connection remotely. I have been using the method below for a long time, especially in System Center deployments, to see if I can reach a remote SQL Server using the default 1433 port. The best thing about this method is that you don’t need to install or run anything. Just create an empty .txt file on your desktop and then change the extension to .udl.

Testing Remote SQL with .UDL file

Once you open that file, you will see a Data Link Properties page. You can simply provide the IP address of the remote SQL Server and credential details. Here I see all available databases in my container, so the connection has been successful.

Verifying created databases

Another easy way to test a remote connection is to use Visual Studio's "Connect to Database" option. Just provide the IP address of the nested VM host and the SA password we specified during the container build.

Checking remote SQL Access from Visual Studio

It looks like we have everything needed to remotely access our containerized SQL Server Express instance. Having SQL Server in production is not an option for enterprises today. But until then, you can leverage the SQL Server Express container image for your dev/test environments.

Using container technology, you can spin up hundreds of SQL instances in minutes and destroy them once you have what you need. There is no need to deploy a whole VM image from scratch for every SQL Server requirement.