Concepts

Three Ways to Modernize with Docker SQL Containers and Instances

In this post we take a look at how database cloning is applied in environments composed of a mix of SQL Server instances and containers. We'll outline three approaches that address all combinations of SQL Server environments.


Docker SQL containers are featured by Microsoft in SQL Server 2017 demos, and many view containers as the future for application driven infrastructure. Containers are growing in popularity and supplanting use of Virtual Machines (to a degree) with increased app density and compute utilization. Containers are a growing form of server-less computing, with container services on AWS, Azure, and Google.

Windocks has focused on SQL Server containers for several years, and our conviction on the value of containers is stronger than ever. Windocks clients are realizing great results, using a combination of SQL Server containers and instances with database cloning.

This article summarizes three approaches to modernize delivery of production SQL Server data. Common uses include dev/test, QA, and reporting and BI, with instances updated with production data, securely, in less than a minute. Where containers are used, organizations realize added benefits of simplified operations and a reduction in VMs used. Each of the solutions support use wherever Windows servers are supported, including the major public clouds and any on premise infrastructure.

Begin with database cloning and Storage Array snapshots

Modernizing SQL Server workflows begin with fast, secure delivery of production data. Most organizations today rely on users or DBAs to restore backups for data access. Updates are infrequent and slow, and loaded with security concerns, and consumes storage and network resources.

Snapshots and database cloning provide secure delivery of data environments that support full read/write operation that can be updated daily, and minimizes storage. When Storage Arrays are available they are preferred, and can provide access to up-to-date production data. Windocks simplifies what has been a complex environment, with an integrated design that manages the creation of snapshots and their associated mount points and mounts the databases to target SQL Server environments.

Windocks also supports use of SQL Server backups to build clonable images, which support delivery of clones. A Windows based database clone can deliver a read/write supporting Terabyte class data environment in under a minute, while using less than 40 MB of storage. These clones are functionally equivalent to snapshots delivered from a Storage Array. The solution is based on Windows Virtual Hard Drives (VHD), which supports Hyper-V, and is robust and industry-tested.

modernization options image 1 650 x 282

Whether using a Storage Array or SQL Server backups, database clones are the obvious choice for efficient, secure, data delivery. Complete secure environments are available in less than a minute, and reflect enterprise security policies on delivery. SQL Server scripts are applied in the delivery process to add user/group permissions, data masking, and encryption. The result is a dramatically improved user access, with improved data security.

Three ways to modernize data delivery for SQL Server

The approaches available for delivery of SQL Server clones and snapshots vary based on the Windows and SQL Server versions involved. There are three options and each can be deployed on premise or on the public cloud, and can also be combined.

In each case, a Windocks server authenticates users, orchestrates creation of snapshots, and mounts the databases to the target. Windocks allows both automated delivery and user-driven access through a simple web UI, and simplifies the integration and use of SAN based storage. The three options include:

Instances: database clones are mounted to SQL Server instances on the LAN. This option is available for any combination of Windows and SQL Server 2008 onward.

Windocks SQL containers: database clones are mounted to Windocks hosted SQL Server containers, running on a shared Windows Server 2012 or 2016, for all versions and editions of SQL Server.

Microsoft SQL containers: clones are mounted to a shared Windows Server 2016, with SQL Server 2016 developer, or SQL Server 2017. A Linux host running SQL Server 2017 is also supported.

Modernization Options 650 x 500

Data security is enhanced as ad hoc use of backups is curtailed, and DBA approved images include enterprise data security policies. Organizations planning for Continuous Integration favor containers, for greater speed, and automation. Containers combined with clones and snapshots are ideal for automated testing, as they support destructive testing and database writes, and are quickly replaced with an immutable source. The output of the CI process with containers can include immutable Docker images as artifacts, further enhancing the CI process.

Containers for advanced SQL Server configurations

While containers are transformative, they aren’t a good fit for every project today. SQL Server containers don’t support all SQL Server configurations, including replication, Azure stretch databases, Machine Learning and other capabilities.

In time containers will support the full range of SQL Server configurations. Windocks continues to expand support for SQL Server containers, and will soon support Report Server and Analysis Server. Microsoft is also expanding SQL container support, particularly for SQL Server 2017 on Linux.

Windocks SQL Server containers supports most database options, including SMB, NFS, and SAN integration, database mail, TDE encryption, Extensible Key Managers (EKM), CLR Assemblies, DNS and server naming, secure (encrypted) secrets store for credentials, and many more instance options.

Conclusions and next steps:

Database cloning combined with SQL Server containers is the ideal combination for modernizing enterprise data management and delivery. While containers don’t support all configurations needed today, they can be combined with support of SQL Server instances for efficient and secure data delivery services.

Windocks is a complete container engine that provides integrated support for Pure Storage, NetApp, and other data storage systems, and includes Windows based database cloning as an option any DBA can implement. Windocks is a modern, open, data delivery platform that provides tremendous economy over various UNIX based legacy solutions.

Explore use of Windows based data management and delivery by downloading the free Windocks Community Edition, available here.

Similar posts

Get notified on new test data management insights

Be the first to know about new insights on DevOps and automation in the test data management space.