We live in a virtualized world, and VMware VMDKs are common for hosting SQL Server in many organizations.   Accessing databases in a VMDK, however, to support clonable SQL Server database images is not well documented.   A relatively simple solution is to mount the VMDK to a Windows server as a virtual disk, to provide access to the VMDK databases.

This article looks at the steps involved in mounting VMDK’s to a Windows host, and then building clonable SQL Server images, using SQL Server containers.    The same steps, however, should support PowerShell or Red Gate SQL Clone.

Options for mounting VMDKs to Windows

VMDK is VMware’s “Virtual Machine Disk” file format, and is analogous to Microsoft’s Virtual Hard Drives.   There are several options available to support mounting VMDK’s to Windows, including VMware Workstation, a command line utility using VMware’s Virtual Disk Development kit (VDDK), and a free third party tool for mounting virtual disks, OSFmount.  It is important to mount the VMDK to the same Windows release, to avoid potential compatibility issues.

The VMware Workstation includes a “Map Virtual Disks” feature.   Open VMware Workstation and use the File menu and select Map Virtual Disk from the drop-down list to map a VMDK to a drive letter on the Windows host.   Once mounted run File Explorer as Administrator to access the drive, and navigate to the desired databases.

For automated process support a command line utility “vmware-mount” is available via the vSphere Disk Development Kit (VDDK), which is available for free download from Vmware.  To mount a VMDK file to drive X, with the VMDK located in C:\temp, in read-only mode, use the following syntax:

Vmware-mount.exe X: “C:\Temp\TestVM.vmdk”

A third option is OSFMount, a free image mounting tool for ISO images and VMDK files.   OSFMount has a good reputation, although there are reported issues with image certificate signing on Windows Server 2016.

Click the Mount new button at the bottom of the window and follow the instructions.

Building database clone images with a mounted VMDK

Available VMDK’s are generally backups or snapshots of production systems. The VMDK is mounted to the Windows host as a separate drive in read only mode to expose the VMDK folder structure.   Once mounted the database files are copied to a Windows Virtual Hard Drive (VHD) on a separate disk (step 2).  Once the image is built, the source VMDK can be unmounted and deleted, and database clones are served to containers and instances (Step 3).

The Windows VHD is built on a separate attached disk to enable the removal of the mounted VMDK.   The new VHD is a full byte copy of the database environment, and delivers clones that are lightweight (<40 MB each) with full read/write suppport.  Images can scale to support scores of databases, and multiple Terabytes.  Windocks allows for the VHD to be located in an arbitrary directory using the following configuration setting.

VHD_PARENT_DIRECTORY="D:\vhddata"

A dockerfile defines the SQL Server release, and the databases that are copied from the VMDK to the VHD.  Each database occupies a separate line in the dockerfile with a SETUPCLONING RAW command.  A network mounted VMDK would require relative file paths.   Build time scripts are included with the COPY and corresponding RUN command.

FROM mssql-2017
SETUPCLONING RAW customers Z:\filepath\to\customerdata.mdf Z:\filepath\to\Files\ customerdata.ndf Z:\filepath\to\ customerdata.ldf
SETUPCLONING RAW operations Z:\filepath\to\operations.mdf
COPY cleansecustomerdata.sql .
RUN cleansecustomerdata.sql
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
RUN git.exe clone https://repo/url scripts
RUN orderscripts.ps1

The image is built with the >docker build -t <imagename> \path\to\dockerfile.   Run time scripts are included below the ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1.  In the example, a Git clone command clones a source repo into the container’s \scripts folder, and a powershell script creates a consolidated migration script.

Ordering SQL migration scripts using PowerShell

Database clones support both state based and SQL migration scripts.  One challenge when working with migration scripts is how to order or “marshall” migration scripts.  A PowerShell script (orderscripts.ps1) can be updated and committed to the repo to define the order of the migration scripts.   The script uses Get-Content piped to Set-Content to concatenate and create a single ordered script.

Get-Content scripts\script1.sql, scripts\script2.sql | Set-Content scripts\orderscript.sql

Orderscripts.ps1

The combination of the Git clone and orderscripts.ps1 ensures that each container is delivered with a clone of the repo, with an ordered migration script.

This combination provides developers access database clones sourced from the VMDK, as well as scripts cloned from a source repo, with a concatenated (ordered) migration script.   Developers, or a CI pipeline controls which of the scripts are applied at run time with the following docker command syntax.   Alternatively, the source repo can be cloned and scripts applied manually with SSMS or any SQL Server client.

>docker create -e RUN=”scripts\script1.sql” <imagename>

Database clones from database files, backups, and VMDKs

Database clones are growing rapidly in popularity, as they enable secure use of production databases for dev/test, in seconds.  Combined with data masking and encryption clones provide a secure near production environments.  Clones enhance development by surfacing performance and data issues earlier than when working with development databases built from source.   When working in a complex environment composed of multiple databases, clones are dramatically faster to provision than multiple database builds.

VMDK’s are often the easiest path for accessing production databases, and the methods outlined here provide a simple and repeatable process for database cloning.   To explore the use of VMDK’s as a source for database clones with SQL server containers download the free Windocks Community Edition at https://windocks.com/community-docker-windows