Last Fall Microsoft released Windows Server 2016 with Docker container support, marking the first shared API for containers with AWS, and Red Hat. Since then Microsoft has emphasized the use of containers for development and test on SQL Server 2017.
Windocks has a lower profile than Microsoft, but we actually were first to market with our port of Docker’s open source for Windows in April of 2016. Windocks includes broader support of the Windows OS family (Windows Server 2012 and 2016), and for SQL Server with all editions of SQL Server 2008 onward. Importantly, Windocks is an easy addition to existing systems and uses existing Microsoft licenses.
Last month we released support for an integrated web UI, and also became the first container engine to include database cloning. The web UI and database cloning reflect consistent customer requests for self-service developer use, and support for large data environments. This article explores the new web UI, and use of database clones.
The response to containers combined with clones has been terrific, as terabyte class environments are delivered in seconds, and require only 40 MB of storage for each instance. The web UI is also popular as it saves developers and testers from needing to learn yet-another-CLI.
SQL Server clones are writable databases that are delivered in seconds, and that require less than 40 M of storage. Clones are based on Windows Virtual Hard Drives (VHD), and support creation of “differencing disks” which are writable “cloned” databases. The VHD Is created with databases defined by a Dockerfile (a plain text configuration file), using Full and Differential backups, or snapshots, and SQL Server scripts.
The VHD is a full byte copy of the data, and is included in a SQL Server image. Containers created from the image include cloned databases that can also be used by conventional SQL Server instances. The process is supported on the Windocks host, or on a network Windows Server file share, as shown below.
Database clones are created in seconds, occupy <40 MB, and are writable. Storage requirement for each clone grows dynamically as changes are made. Clones share read-only access to the parent VHD, and are well suited for Development and Test use, but are not suited for performance and database stress testing. A growing number are also exploring use of clones for reporting and BI uses.
SQL Server images are defined with a configuration file (Dockerfile) that includes one or more databases that are cloned with the SETUPCLONING commands, which generate the VHD. SQL Server scripts can be applied during the image build to implement data masking. The example below is \windocks\samples directory. Network based files require use of a universal network path. ,
Following the Windocks install, the Windocks daemon will auto-start after each reboot. The auto-start reflects the permissions of the user login, and must “run as Admin.” The daemon can always be restarted with a right-click on the desktop Windocks icon, and “run as admin.” Minimize the daemon Window to allow the daemon to run in the background.Open a Firefox or Chrome browser on the Windocks host, and direct the browser to localhost. Once the Windocks web UI resolves enter the local loopback address: 127.0.0.1. The Windocks web UI is now visible, and displays the images available. From a remote workstation, using Firefox or Chrome enter the URL of the Windocks host IP address. Ensure the Windocks host firewall is configured to allow inbound traffic to ports 10001 to 10200 (container ports), 2375 for the Windocks daemon, and SQL Server default port of 1433.
To build a SQL Server image click the “choose files” tool and navigate to the Dockerfile and scripts (\windocks\samples\testfastclonefromfullbackup). Highlight the files, right-click and “select,” enter an image name and “build.” The Dockerfile and scripts are copied to the Windocks for execution. The SETUPCLONING commands are executed on the host, and create the VHD on the host or on a network attached file share (as defined by the Dockerfile).
Multiple databases can be moved or restored, and striped backups are supported. The VHD is built in the same folder as the backup, and the first disk defined in the case of a set of striped backup files.
Building the image takes time associated with restoring the backup, as the image is a full byte copy of the database. When the image is available enter a name for a new container, and click on “create.”
New containers and database clones are delivered in seconds, irrespective of size of the VHD, and each clone occupies less than 40 MB. The container includes an assigned port, SQL sa password, database name, and full path to the cloned databases. At this point you can work with the environment by starting the container, or with appropriate file share permissions can navigate to the databases and attach to another SQL Server instance. The cloned databases reflect the steps involved in the image build, including any data masking scripts applied.
Starting the container attaches the cloned databases, and makes the container accessible. Remote clients use the host IP address (port separated by a comma), and the SQL sa credentials to access.
Images that include cloned databases are updated with Differential backups, or scripts, through another image build. As before, the build is defined by a Dockerfile. In this case we use a Differential backup that is included in the \windocks\dbbackups folder. The Dockerfile used is located at \windocks\samples\testfastclonefromdifferentialbackup.
The update is applied to the parent image (FROM newsql in our example), and applies a differential backup with the SETUPCLONING DIFF command. SQL Server scripts are copied into the container during the build. Any files that need to be copied are located in the same folder as the Dockerfile. Note: Windocks image names are case sensitive!
Updated images based on backups depends on the Full backup parent image, and becomes another full byte copy of the environment. Updated images can be deleted and replaced, but require the original parent image.
To build the updated image, select the “choose files” tool and navigate to the Dockerfile and script, highlight both files, right-click and “select.” Assign a name to the image, and click on “create.” Once the image is available, assign a new container name and “create.” The page will refresh and display the container and access details.
The processes outlined in this article are ideal for delivery of Dev and Test environments of any size. Each container is delivered in approximately 30 seconds, irrespective of the size of the image, is writable, and only occupies <40 MB on delivery. A team can work with scores of identical environments on a single shared server, and save hours each week in VM maintenance (and on costs of VM infrastructure). Containers are created quickly, so this workflow is ideal for short-lived instances needed for Dev and Test. The web UI provides Developers an easy to use self-service environment.
An additional advantage is that the cloned databases can be used with conventional SQL Server instances, or the containers, so Windocks is both a container engine as well as general purpose SQL Server cloning solution.
Cloned environments are not well suited for performance or stress testing, and system planning can be challenging. As changes are made to the clone, the disk footprint of the clone will expand dynamically.
Later this summer we plan to ship the first Windows based Continuous Integration support that combines Jenkins with .NET and SQL Server containers. If your organization is interested, contact us at firstname.lastname@example.org to inquire about early access.
If you’re interested in piloting Windocks for your development teams, contact us at email@example.com to learn about our risk-free QuickStart program.