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

Modernizing .NET and SQL Server Development and Testing

DZone's Guide to

Modernizing .NET and SQL Server Development and Testing

· Database Zone
Free Resource

Finding a database that fits with a container-based deployment model can be frustrating. Learn what to look for in a Docker database

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

Database Clones?

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 by the Windocks host or on a network Windows Server file share, as shown below.

modernize dev image 1 500 x 365

Database clones are created in seconds, occupy <40 MB, and are writable. The 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 well-suited for performance and database stress testing. A growing number are also exploring the use of clones for reporting and BI.

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 the \windocks\samples directory. Network- based files require the use of a universal network path. 

modernize dev image 2 650 x 116

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 the Admin. The daemon can always be restarted with a right-click on the desktop Windocks icon and hit 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 to 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.modernize dev image 3 650 x 332

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 hit Select, enter an image name, and hit 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).

modernize dev image 4 650 x 250

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 Create.

modernize dev image 5 650 x 375

New containers and database clones are delivered in seconds, irrespective of the size of the VHD, and each clone occupies less than 40 MB. The container includes an assigned port, a SQL sa password, a database name, and a 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 you 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.

modernize dev image 6 650 x 225

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.

modernize dev image 7 650 x 330

modernize dev image 8 650 x 312

Create an Updated SQL Server Image

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.

modernize dev image 9 650 x 190

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 depend on the full backup parent image and become 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, navigate to the Dockerfile and script, highlight both files, right-click, and hit Select. Assign a name to the image and click Create. Once the image is available, assign a new container name and Create. The page will refresh and display the container and access details.

modernize dev image 10 650 x 300

Pros and Cons of SQL Server Containers With Cloned Databases

The processes outlined in this article are ideal for the 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.

When you're looking for a SQL database that can scale elastically, while still preserving ACID guarantees, you only have a few choices. Find out how these elastic SQL databases perform in thishead-to-head YCSB benchmark.

Topics:
sql ,database ,containers ,sql server ,.net ,database cloning

Published at DZone with permission of Paul Stanton, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}