An Introduction to Docker and Containers for SQL Server Developers and DBAs

In March 2013 Soloman Hykes announced what would become known as the Docker open source project.    In the months that followed there was a ground swell of adoption by the Linux community, and in the fall of 2014 Microsoft announced plans to implement containers in Windows Server 2016.    WinDocks, where I am a co-founder, released an independent port of Docker’s open source to Windows in early 2016, with a focus on first-class container support to SQL Server.  

Containers are rapidly become a strategic focus across the industry.   In this article we’ll look at containers and their use by SQL Server DBAs and developers.  

Understanding Containers

Containers define a new method of application packaging combined with user and process isolation, for application multi-tenancy.    Varied Linux and Windows container implementations have existed for years, but Microsoft’s release of Windows Server 2016 established Docker’s design as the defacto container standard.   The Docker API and container format is now supported on AWS, Azure, Google Cloud, every Linux distro, and Windows.   Docker’s design is elegant and delivers compelling benefits: 

Portability: containers include application software dependencies and run unmodified on a developer’s laptop, a shared test server, or any public cloud. 

Why SQL Server Containers? 
SQL Server has supported multitenancy with named instances for the past decade, so how are SQL Server containers valuable?    
The answer is that SQL Server containers are more practical by their speed and automation.  SQL Server containers are named instances, complete with data and configuration, delivered in seconds.   The ability to create, discard, and replace SQL Server containers in seconds creates more practical use for Dev and QA, and other uses as we’ll discuss below.  
The speed and automation of SQL Server containers make them ideal for delivery of production data environments for Dev and QA.   Each member of a team works with isolated containers on a shared VM, with a 3-5x reduction in VMs used.  This translates to significant savings on VM maintenance, and Microsoft license cost savings.   Containers are also easily integrated with Storage Area Network (SAN) arrays, using storage replicas and database clones.  

A mounted 1 TB database is delivered in a container instance in less than one minute.   The process is a dramatic improvement over servers with dedicated named instances, or provisioning VMs for each developer.  One organization uses an 8 core server to support up to twenty 400 GB SQL Server containers.     Previously each VM took over an hour to provision, but container instances are delivered in 2 minutes.  The result is a 20x reduction in VMs, a 5:1 reduction in CPU cores, and dramatic financial savings in Microsoft license costs, and greater flexibility and responsiveness to the business.  

SQL Server Containers in Use:

Containers are defined by Dockerfiles, which define the steps to build a container.   The Dockerfile below specifies SQL Server 2012, followed by databases that are copied into the container, and a SQL Server script to mask selected tables.  

Each container can include dozens of databases with secondary and log files.    Databases can be copied and run in the container, or mounted using the MOUNTDB command.   

Each container includes a private file system, which is isolated from the host resources.   In the example below, a container is built with MSSQL-2014 and a venture.mdf.   A unique ContainerID and container port are generated. 

While SQL Server containers provide a new level of speed and automation, they behave just like ordinary named instances.   Resource governance can be implemented with SQL Server tooling, or via the container resource limits.  

Beyond Dev and QA Support

While containers are most popular for delivery of environments for Development and QA, other uses are emerging.  DR system testing is a simple but powerful use-case.   Others are containerizing back-end SQL Server environments for legacy apps, such as SAP or Microsoft Dynamics.    A containerized back-end is used to provide working environments for support, and for ongoing maintenance an update teams.  Others are evaluating containers for support of production environments, with persistent data storage.   We’ll cover strategies for persistent data in a forthcoming article.  

At WinDocks we’re working to further simplify the use of containers with a web interface.    Another project focuses on bringing SQL Server containers into a Continuous Integration or DevOps process, with Jenkins or Team City based CI/CD pipelines.   

Watch for additional articles on the use of SQL Server containers.  In the meantime, you can explore the use of containers on all editions of Windows 8 and Windows 10, Windows Server 2012, or Windows Server 2016, with support for all editions of SQL Server 2008 onward with your own copy of the WinDocks Community Edition. (https://www.windocks.com/community-docker-windows)