Docker Windows SQL Server Containers and Database Clones

Microsoft’s adoption of Docker containers is a boon for Continuous Integration and DevOps initiatives, with .NET and Java application environments provisioned in seconds. But, incorporating SQL Server environments with large databases continues to be a challenge. SQL Server containers are provisioned on demand, but data environments can require hours to replicate or restore. Windocks has pioneered the use of Windows based SQL Server database clones, and is pleased to introduce support for Microsoft’s SQL Server containers, and conventional servers and workstations.

SQL Server clones utilize Hyper-V based Virtual Hard Drives (VHDs) and run wherever Windows Servers are supported, including public clouds or on premise infrastructure. Terabyte class environments are delivered in seconds with full read/write support, and are ideal for development, test, and reporting and BI.

Microsoft’s SQL containers combined with SQL Server database clones delivers a powerful set of benefits:

• Existing Jenkins, TeamCity, and Visual Studio users can easily incorporate SQL Server workloads. The integrated workflow improves developer productivity, test coverage, and results in higher quality releases.
• Docker containers and images are portable, improving team productivity, and simplifying cloud migration strategies. An image that runs on a developer’s machine can be shared with a test team, without DLL dependencies or configuration issues hindering usage.
• Data images include privacy and security enhancing Data Governance and Regulatory Compliance, with secure data distribution within the enterprise.
• Terabyte class data is delivered in seconds, and requires less than 40 MB of storage.
• Affordability: this is a Windows based alternative built for SQL Server DBAs, with dramatically lower prices and cost of ownership than UNIX storage appliances.
• Teams simplify operations with containers on a shared host, reducing use of VMs by 5X or more.

SQL Server Database Cloning

SQL Server clones are based on Windows Virtual Hard Drives (VHDs), with “Differencing disks” providing writable cloned databases. The parent VHD is a full byte copy of the data, and can support a single database or scores of databases in each clone. Scripts are run during the parent VHD build, creating an immutable artifact that incorporates user permissions, data masking, and other preparations for regulatory and policy compliance.

The workflow begins with full and differential backups delivered to a Windows file share (step 1 below). Windocks builds the clone image by restoring the backups and applying SQL scripts as defined by a Dockerfile (step 2). Once the image is built, clones are created on demand for users (Step 3), and delivered to SQL Server containers and other instances (Step 4).

SQL Server images are defined with a Dockerfile that can include scores of backup files with a SETUPCLONING command as illustrated below. SQL Server scripts are applied during the image build, to address user permissions, encryption, and data masking.

The initial build of the parent VHD based on a full backup can take time associated with the backup restore process, but clones are created in seconds, are writable, and occupy <40 MB of storage. Storage requirement for clones expands dynamically as changes are made, based on a Copy on Write design. Clones are well suited for Development and Test use.

Delivering database clones to Microsoft's SQL Server containers

A PowerShell script automates the creation of the clone which is mounted to a fresh SQL Server container. The Windocks design matches each clone to a single container, so the container can be stopped and restarted for however long the environment is needed.

The workflow involves the creation of the clone, parsing the file paths and databases. Then, the SQL Server container is created, and the cloned databases are mounted. The script prompts the user for the container host IP address, assigned container port, and SQL sa password, and the SQL Server image to be used. It takes less than two minutes to provision the complete environment, irrespective of the size of the database environment!

On completion we have a running SQL Server 2016 Developer container, with the mounted cloned database. We access the container using SQL Server Management Studio, referencing the container host IP and port, 192.168.1.102,11000 (note the use of the comma separator)! This approach is compelling for large data environments that are composed of scores of databases. In this case the clone mounted is a single database, “customerdata.”

Observations and next steps:

Feedback on SQL Server containers with cloned databases has been very positive for development and test, and reporting and BI. Customers report improved efficiency, test coverage, and improved quality of releases, and a dramatic reduction in the number of VMs used. Check it out for yourself by downloading the Windocks Community Edition available here.