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

Docker Windows SQL Server Containers and Database Clones

DZone's Guide to

Docker Windows SQL Server Containers and Database Clones

Learn how Microsoft’s containers, when combined with SQL Server database clones, can deliver a powerful set of benefits.

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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, as well as conventional servers and workstations. This solution runs on any public cloud or on-premise, including AWS and Azure. 

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, testing, and reporting and BI. 

Microsoft’s containers combined with SQL Server database clones deliver 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, improve team productivity, and simplify 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
  • 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 the 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). 

Image title

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.    

Image title

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 requirements for clones expand dynamically as changes are made, based on a copy-on-write design. Clones are well-suited for development and testing use. 

Delivering Database Clones to Microsoft’s SQL Server Containers

A PowerShell script is used to automate 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, regardless of the size of the database environment!

Image title

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

Image title

Observations and Next Steps

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

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
sql server ,database ,windocks ,database cloning ,docker windows ,containers

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}