Introduction to SQL Server Containers

In the Fall of 2014, Microsoft announced plans to add Docker container support to Windows Server 2016. The announcement added emphasis to Microsoft’s growing focus on Linux and open source tooling. In the months that followed, .NET core was open sourced, Microsoft and Red Hat formed an alliance, and plans to port SQL Server to Linux were announced. Containers are just around the corner for the Windows community, so let’s take a closer look at the use of SQL Server containers.

Containers and Virtual Machines

Containers have been around for decades (remember BSD Jails in circa 2000), but have surged into the mainstream with adoption of Docker Inc’s open source project.  Docker’s design includes an elegant method of application packaging and a server-side API for container management. The crux of the design is a method of application packaging that solves the “dependency hell” problem by incorporating software dependencies into a container image. Using Docker containers, a developer can be confident an application developed on a laptop will run on a shared test server, or in the public cloud.

Just as the Win32 API dominated the landscape for desktop applications, Docker is rapidly becoming the defacto industry standard API for container packaging. Microsoft is implementing  a subset of the  Docker API for Windows Server 2016, is involved in the standardization through the Open Container Initiative.  Docker users benefit from industry-wide support with hosted services on AWS, Azure, and other clouds, and third party tools for monitoring, logging, cluster orchestration, and other services.

Containers are fast and lightweight and deliver improved resource utilization.  Containers take seconds to launch, and typically present a small incremental workload for the host. It’s common to run scores of containers on a shared development or test server. VMs, on the other hand, can take minutes to launch, and often have low (10-15%) resource utilization. The popularity of containers for development and test is due to greater speed and agility in creating environments, as well as a significant reduction in the use (and maintenance) of VMs.  

Containers, Images, and Repos

Containers provide user and process isolation, to protect applications from poorly behaved neighbors. Both .NET and SQL Server containers also deliver effective name space isolation. There is a strong industry focus on enhancing container security, and the major cloud vendors (Azure and AWS) wrap container services in VMs to ensure secure use. Containers also include methods for managing resource usage, such as limiting CPU, disk, and network use.

Containers are built using a Dockerfile, which is a text file with a series of commands that are executed to build a container. Dockerfiles begin with a base image, and add code or data to the container. Containers can be saved and re-used as an “image.”   Images are stored in a shared registry or repo. 

Relational DB Containers and SQL Server

MySQL and Postgres are among the most popular container images on the Docker Hub. Database containers are used to deliver identical, isolated, sandboxed environments for development and test. Data is included in the container or can be mounted. SQL Server containers are SQL Server instances based on Microsoft’s shared DLL architecture that has supported multiple SQL Server instances on a host for the past decade. Containers provide added process and user isolation, SQL Server configuration, and are accessible via SQL Management Studio and other tools. Once built, containers can be saved as Images, which are used to support teams with identical instances on a shared host. What makes containers uniquely helpful include:

• Speed:  SQL Server containers are provisioned in seconds and provide effective support for developers and testers, who often need to discard an instance and have it quickly replaced
• Efficiency:  SQL Server containers run efficiently on a shared host, and support the consolidation of multiple VMs onto a single shared VM for dev and test teams, with corresponding license and operational savings
• CI/CD support:   SQL Server containers provide automated support for adding and mounting databases, running configuration scripts at build time, and the support needed for automated build for Continuous Integration and Delivery processes
• HA/DR support:  SQL Server containers support integration with third party systems for DB replication, snapshots, and mirroring, and significant work is being applied to prove their utility for a range of operational purposes.  More on this below.

Microsoft is poised to fulfill Docker support with Windows Server in the second half of 2016. Microsoft’s design features an option to support containers on either the Windows Server 2016 "core" or a headless server called Nano. Interestingly, Microsoft has been quiet regarding plans for SQL Server container support on Windows Server 2016, and earlier this spring chose to demo the first SQL Server container running on the future Linux port of SQL Server! This suggests that the SQL Server division may bypass Windows container support in favor of a future Linux host. This is understandable, given Microsoft’s choice to implement a Linux style of container or application configuration.  

