SQL Server Containers and Clones

The WinDocks team appreciated collaborating with Pat Sinthusan of Netapp to develop the solution outlined here.

There’s a lot of buzz about containers, and Microsoft will soon release container support in Windows Server 2016. The use of containers for supporting SQL Server is new, and some understandably ask “why use containers?” The reasons are many:

• Containers are fast and lightweight, and support multiple SQL Server instances on a shared host. Users realize an average 3-5x reduction in VMs used, with containers provisioned in seconds. A dev or test team can share a single VM to host identical, isolated environments.

• SQL Server maintenance is simplified, as containers are quickly refreshed with an updated container host image.

• DBAs save time by creating custom SQL Server images that developers and testers use to create integrated environments.

• Finally, and our focus for this article, is how containers are integrated with storage systems, to use cloned databases

Interestingly, Microsoft has been quiet on plans to support SQL Server in Windows Server 2016 containers. Earlier this year Microsoft demonstrated SQL Server container support using the future Linux hosted SQL Server. We’re not surprised by this, given the limitations of Microsoft's design in Windows Server 2016. A Linux hosted demo, combined with no shared plans for SQL Server containers, combine to suggest that the SQL Server division will bypass Windows container support altogether.

To explore the use of SQL Server containers with database clones, we’ll use the Windocks port of Docker's open source project. WinDocks has released support for Windows 8.1, Windows 10, Windows Server 2012, and Windows Server 2016, and supports all editions of SQL Server 2008 through SQL Server 2016. We’ll use the WinDocks Community Edition, a free downloadable edition here.

Windows Containers for Development and Test

SQL Server containers are SQL instances with added process and user isolation. The workflow begins with a base SQL Server image (SQL Server 2008, 2012, etc.), and databases are added into the container or mounted. Once created, a container can be saved as an image, which is shared to the dev and test team. Each user can create and delete isolated instances without impacting others, and development and test cycles become more efficient.

The ability to deliver isolated, identical SQL Server instances, for development and test is the most popular use of SQL Server containers. Most organizations work with databases local to (in) the container. A single VM typically supports a dev or test team quite nicely with twenty or more SQL Server containers. Creating a custom image takes only seconds, and developers and testers can provision identical isolated instances on-demand. This is particularly useful for destructive testing.

As the data footprint grows, however, the time and resources involved in replicating the data “in container” becomes less practical. The alternative is to work with mounted databases, and mounted database clones (some clones are writable, and support destructive testing).

Privileged Process Support for Containers

Each container has an isolated file system, and controlled use of system resources. A badly behaved application cannot impact neighboring containers. SQL Server container users can be authenticated via Windows or with SQL sa authentication, which is popular given the short-lived nature of SQL Server containers in dev and test (typically measured in hours).

To work with third party storage systems and database clones, the container host needs to support privileged commands. WinDocks provides Administrators the ability to define privileged commands through a host configuration file. The image below shows the WinDocks node.cfg file with support for NetApp’s SnapManager for Windows (SDCLI.exe). The Administrator defines a DockerFile command, in this case RUN_AS_ADMIN_CLONEDB, which refers to NetApp’s SDCLI.exe.

Windows Containers with Database Clone Workflow

WinDocks collaborated with NetApp to explore how containers could integrate with NetApp storage to provide efficient delivery of SQL Server database clones. We used NetApp’s SnapDrive for Windows (SDCLI.exe) to create mount points to database clones, which are mounted to the SQL Server containers.

The workflow begins with a Snapshot of the target database, and appropriate permissions between the container host and the Storage LUN. A DockerFile is used to combine the operations involved between the creation of the SQL Server container, and the corresponding database clone (shown below). DockerFiles are a series of commands executed in sequence. The process begins by building a container using SQL Server 2014 and proceeds to clone the source database and create a mount point for target container. Then, the container is provided with directions for mounting the cloned database. Note the use of the environment variable for the ContainerID.

Eye Popping Results!

Working with both the Docker commands, or through a PowerShell script, the container is instantiated with a mounted cloned ~1 TB database in less than a minute.

When using writable clones, changes to the database can be retained by creating a new snapshot, and the process can be repeated.

A two minute video of the demo is here.

Conclusions:

Integrating storage system operations with containers opens up new possibilities, for fast provisioning of clones within an enterprise, or support for a Hybid public cloud + private data. SQL Server containers provide a new approach for supporting development and test, and can provide integral support for third party storage systems. The most compelling aspect is how Docker tooling can enable dev and test teams to self-service their needs for SQL Server environments. To explore SQL Server containers, consider downloading your own Windocks Community Edition here