SQL Server Clones and Containers for DevOps

DZone 's Guide to

SQL Server Clones and Containers for DevOps

Windocks is an independent port of Docker’s open source to Windows. Combine SQL Server containers with Windocks' integrated database cloning and web UI for testing.

· DevOps Zone ·
Free Resource

Windocks is an independent port of Docker’s open source to Windows, and is unique in supporting Windows 8 and Windows Server 2012 (in addition to Windows 10 and Server 2016). Windocks also supports all editions of SQL Server 2008 onward, along with .NET and a number of open source projects.  

It’s been a year since Windocks was released, and users have asked for simple, affordable support for large SQL Server environments for development and testing. We’re pleased to answer by delivering the first port of Docker’s open source with integrated database cloning, along with a web UI. Let’s take a look at the combination of SQL Server containers with database cloning, using a free downloadable Windocks Community Edition.

Windocks Installation and Starting The Web UI

WinDocks includes an automated install that detects any edition of Windows 8 or 10, and Windows Server 2012 or Windows Server 2016. The install takes 10-15 minutes and requires a reboot of the system to complete. Windocks can also be updated with a momentary stop and restart of the daemon, and containers are unaffected during an update. Cloning is built on Hyper-V server, so is limited to Pro and Enterprise editions of Windows 8 and 10, and Windows servers.

The Windocks daemon starts automatically, and the web UI is accessed with a browser using the “localhost” address. The web UI is then pointed to either the localhost ( or other WinDocks host IP addresses. Users access the UI by simply directing their browser to the appropriate IP address.    

The UI lists available images and containers. DBAs can create images and Developers self-service SQL Server instances with data in seconds. Teams work with isolated containers on a shared server and use just a single server rather than up to 10 individual VMs. Windocks uses a default SQL Server installation to support the creation of containers. This approach works well as container support is added to existing systems and existing Microsoft license support for SQL Server named instances.

A single click of the Image button creates a container. Custom images can also be easily created through the “choose files” tool. A container is built with a Dockerfile, databases, and SQL scripts.   More on this below. 

Image title

SQL Server Container = SQL Server Named Instance With Docker Commands

A Windocks SQL Server container is simply a SQL Server named instance that is provisioned and managed with the Docker commands. Containers include added user and process isolation, but are compatible with all standard SQL Server applications and tools. There is ongoing debate within the Docker ecosystem about container support of persistent data and stateful applications, but these concerns don’t apply here as Windocks SQL Server containers are standard SQL Server instances. Certainly, SQL Server has been proven to support persistent data and stateful applications over the past two decades!

Data can be copied into the container file system, or mounted from a host or network based location. Databases copied into the container file system are a good solution for databases up to a few GB in size, and support the creation of a custom image. This is particularly suited for development and testing, as the databases are removed when containers are deleted (no concern for data persistence).  Where data persistence is needed, databases can be mounted to the container. A special form of database mounting includes the use of database clones, which is described further below.

SQL Server DBAs build images using the standard Docker client CLI or use the web UI.  The “Choose Files” tool is used to select a Dockerfile and associated databases and scripts to build a container or image. Dockerfiles define the configuration of a container, beginning with the image and followed by files that are copied and run in the container. A sample Dockerfile is shown here from the Windocks samples folder that builds a SQL Server 2012 container with three databases copied into the container file system (via the ADDDB command), and a cleanseData script as well. WinDocks includes a couple of dozen working samples in each install, in the \\windocks\samples directory.

Image title

To build the container, navigate to the folder, select the files, right-click and “select,” and click the “new container” button. The sample below refers to a build of a .NET app, in the “testdotnet” folder. 

Image title

Within a few seconds, the container is built and can be started and committed to form a new image.  

SQL Server With Database Cloning

The new web UI is popular as a self-service tool for developers and testers, but feedback on Windocks 2.0  integrated database cloning is even more enthusiastic. Cloning leverages Hyper-V virtual disks to enable a Terabyte-class database to be “cloned” and delivered to developers in seconds, and only require 40 MB of storage for each clone. Clones begin by creating a parent Docker image based on database files (mdf, ndf, and ldf), or by restoring a backup, either on the Windocks host or a network file share.   Each parent image is a full byte copy of the database, and can incorporate data masking implemented with SQL scripts.

Image title

Clonable images are built with a single command and a Dockerfile, as shown. The creation of the initial parent image will take a substantial block of time, as the snapshot or database is restored with a full byte copy. Once created, however, subsequent cloned environments take just seconds.   A single click on the clone image provisions a container with the cloned data. Each cloned environment is also writable. A full backup can be updated with differential backups to support dev and test teams for a week or more at a time. 

Terabyte Environments in Less Than a Minute With SQL Server Containers and Clones

Windocks 2 cloning is based on Windows Virtual Disks and can be used on-premise, Virtual Machines, or public clouds.   The design is extensible and we’re collaborating with other vendors to support a wide range of cloning solutions. A number of Windocks customers rely on NetApp, EqualLogic, Red Gate, and other solutions today.  

One advantage of integrated cloning support is that Windocks manages the creation of the image, as well as clones, mount points, and effecting the mount to a container instance. When a container is removed, Windocks takes care of removing the associated clone and mount point.   Maintenance is as simple as cleaning up unused containers and associated images.

SQL Server containers align with Microsoft’s strategy with Docker-based tooling and should put any organization on track to migrate to future Microsoft SQL Server related tooling and continuous integration strategies. We believe that the combination of SQL Server containers with integrated image and clone creation is the easiest, most efficient, and most affordable solution on the market for the delivery of large data environments for development and testing. 

Check it out yourself by downloading a free download of the Windocks 2.0 Community Edition at  http://www.windocks.com/community-docker-windows.

containers, database cloning, devops, docker, software testing, sql server 2012, windocks

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}