Database Migrations With Containers and Database Cloning
This article looks at how database cloning supports migration of SQL Server 2008 to SQL Server 2017 Linux containers.
Join the DZone community and get the full member experience.Join For Free
SQL Server migrations are a constant in the life of DBAs, and the end of support for SQL Server 2008 is driving a lot of migration planning. Database migrations typically involve restoring backups to target environments, to serve Development and QA environments for application testing, and to identify deprecated features. The challenge becomes burdensome when working with large and complex environments involving scores of databases that require hours to restore.
Docker containers combined with database cloning simplify migrations by utilizing immutable images that support delivery of complex multi-terabyte environments in seconds. This article looks at how database cloning supports migration of SQL Server 2008 to SQL Server 2017 Linux containers.
Windocks is a full-featured Windows container engine, which supports all editions of SQL Server 2008 onward and database cloning. Windocks also supports delivery of database clones for use with all SQL Server environments including Microsoft’s SQL containers (Linux and Windows) and conventional SQL Server instances. The combination allows delivery of SQL Server 2008 databases to the upgraded target.
Database cloning begins with immutable images that support the creation of clones for various target environments. Database images are built with Dockerfiles and 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 the 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 at the DevOps Enterprise Summit 2018 on their use of SQL Server database clones as part of their DevOps strategy:
Building Database Clone Images
Building a clonable database image begins with a plain text configuration file (Dockerfile) that specifies the target environment and the backups used. 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, and networked file shares would use 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 is built with the Docker command line or with the web UI by selecting the Dockerfile and assigning an image name and clicking the “Build” button.
Deliver SQL Server 2008 Databases to SQL 2017 Linux Containers
On completion of the image build, the web UI updates to display the new image. A dropdown list of databases is provided to allow users the option to work with a subset of the databases. Users are prompted to assign a port and sa password for the SQL Server 2017 Linux container.
It takes less than a minute to create a database clone and provision a Linux SQL Server 2017 container and mount the databases. Windocks tracks the life cycle of the cloned databases and cleans up the 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.
The process of provisioning fresh environments can be repeated as needed to support the testing of the upgraded environment.
DevOps With Data and Database Migrations
DevOps strategies are widespread for front-end and stateless applications, but organizations continue to struggle to incorporate relational backends into a CI pipeline. Industry surveys indicate the average database backend test environment is updated twice monthly or less, and few organizations achieve adequate test coverage 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 complete environments for 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.
Opinions expressed by DZone contributors are their own.