Introduction to SQL Server Containers

SQL Server containers using database cloning supports delivery of large, writable SQL Server environments in seconds, with minimal storage requirements.  SQL Server clones are based on Windows Virtual Hard Drives (VHD), and support creation of “differencing disks” which are writable “cloned” database environments.   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 the SQL Server image.   Containers created from the image include cloned database files that can 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.   Clones are not recommended for performance and database stress testing.

SQL Server images are defined with a configuration file (Dockerfile) that includes one or more SETUPCLONING commands that are executed on the Windocks host, or attached file share.      SQL Server scripts can be applied during the image build.  The example below is \windocks\samples\testfastcloningfromfullbackup, and uses a SQL Server 2012 backup located in the dbbackups folder.   Network located files are used with a universal network path.

Start the Windocks daemon and web UI

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.

Use the Windocks web UI to build a custom SQL Server image

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 normally 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 the user can work with the environment by starting the container, or with appropriate file share permissions can navigate to the databases and attach them 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.

Using the Web UI to create updated SQL Server images:

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.   

Using the Command Line Interface

 Windocks is an independent port of Docker’s open source to Windows, and uses standard Docker client software.   The Windocks installation includes a Docker.exe that can be copied to any Windows client, and can be used locally on the Windocks host.

The docker client on the Windocks host is included in the system path, so is available via a standard command prompt window or PowerShell on any directory path.   For client machines either setup the system path, or navigate to the directory where the Docker.exe is located to work with the following commands. 

The CLI supports a standard set of Docker commands.  When working from a remote client, the syntax takes on an added complexity, and requires the host firewall to be configured to allow inbound traffic on ports 10000 – 10200, 2375, and SQL Server port 1433.

>docker  -H tcp://ip.address.of.host:2375 images            Remote client call for >docker images

Docker commands used on the Windocks host use the simpler syntax shown below:

>docker images                                                            Creates a list of the available images on the host

>docker run  -d <image>                                            Creates a running container based on the image

>docker build  -t <imagename>                                Builds a container and image, in the case of images

                                                                                           with cloned data, the container produced reflects the

                                                                                           preceding image.

>docker ps                                                                        Lists containers on the host, their ports, and state

>docker commit <containerid> <imagename>     Creates a new image based on the container

>docker create <image>                                              Creates a container from the image in a stopped state

>docker start <containerid>                                       Starts the container

>docker stop <containerid>                                       Stops the container

>docker rm <containerid>                                           Deletes the container

>docker rmi <imagename>                                         Deletes the image

 Below the docker client is run locally on the Windocks host.  The >docker images command confirms the daemon is running, and the SQL Server 2014 image is available.  We build an image assigning the image name of “clone” (>docker build  -t clone).   When building images with database clones, the container delivered reflects the source image it is built from.   In the example below, the delivered container is nothing more than the base mssql-2014 image, and can be disregarded.     A second >docker images command confirms the new clone image is available.

Containers with the clone environment are delivered with a >docker run –d <image> command.  In the example we assign a port at 10050, a container name of “pauls,” and the return string includes the containerID, with the port and SQL sa credentials.

The container is accessible via SQL Management Studio and other tools, for remote access use the SQL sa credentials.

To update the image, we build a new image.   We can apply a SQL Server change script to the Full backup, and create a modified version of the current image.   Or, we can apply a Differential backup.  In the example below, note that the image refers to the parent VHD (clone), that we are updating the image with the differential backup, and running the same data masking scripts used earlier.

The build uses the SETUPCLONING DIFF command.   The container delivered below is from the source clone image, and can be disregarded.   The newclone image is confirmed with a >docker images command.

Pros and Cons for SQL Server containers with Cloned databases

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. 

Additional Reading and Resources:

SQL Server containers are popular for delivery of environments for Development and Test.   Teams work with containers on a shared server, rather than with individual VMs.   A container based environment delivers large and complex environments in seconds, and the team saves hours each week in VM maintenance.   Teams drive significant savings by reducing VMs used on average by 5:1.  

Download a free Windocks Community edition for your own exploration at:   https://www.windocks.com/community-docker-windows