The end of Microsoft support for SQL Server 2008 and 2008 r2 is leading to a lot of SQL migration planning. SQL Server migrations often involve restoring backups to target environments, to serve Development and QA environments for application testing and to identify deprecated T-SQL features. The challenge becomes burdensome when working with large and complex environments, involving scores of databases or large environments that require hours to restore. The challenge presents added complexity if evaluating more than one potential upgrade target. Many organizations are evaluating SQL Server 2017 on both Windows and Linux.
Fortunately, database cloning simplifies delivery of large and complex environments, with delivery to varied SQL Server target environments. This article looks at how Windocks database cloning is used for migrating SQL Server 2008 to SQL Server 2017 Linux containers.
Database cloning and Database-as-a-Service (DBaaS)
Windocks is a database cloning service and a SQL Server container engine, supporting all editions of SQL Server 2008 onward, with delivery to any SQL Server target environment. Clones are created and mounted to SQL Server instances, or Microsoft’s Docker SQL Server containers on Windows or Linux. Immutable images support automated or user-driven delivery to target environments, providing an effective Database-as-a-Service.
Database clones are built using Full or Differential backups that are restored into a Windows Virtual Hard Drive (VHD). The parent VHD becomes an immutable full byte copy, and supports delivery of read/write “differencing disks” (clones) in seconds, each using less than 40 MB of storage. SQL Server cloning is available wherever Windows Servers are supported, and is popular for development and test support with SQL Azure, AWS, or on premise. John Hancock spoke recently at the DevOps Enterprise Summit 2018 on their use of SQL Server database clones as part of their DevOps strategy: https://youtu.be/ZtsTq0bmzRw.
This approach delivers proven economy with an average 5:1 reduction in VMs used, and even greater savings in storage with a 95% reduction in storage.
Building clonable SQL Server images
The process of building a clonable image begins with a plain text configuration file that specifies the target environment and the backups used. Windocks is a port of Docker’s open source project to Windows, and uses standard Dockerfiles to build images. The following example builds an image that targets a Linux container with clones from four SQL Server 2008 Full backups. The backups in this case are local, but networked file shares are also supported with universal file paths.
The Dockerfile begins with the SQL Server 2017 image, and is followed by an environment variable used to save and run the Dockerfile at run time. The Dockerfile includes both build time and run time parameters, including target IP address, shared SMB folders, and user assigned ports and sa passwords. The SETUPCLONING FULL command identifies the backups used to build the image.
The image can be built using the Docker command line, or via the Windocks web UI, by simply selecting the Dockerfile, assigning an image name, and selecting the “Build” button.
On completion the web page updates to display the new image, along with the user provided port and SQL sa password. The web UI also allows the freedom to work with a subset of the complete image, with a dropdown list tool.
It takes seconds for the clone to be created and provision a fresh Linux hosted SQL Server 2017 container, and mount the databases. Windocks tracks the complete life cycle of the cloned databases, and cleans up the created containers and mount points when they are no longer needed. The web page updates and displays the added environment under the Data Environments section. The container is accessed via SSMS with the assigned port and sa password.
DevOps with Data and SQL Server Migrations
DevOps strategies are ubiquitous, and most advanced for support of .NET and Java for front-ends, and middle-tier applications. Organizations continue to struggle, however, to incorporate stateful back-ends into a Continuous Integration pipeline. Industry surveys indicate the average database back-end test environment is updated twice monthly or less, and few organizations achieve adequate test coverage with VM provisioning that is prevalent today.
It’s time to modernize SQL Server migrations and DevOps with production database clones. Clones provide full read/write support, are provisioned in seconds, without impacting storage, and provide development and test the most realistic environment for functional and unit testing. While we advocate the use of Docker containers for dev/test, organizations also need data delivery for all SQL Server environments, and Windocks addresses this by supporting Microsoft Dockers SQL Server containers as well as instances, and Kubernetes. Finally, the process outlined in this article applies equally well for migration testing SQL Server 2008 to SQL Server 2016 or other targets.
You can get started today with a SQL Server containers with database cloning, in the Windocks free Community Edition, available at: https://www.windocks.com/community-docker-windows