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

SQL Server Database Cloning on AWS

DZone's Guide to

SQL Server Database Cloning on AWS

Database clones are great for Dev and Test environments, allowing devs to work with lightweight images in preparation for cloud deployments.

· Cloud Zone ·
Free Resource

Site24x7 - Full stack It Infrastructure Monitoring from the cloud. Sign up for free trial.

WinDocks is an independent port of Docker’s open source solution to Windows. Released in April 2016, WinDocks is popular for support of SQL Server Development and Test environments. The WinDocks 2.0 release this past month added database virtualization and is usable on any infrastructure or public cloud.

Cloning enables delivery of writable, terabyte-class databases in 30 seconds while using only 40 MB of storage. Let’s take a look at the technology and how it’s being used. (In the interest of full disclosure, I am a principal at WinDocks.)

Database Cloning  

A “clone” is a byte copy replica of the original. In this article, clones are based on Virtual Hard Disks, which support writable databases that are provisioned in seconds, and require only a fraction of the storage of the original. WinDocks cloning is based on the Windows Storage Management API. While Hyper-V is not supported on AWS, Windows Virtual Disks run wherever Windows Server 2012 and 2016 are supported. A writable 1 terabyte database can be cloned and delivered in just 30 seconds and, as mentioned, requires only 40 MB of storage! 

The process begins with a snapshot or backup that is restored to a VHD. The VHD becomes a full byte copy of the source database, and takes the normal time needed for the database backup to be restored. Once created, the VHD supports the creation of “clones” in the form of a Windows “differencing disks.” Each differencing disk includes pointers to the full byte copy image (the VHD parent) and captures any changes made. Clones are created in seconds and can be delivered mounted to a SQL Server container or used with existing SQL Server instances.

Image title

WinDocks builds the VHD with a Dockerfile using a command SETUPCLONING FULL (similar commands support the use of Differential backups or database files). WinDocks creates the VHD, the associated mount point, restores the database(s) to the VHD, and associates the VHD with a new Image. The VHD and associated backups can be located on the WinDocks host or a network attached file share. The Dockerfile below illustrates the use of multiple databases, with a data masking script applied to one.  

Image title

The image is built with a standard Docker build step, with an assigned image name of “clone.”

docker build  –t clone  c:\windocks\samples\testfastclonefromfullbackup


On completion, the image is available to create containers with the cloned database environment.   The standard Docker CLI is shown, with the return string including the containerid, port, and optional SQL as credentials.

Image title

Web UI and Clones for Non-Container SQL Server Instances

SQL Server database cloning was implemented to respond to requests for improved support of large and complex database environments, and requests also include a simpler web UI and flexibility to use SQL Server cloned environments with conventional (non-container) SQL Server instances. So, WinDocks 2.0 also includes a web UI and flexible use of the cloned environments with either containers or mounted to other SQL Server instances.  

The web UI is pointed to the IP address of the WinDocks host, a local host loopback address in this example. Images are listed followed by containers. A single click on the “create” button delivers a new container with cloned databases.

Image title

Images that include cloned databases include both a container and the full path to the cloned database(s). Below, a container is based on the “clone” image includes the port at which it is accessed and the full path to the cloned databases. The user can work with the clone + container instance, or mount the clones to a personal SQL Server. 

Image title

Benefits of Clones

Clones are well suited for most Development and Test uses. Where performance is critical, it is recommended that clones be accessed with high performance or dedicated LANs, but many teams are finding clones accessed over the Internet work well, too. This combination is particularly popular for teams that work on production database environments, and with more production workloads running on AWS, the use of clones is growing.   

The advantages delivered with clones are many:

  • Clones provide practical support for large and complex database environments and provide writable databases that are delivered in seconds. WinDocks containers and clones are compatible with all existing SQL Server tools and interoperate with VSS and SQLwriter based backup systems. 
  • Cloned images are immutable, versioned, and auditable for complex database environments.   This dramatically improves support for data governance and regulatory compliance.
  • Clones are economical and minimize network and storage costs of database copies.  
  • WinDocks customers average a 10:1 reduction in VMs used, by using containers on a shared host.

Explore the use of SQL Server Clones on AWS

WinDocks 2.0 is both a full-featured .NET and SQL Server container engine, and a general purpose tool for creating SQL Server database clones. WinDocks supports cloning on Windows 8.1 or Windows 10, Pro and Enterprise editions, and Windows Server 2012 or Windows Server 2016. WinDocks supports all editions of SQL Server 2008 onward, based on a default or named instance installed on the WinDocks host.  

Try it out by downloading a free Community Edition of WinDocks, available here.

Site24x7 - Full stack It Infrastructure Monitoring from the cloud. Sign up for free trial.

Topics:
aws ,sql server ,cloud ,windocks ,tutorial

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}