Microsoft’s Windows Server 2016 containers are designed to support a shell based container for .NET, Windows console apps and services. But, SQL Server and other Windows applications aren’t designed to be managed through a shell.  SQL Server containers on Windows Server 2016 will require the use of SQL Management Studio, or other tools, to run scripts, or add or mount databases. Simply put, a SQL Server container that requires such manual steps defeats the speed and automation offered by containers. 

Fortunately, developers interested in SQL Server containers based on a port of Docker’s open source aren’t limited to Microsoft’s implementation. WinDocks is a startup comprised of a group of former Microsoft engineers, who have released a port of Docker’s open source project for Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. WinDocks supports all editions of SQL Server 2008 (and r2), 2012, and 2014, and SQL Server 2016. WinDocks adds container support to Windows, enabling the use of SQL Server licenses with containers. A free WinDocks Community Edition is also available, and is the basis for this article. 

Data Persistence and SQL Server Containers

Working with SQL Server containers is no different from SQL Server instances, so the options include locally attached data or mounted databases. WinDocks SQL Server containers support the use of the ADDDB command to copy a database into the container, while the MOUNTDB command supports either local or network located databases.

The ADDDB copies databases (Primary, Secondary, and Log files) into a container, and are attached when the container is started and detached when stopped. SQL Server containers provide name space isolation, so identical instances can be run in containers on the same host without name or user conflicts.  

Once created, a container with added databases can be saved as a new image, and then developers and testers can easily generate instances on demand. The containers can be instantiated in seconds, with the time determined by the size and number of databases. This is a popular approach for support of development and test, and some users add 30 or more databases into a container with good performance.   

 "In container" data behaves the same as locally attached data to a SQL Server instance. The data is owned by the container, persists in the container file system, and is affected by container downtime. Databases are accessible via SQL Server Management Studio, and other standard tools.  

An example DockerFile using ADDDB, with two named databases, comprised of a primary database and multiple secondary database files:

FROM MSSQL-2016
ADDDB dbname Primarydb.mdf Secondarydb.ndf Secondarydb.ndf 
ADDDB dbname2 Primarydb.mdf Secondarydb.ndf

Where ADDDB copies databases into a container, MOUNTDB supports mounting local or network hosted databases. WinDocks supports mount points generated independently of containers. In this respect, WinDocks reflects the direction of future Docker designs.  See Collected issues with Volumes.

Using MOUNTDB databases are mounted and attached when the container is started, and unmounted and detached when stopped. Only one mount per database is supported at a time. Unlike containers using ADDDB, mounted databases are not passed through in SQL Server container images. To support multiple containers with mounts, the databases must be replicated and multiple mount points created, or cloned. 

An example Dockerfile with MOUNTDB, using both local and network hosted databases:

FROM MSSQL-2016
MOUNTDB dbname c:\path\Primarydb.mdf 
MOUNTDB dbname2 \\networkpath\dbname2.mdf \\networkpath\dbname2.ndf

Building a SQL Server Container

The following example illustrates the process of building SQL Server containers using the ADDDB command.

Step1:   open a new command prompt window
Step 2:  enter  >docker build c:\Windocks\samples\Test1 The WinDocks client return string includes the container ID, container port, and SQL Server sa password.    

Step 3:  >docker start <containerID> A subset of the container ID can be used.

The Docker commands and return output is shown below. Each container is a fully isolated SQL Server instance, with name space isolation, and is accessible via SQL Server Management Studio. In this example, the Adventureworks database is copied into the container, and attached when the container is started.

The database schema and design can be updated, the container stopped, and a new SQL Server image created. The new SQL Server image can then be used by an entire team, on a shared WinDocks host.

>docker stop <containerid>
>docker commit <containerid>  <imagename>
>docker run –d <imagename>

