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

Docker and SQL Server Data Persistence

DZone's Guide to

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.

· Database Zone
Free Resource

Learn how our document data model can map directly to how you program your app, and native database features like secondary indexes, geospatial and text search give you full access to your data. Brought to you in partnership with MongoDB.

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.

ADDDB

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:

  • MSSQL-2008
  • MSSQL-2008r2
  • MSSQL-2012
  • MSSQL-2014
  • MSSQL-2016

"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:

FROM MSSQL-2016
ADDDB dbname Primarydb.mdf Secondarydb.ndf Secondarydb.ndf
ADDDB dbname2 Primarydb.mdf Secondarydb.ndf

MOUNTDB

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:

FROM MSSQL-2016
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.

Image title


The DockerFile can define a SQL Server container that includes a mounted database clone. Note the use of the environment variable for the container.

Image title


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.

Conclusions

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.

Discover when your data grows or your application performance demands increase, MongoDB Atlas allows you to scale out your deployment with an automated sharding process that ensures zero application downtime. Brought to you in partnership with MongoDB.

Topics:
devops best practices

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}