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:
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.
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.”
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.