This workflow illustrates the popularity of containers for rapid sharing of SQL Server instances in identical, isolated containers. A SQL Server change script can be exported to update the source database as needed.

Containers and Database Clones

Database clones are useful when working with larger databases, and supports delivery of multiple containers with mounted clones for development or test (some clones are also writable). WinDocks is an extensible system, with support for the administrative processes involved in creating snapshots and clones.  

Support for adding snapshot and clone support is provided by the Administrator, who chooses to enable “privileged” commands in the WinDocks node.cfg file. In the example below we use a simple “copy” command to illustrate the process.

The Dockerfile can define a SQL Server container that includes a mounted database clone. Note the use of the environment variable for the container.

Earlier this year WinDocks collaborated with NetApp to explore the use of containers integrated with the NetApp SnapClone process (SDCLI.exe). The results were compelling with one-step delivery of SQL Server containers with a mounted 750 GB cloned database in 50 seconds!  Watch a 2 minute video.

SQL Server Containers for Legacy Support

Interest in Windows containers is growing rapidly, and a survey of WinDocks Community Edition downloaders has revealed a surprising range of uses. The most popular use is for development and test, but support for legacy environments such as SAP and Microsoft Dynamics have also proven popular. In these cases, the SQL Server back-end is being containerized, and the support environment is simplified. 

Integrating SQL Server and .NET Containers and existing infrastructure

Fortunately, containers provide excellent support for .NET applications, and integrating environments that combine SQL Server and .NET containers is straightforward. The ASP.NET architecture defines the use of an XML file, web.config, to define the configuration and connection strings between the .NET application and the SQL Server container.  

For this example, we’ll use the sample .NET app included in the WinDocks install, located in the \windocks\samples\testdotnet folder. Open the web.config using Notepad, and note the “connection string” section, and references to the host address, port, and SQL credentials.

(Click on the image to enlarge it)

Edit the Host address if needed, the Port, and SQL Server sa password with the details of a running SQL Server container. Save and close the file (be sure not to save the file as a .txt file). Run:

>docker Build c:\windocks\samples\Testdotnet
>docker start <container id>

Open a Web browser, and navigate to the host address and port to view the integrated application.

An alternative approach is available by integrating the containers with DNS named services, as outlined in an earlier article. This approach enables containers to be dynamically assigned to known IP addresses when they are built, using the Windows NETSH utility.

Conclusions

SQL Server containers take advantage Microsoft’s proven shared DLL architecture to provide practical support for SQL Server instances on a shared host.  The most popular use is for development and test, but some are testing Legacy application support (primarily SAP and Microsoft Dynamics), with a containerized SQL Server back-ends.   The reduction in VM count varies, but users report an average reduction of 3-5x, with significant license cost and host maintenance savings. Host maintenance is simplified as updates are passed downstream when containers are refreshed.  

The examples discussed here have focused on the database engine, and further work is needed to explore the use of containers for support of SSIS and other SQL Server services.  

Other promising uses being explored include migration of workloads, High Availability, and Disaster Recovery. The speed and portability of containers should make these more affordable, practical, and more easily tested. Additional time and testing will also help answer whether SQL Server containers are production ready. 

The lack of a stated plan from Microsoft suggests it is unlikely to ship practical support for SQL Server containers on Windows Server 2016. If not for Windows Server 2016, their future Linux port of SQL Server should be ideal for container support, and we look forward to that in 2017. 

The time to begin exploring the use of SQL Server containers is now. To download the WinDocks Community Edition that was the basis for this article click here. The download for Microsoft’s Windows Server 2016 preview.

About the Author

Paul Stanton, Co-founder WinDocks, Ex-Microsoft, has been involved in Cloud Foundry, OpenShift, and cross platform support for Windows in an increasingly Linux focused world.    He is currently focused on bringing the best port of Docker’s open source to Windows and SQL Server developers, and Operations.