There has been a lot of buzz about containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source to Windows in March of 2016. Full disclosure, I am a co-founder at WinDocks.
In this article we’ll take a look at containers, the reasons for their growing popularity, and use by SQL Server DBAs and developers.
Containers and the Docker’s Container Standard
Containers provide process and user isolation for application multi-tenancy. Each container includes all software dependencies and becomes an immutable application package that will run on a laptop, or the public cloud without modification. Once created containers aren’t modified, but are designed to be discarded and replaced (more on how SQL Server developers work with containers below).
There have been a number of container designs for Windows over the years, including Spoon, Virtuozzo, Turbo.net, and others. Microsoft’s release of Windows 2016 and container support for the Pro and Enterprise editions of Windows 10 creates a defacto standard for server side Windows application delivery. Microsoft has also joined the Linux Foundation, and participates in the formal standardization of containers.
Even though the Docker API now supports both Windows and Linux, containers are not OS independent. A SQL Server container using SQL Server 2014 Enterprise requires a Windows host, and works with existing SQL Server tools such as SQL Server Management Studio. Over the past three years early adopters have proven use of containers for speed and economy. Multiple application containers are supported on a shared host, rather than a single application per VM, resulting in VM consolidation.
Containers for SQL Server Development and QA
At this point astute readers will ask “why use containers?” After all, SQL Server supports multiple named instances, and IIS supports similar capabilities. The saving in OS overhead shown above is illusory if we already enjoy effective multi instance support for .NET and SQL Server.
Containers are growing in popularity due to their speed, simplicity, and automation, particularly for short-lived instances needed for Dev and QA. While SQL Server supports multiple named instances, most organizations use VMs to support Development and QA (as illustrated above). Early adopters of SQL Server containers automate delivery of production data environments for Dev and QA, and average a 5x reduction in the number of SQL Server hosts used. This translates to significant savings on VM maintenance, and Microsoft license cost savings.
Each member of a Development team uses an isolated SQL Server container on a shared server. A mounted 1 TB database is delivered in less than one minute, and can be deleted and replaced as needed. Updates to the data or code can be incorporated into a new container image, and environments can be refreshed in minutes. The process is a dramatic improvement over servers with dedicated named instances, or provisioning VMs for each developer.
An Introduction to Using SQL Server Containers
Containers are defined by Dockerfiles, which define a sequence of steps to build a container. Dockerfiles begin with a “base image,” followed by databases and SQL Server scripts, and can include use of snapshots and database clones.
In the example below the Dockerfile specifies SQL Server 2012 standard, followed by several databases being copied into the container. Finally, a SQL Server script is run to mask selected tables. In this example the database files are located in the same directory as the Dockerfile, so no relative path is needed.
Containers can include scores of databases, including secondary and log files when needed. Databases can be copied and run within the container file system as shown above, or mounted to the container using the MOUNTDB command.
Containers with databases “in container” can be saved as a custom image, to support creation of identical containers. Containers up to 500 GB can be instantiated in two minutes or less depending on IO performance. Containers with mounted databases require a slightly different approach. SQL Server databases can only be mounted to one instance at a time, so each container is built with a snapshot or clone for mounting. A 1 TB mounted database container can be instantiated in roughly one minute using this process.
Each container includes a private file system, and users are limited to their container and file system. The example below shows a standard Docker client building a container with MSSQL-2014 and venture.mdf. This example includes SQL sa credentials, which is optional. A unique ContainerID and container port are generated. Once started the container is available to SQL Management Studio and all other standard tools.
New web based interfaces will soon be available to further simplify use by Developers and QA.
Conclusions and Additional Uses:
Containers enable a shared VM to support up to 20 simultaneous running SQL Server environments, each provisioned in seconds. Front-end and back-end teams can replicate bugs quickly and validate tests by simply choosing the appropriate branch in the build script.
One WinDocks customer uses a host with eight cores and 96 GB of RAM to support up to twenty SQL Server containers for dev and test. Each container includes over twenty five databases running in the container, with an aggregate size of 400 GB. Prior to using containers, each VM took over an hour to provision. Using WinDocks, the provisioning time is down to a couple of minutes. The result is a 20x reduction in VMs, a 5:1 reduction in CPU cores, and dramatic financial savings in Microsoft license costs.
While support for Development and QA is the most popular, other uses include DR Testing, and support of legacy back-ends for SAP and MS Dynamics. Some clients are using containers with Mesos to consolidate VM workloads. A Fortune 500 firm plans to consolidate a mix of Linux and Windows VMs onto containers, with a goal to reduce the number of VMs used by 8:1 (a monthly savings of over $200,000)!
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).
