Migrations of SQL Server databases to a recent release is an ongoing challenge of SQL Server DBAs. In this article we take a look at how containers and database cloning can simplify the process, with an in-depth look at migration of SQL Server 2008 to SQL Server 2017 Linux containers.
SQL Server migrations is a constant in the life of SQL Server DBAs, and the end of support for SQL Server 2008 and 2008 r2 is driving a lot of migration planning. SQL Server migrations typically 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 that require hours to restore.
Docker containers combined with database cloning can simplify migrations, by utilizing immutable images that support delivery of complex multi-terabyte environments in seconds. This article looks at how Windocks database cloning supports migration of SQL Server 2008 to SQL Server 2017 Linux containers.
Windocks is a full featured Windows container engine, supporting all editions of SQL Server 2008 onward with Windows containers, and database cloning. Windocks database cloning supports delivery to all SQL Server environments including Microsoft’s SQL containers (Linux and Windows), and conventional SQL Server instances.
Database cloning begins with immutable images that support 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 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.
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 Windocks web UI by selecting the Dockerfile and assigning an image name, and clicking the “Build” button.
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 upgrade environment, and offers an excellent platform for Windows to Linux SQL migrations.
DevOps strategies are widespread for front-end and stateless applications, but organizations continue to struggle to incorporate relational back-ends into a CI pipeline. Industry surveys indicate the average database back-end 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.
Get started today with a SQL Server containers with database cloning, by downloading the free Windocks Community Edition.
In this article we look at how SQL Server database clones with TDE encryption are delivered to Windocks SQL containers and the official Docker SQL...
In this article we look at the new support in Windocks 3.0 for delivery of SQL Server database clones to the official Microsoft Docker SQL Server...
In this article we take a look at Windocks new support for creating SQL Server database clones that are delivered to Microsoft's official Docker SQL...