Docker and SQL Server Data Persistence
Docker and SQL Server Data Persistence
This article looks at SQL Server in Windows containers, the options for working with SQL Server data, the Docker commands used, and the trade-offs involved.
Join the DZone community and get the full member experience.Join For Free
Read the 2019 State of Database DevOps Report for the very latest insights
Relational database containers have emerged as a cornerstone of Docker's container-based development, with MySQL and Postgres among the most popular Docker images. This article looks at SQL Server in Windows containers, the options for working with SQL Server data, the Docker commands used, and the trade-offs involved.
SQL Server Containers
Windows containers managed with Docker commands and API will soon be available from Microsoft on Windows Server 2016 and is already available from WinDocks on Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. There are differences in the SQL Server support offered by Microsoft and WinDocks, outlined here. One shared goal is support of enterprise software development using trusted software on a dedicated VM or bare metal host for security.
Some ask "why use SQL Server containers?" SQL Server has supported multiple instances on a shared host for the past decade. The reasons include speed, VM efficiency and savings, and simplified maintenance. Containers are faster to create, can be discarded and replaced in seconds, and are well-suited for short-lived needs in development and test. Containers are lightweight, and a shared VM can support an entire team with isolated and identical instances. A container host is a single point of maintenance (containers can be refreshed in seconds from the updated host image).
Faster, more agile SQL Server development and testing, combined with hosting efficiency and simplified maintenance, and license savings, combine to drive interest in SQL Server containers. SQL Server containers are being used to reduce the number of SQL Server hosts by three to five times — with significant license savings.
SQL Server containers support three methods for working with databases, all supported in DockerFile operations on WinDocks. The ADDDB command copies a database into the container. The MOUNTDB command mounts either local or remotely located databases. Finally, the MOUNTDB command can be combined with third-party database clones to support containers with mounted cloned databases.
The following examples are generated with the WinDocks Community Edition, a free downloadable port of the Docker engine to Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. Get yours here, if you'd like to follow along.
One of the challenges in supporting SQL Server container operations is the automated Docker workflow for database operations. Using SQL Management Studio to attach databases defeats the automation that containers are designed to deliver. WinDocks includes automation for SQL Server with the DockerFile command ADDDB, which copies databases (primary, secondary, and log files) into a container. The copied databases are automatically attached when the container is started and detached when stopped.
Containers are built with a DockerFile, which includes a sequence of commands that begins with a base image. WinDocks provides the image support for these SQL Server editions:
"In-container" data behaves the same as locally attached data to a SQL Server instance. The data is owned by the container, persists in the container file system, and is affected by container downtime. Databases are accessible via SQL Server Management Studio and other standard tools.
The use of ADDDB is popular as a simple method for data persistence and is well-suited for short-lived instances used in Dev and Test. The workflow includes the creation of a custom container (a container is built with one or more databases added with the ADDDB command). The container is then "committed" to create a new SQL Server image, which is used to create isolated, identical container instances for Development or Test.
An example DockerFile used with ADDDB, with two named databases, comprised of a primary database and multiple secondary database files:
ADDDB dbname Primarydb.mdf Secondarydb.ndf Secondarydb.ndf
ADDDB dbname2 Primarydb.mdf Secondarydb.ndf
Where ADDDB copies databases into a container, MOUNTDB supports mounting local or network hosted databases. Databases are mounted and attached when the container is started, and are unmounted and detached when stopped. One mount per database is supported at a time.
One advantage of MOUNTDB is improved support for high availability, with a replacement container capable of being created and mounting a database within a minute. Unlike containers using ADDDB, mounted databases are not passed through in new SQL Server container image. To support multiple containers with DB mounts, the databases must be replicated and multiple mount points created or cloned.
An example DockerFile with MOUNTDB, using both local and network hosted databases:
MOUNTDB dbname c:\path\Primarydb.mdf
MOUNTDB dbname2 \\networkpath\dbname2.mdf \\networkpath\dbname2.ndf
MOUNTDB with Clones
WinDocks supports mount points generated independently of containers. In this respect, the WinDocks design reflects the direction of future Docker designs. https://github.com/docker/docker/issues/6496
Database clones are useful when working with larger databases and support delivery of multiple containers with mounted clones for Development or Test. WinDocks is an extensible system with support for the administrative processes involved in creating snapshots and clones.
Support of third-party snapshot and clone support is provided through the WinDocks node.cfg file. The administrator can add DockerFile commands for the snapshot and cloning steps. In the example below, we use a simple copy command to illustrate the process.
The DockerFile can define a SQL Server container that includes a mounted database clone. Note the use of the environment variable for the container.
The results realized with NetApp are compelling. Automated delivery of a SQL Server container with a mounted 750 GB cloned database in 50 seconds! Watch a two-minute video here.
Containers are rapidly becoming standard tooling for DevOps processes. DevOps process support for SQL Server is now available and on-par with the best available on Linux with MySQL or Postgres.
Opinions expressed by DZone contributors are their own.