Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server Containers and Clones

DZone's Guide to

SQL Server Containers and Clones

See how containers can be integrated with storage systems for fast database mounting.

· Integration Zone
Free Resource

The Integration Zone is brought to you in partnership with Cloud Elements. What's below the surface of an API integration? Download The Definitive Guide to API Integrations to start building an API strategy.

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

There's a lot of buzz about Docker containers, and Microsoft will soon release Docker 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. 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 Docker-compatible containers from WinDocks. WinDocks has released a Docker engine implementation that supports Windows 8.1, Windows 10, Windows Server 2012, and Windows Server2016, 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, created and managed using Docker commands, but accessible to normal SQL Server tools. The Docker 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 teams. 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.

Image title


Windows Contaniers 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.

Image title


Image title


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 hybrid 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. 

Your API is not enough. Learn why (and how) leading SaaS providers are turning their products into platforms with API integration in the ebook, Build Platforms, Not Products from Cloud Elements.

Topics:
sql server ,containers

Published at DZone with permission of Paul Stanton, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}