Containers at work: .NET and SQL Server containers for Dev and QA

Windocks 1.0 was released in Q1 of 2016, as the first port of Docker’s open source to Windows. Some were skeptical of Windocks, as Microsoft planned an implementation for Windows Server 2016. We’re glad to have persevered, as Windocks now delivers a more complete and practical solution for .NET and SQL Server developers. 

WinDocks is more complete with support of the full Windows OS family, including Windows 8 and Windows Server 2012, in addition to Windows 10 and Windows Server 2016. Importantly, Windocks also supports all editions of SQL Server 2008 onward and .NET, while Microsoft’s containers are limited to .NET and and an evaluation version of SQL Server vNext. Full disclosure, I am a principal at WinDocks. This article looks at three different customer case studies of SQL Server containers for Development and QA support. 

SQL Server Containers for developer productivity

Datical recently released results of a survey of Database Administrators. Over 90 percent reported that challenges in delivering database environments limit software development. Other surveys report that most organizations update SQL Server dev and QA environments twice monthly or less.

SQL Server containers are growing in popularity as a tool for automated delivery of data environments for Development and Test. SQL Server containers are SQL Server instances, provisioned with data, in seconds. SQL Server containers deliver short-lived, frequently updated instances that Dev and QA teams need. 

Case 1: On-premise, in-container data for .NET + SQL Server environments

A leading mobile phone Top-up provider, works with a SQL Server 2012 production database environment comprised of 37 databases ranging up to 500 GB in size, and an aggregate footprint of well over 1 TB. A daily back up is used to create a baseline with customer data cleansed, and size reduced, which is copied to the corporate network for support Dev and QA teams. VMs are built using chocolatey to set up SQL Server, and Octopus deploy is used to restore the baselines, configure user accounts, and integrate with a .NET application. 

The process of building the VMs takes over 30 minutes, and is subject to errors that often require a new VM build. The Octopus deploy process takes an additional 30-40 minutes to complete the database baseline restore and configure the VMs for use. 

SQL Server containers were evaluated with a new workflow. Once the baseline backup is available the databases are restored, stopped, detached, and the database files copied to a Windocks container host. A container is created and saved as an image to support creation of identical, isolated containers on the container host. 

The new process has proven more reliable, and delivers fully configured containers in 2 minutes or less. Where previously scores of VMs were used now up to 25 SQL Server containers are provisioned on a single 8 core bare metal machine. The QA team prefers the container based tooling, as it has proven to be more reliable and is far faster and responsive than the VMs used previously. 

Case 2: AWS hosted mounted production data for .NET + SQL Server development

A globally distributed development team worked with a .NET and SQL Server 2014 production environment hosted on AWS. The production database is >200 GB, and is replicated to a fail over instance on AWS using NetApp ONTAP. The database replica was used to create database baselines, with data masking, and reduced size, to provide downloadable files for the development teams. The .NET application was maintained in a private Git repo. In total, each team would spend hours each week updating their respective VM based local development environments. 

To evaluate the use of containers the team implemented a single VM to host the full development environment in containers. The DB replication is interrupted to create a database snapshot. A Powershell script generates database clones, which are mounted to SQL Server containers. The Powershell script also defines consistent connection strings and credentials for each developer. Once mounted, but before being provided to the developers SQL Server scripts are run to perform data masking. 

The project was successful, and now a new developer can be on-boarded immediately, without hours spent in replicating a complex environment. Where up to 16 VMs were used previously the team works with a single VM, and developers benefit from a full production data set. Developers save hours each week previously spent maintaining local environments. Even developers located in Romania reported the AWS East environment is more responsive than running the environment locally. 

Case 3: EqualLogic Clones for a 1 TB SQL Server environment

An automobile data service provider works with a large (~1 TB) SQL Server 2014 database. The production environment is cloned daily and available on an EqualLogic PS series SAN array. In the EqualLogic PS series, a cloned volume is a complete copy of the production environment and is recommended for dev and QA use. 
The DBA installed WinDocks on an 32 core VM, and used a PowerShell to script the creation of containers, with snapshots of the data volume mounted to the SQL Server containers over a 10 GbE iSCSI network. Data masking is also implemented on startup via a SQL Server script. 

Previously creating VMs for development took over an hour, now SQL Server containers are instantiated in less than 1 minute, and can be deleted and replaced as needed. Developers benefit from access daily to the full production data environment, with substantial savings in VM maintenance and server side license costs. 

SQL Server containers for production?

Containers are well suited for short-lived instances needed for Dev and QA, and where data persistence is not required. Some argue that containers are only suitable for state-less workloads, and are not suited for production use. 

Windocks SQL Server containers, however, are simply SQL Server instances that are instantiated and managed with Docker commands. In short, SQL Server containers are as production worthy as any manually created instance. Like any instance, SQL Server containers support VSS based back up, and support mounted data volumes both on the local host or network attached storage. While it is not standard practice to update container based applications, there are no barriers to working with SQL Server containers in the same way as SQL Server hosts are patched and maintained. 

Observations:

SQL Server containers can run on a Windows 8 laptop, or a shared server, or on any public cloud, and support data in the containers private file system (Case #1 above), or with mounted databases and clones (Case #2 and 3). In every case, containers are delivered complete with data in seconds, and can be replaced just as quickly. Early adopters realize a streamlined delivery of SQL Server and multi-tier environments, with greater speed, and dramatically reduced VM usage. 

Docker and containers is new to Windows and SQL Server developers and DBAs, but is clearly aligned with Microsoft’s and industry-wide direction. Now is the time to start exploring the use of SQL Server and .NET containers. Windocks provides a no cost downloadable Community Edition, and you can download yours at https://www.windocks.com/community-docker-windows