Karis Brummitt

15 August 2016

What is SQL Clone and why are we building it?

If you’ve ever worked in a team that uses a shared database for development, testing or UAT, you’ll appreciate some of the frustrations in simply ‘getting stuff done’. And you’ll be all too familiar with the reasons why… contentions for resources, overlap between projects, inconsistent environment states, and dependencies on other team members to get things deployed.

However, as an IT Manager or DBA, creating a replica of the production environment for each member of your team is no quick fix. That’s largely due to the amount of data stored in the database, and the time it takes to provision a new environment or perform a refresh. Yes, a backup and restore may seem a simple enough way to create an accurate database copy when working on a shared environment, but when you use this approach to create individual development environments, it can quickly transcend into a colossal waste of time and disk space.

Aside from this, there are concerns around data security when developing and testing code, which results in one of two things: manual data masking (which, let’s face it, can be a long and tedious task); or testing code on poor dummy data and hoping for minimal disruption post-deployment.

We’ve been thinking about this problem for quite some time at Redgate, and we think we’ve cracked it!

Who’s the sheep in shining armor?

Baa! – it goes by the name of SQL Clone. We’re building a rather radical tool at Redgate that simplifies database provisioning, safeguards data, and makes efficient use of disk space. SQL Clone takes the pain out of creating and managing multiple copies of production databases and allows database developers to work more like regular software developers, where they can experiment in isolation while still benefitting from a realistic server environment and data set.

With SQL Clone you can create a data image of a live SQL Server database (hosted locally or on Azure) or a SQL Server backup. The data image is a full copy of the database at a point in time and contains the source data from which the clones will be derived. Creating a data image takes about as long as restoring a backup and consumes as much disk space as a single restore. They can either be stored locally or shared with other machines by placing them on a Windows file share. But here’s where it gets interesting.

You can then create clones from the data image on any machine that can access the image file. Clones take up a tiny amount of disk space (40MB or so) and the provisioning time is the seconds it takes to set up a local differencing file and mount the database. They work just like normal SQL Server databases and can be connected to and edited with any program. These changes are specific to each clone and are persisted to the local differencing file. The rest of the data is accessed using the data image.

Creating data images and clones can also be automated using PowerShell. SQL Clone works by using the Virtual Disk Service in 64-bit Windows to allow the same bytes (the data image) to be reused many times – and on multiple SQL Server instances – as ‘clone’ databases. With a single central UI, a process for data masking can be defined and access to sensitive data can be restricted on a per data image basis to comply with data security regulations. Realistic test data can then be generated to replace sensitive data to allow development and testing on a realistic server environment and data set.

SQL Clone was initially envisaged as a way of ensuring all developers have a dedicated version of the current database build without cluttering up the disk-space on their own PCs. However, feedback from our technical preview to date has shown a magnitude of problems SQL Clone can help solve, including analysis, reporting, and debugging.

SQL Clone is currently in development and we’re not planning to release all of the functionality listed above in version one, but this is how we envisage the product could look in the future. Want to find out more about the new way of database provisioning? Join the SQL Clone beta.