Automate Delivery of SQL Server Production Data to Development Teams - Simplify VMs, Licensing, and Support to a Single Machine

WinDocks is a port of Docker’s open source to Windows, providing .NET, SQL Server, and Java in containerized environments for development, test, and QA. Based on the performance and customer feedback, we expect Windows containers to follow a similar pattern of adoption as on Linux, and become preferred infrastructure for delivery of environments for Dev, Test, and QA.

SQL Server containers are a big step forward in terms of efficiency. Where most organizations are testing SQL Server environments twice monthly, the process outlined here makes it practical to provide daily (or even more frequent) environments.

WinDocks is available as a free Community Edition, and supports Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. WinDocks supports .NET, and all editions of SQL Server 2008 onward, and Java with Tomcat or Jetty, Nginx, and Node.js.

Containers for Daily Production Data Environments

WinDocks installs on a bare metal or a VM host, and even a Windows 8 laptop can provide reasonable service for a team. Once added, the server supports a group of developers with isolated container environments. Where up to 20 VMs were used previously to support a team, with containers a single machine serves the purpose, with each developer using isolated container environments. Containerized environments are delivered in seconds, and the dramatic reduction in VMs yields an immediate and significant savings on Microsoft Server license costs.

A typical user works with a two-tier application, using .NET with SQL Server. The database used could be recovered from a backup, or a daily replica. In the simplest case the databases are copied into a container, which is used to create an image, and the image then supports the team with identical containers. Alternativey, the databases can be cloned, and then mounted to new containers. Using mounted database clones saves time in copying the data into each container, and provides a writable copy of the data and saves on storage costs.

Containers are defined by Dockerfiles, which specify the base image, container names, port assignments, paths to databases, Git repos for pulling code, and SQL Server scripts. The Dockerfile also includes storage commands for generating the database snapshot and clones, using NetApp, Equallogic, or similar systems. Incorporating storage system support into Dockerfiles allows a DBA or DevOps engineer to support a dev or test team with self-service environments. Finally, a PowerShell script automates the creation of the complete environment.

A step-by-step view of the process includes:

• Snapshots of the production databases are made, each being a writable copy. In this example, the snapshots are cloned and mounted to each SQL Server container. Smaller databases can also be copied and run in the SQL Server container.
• A PowerShell script runs the >Docker build on the SQL Server Dockerfile.
• The SQL Server container is started and build-time scripts are run (for data masking, and other needs).
• The PowerShell script parses the SQL Server container port and credentials to be used in the associated .NET web.config file.
• Powershell runs the Docker build of the .NET Dockerfile. The Dockerfile includes a Git pull from the GitHub repos. The web.config file is updated with the SQL Server container port and credentials and saved. The Dockerfile can also specify container ports and naming.
• The .NET container is started. The integrated environment includes a .NET application based on the latest code and a paired SQL Server container using the most recent DB snapshots.

A shared VM supports up to 20 simultaneous running environments, each provisioned in just 100 seconds. The front-end and back-end teams can replicate bugs quickly and validate tests by simply choosing the appropriate branch in the build script. Bugs are resolved quickly and easily.

One WinDocks customer uses a bare metal 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 (not mounted), with an average size of 100 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 MS license costs.

The WinDocks team is building quite a bit of experience in working with a range of storage systems, including NetApp, Equallogic, and others. We can generally provide the dockerfiles and steps involved to setup this process in a single call. If you’re interested but uncertain as to your ability to setup such an environment, we also offer a $500 guaranteed QuickStart service designed to provide a turn-key environment.

Conclusions

Containers are a dramatic step forward for support of developers and test, and the ability to support SQL Server in containers should be a boon for Windows based development. It’s reasonable to expect that SQL Server environments can be provided daily, with the latest production data, using a simple automated process. Developers should be happy with daily, on-demand access to fresh isolated environments. Management should be happy with added productivity, and cutting the Microsoft license bill in half!

Explore this for yourself, by downloading your own copy of the WinDocks Community Edition.