Product articles SQL Clone Development and Testing with Clones
A behind the scenes glimpse of SQL…

3 November 2016
3 November 2016

A behind the scenes glimpse of SQL Clone

It has always been a difficult task to provision development and test environments so that they reflect as closely as possible what’s present in production. With the rise of containerization and Infrastructure as Code (IaC) technologies, some parts of this are becoming much easier. We can automate the process of spinning up and configuring new virtual machines, of installing and configuring SQL Server, and so on. However, we still have one missing piece here: data. This final step of being able to deploy and then tear down, on demand, a database filled with realistic volumes of data, on multiple development and test environments, is still a long and slow process.

Most of us have rolled our own solutions for this, due to lack of tooling help in this space, but there are plenty of reasons why we need to find better ways to solve this problem. Firstly, it makes it much easier to diagnose production problems, without needing to access the live production database. Secondly, it makes it possible to have realistic testing environments, where developers can perform test runs quickly and in parallel, to verify that the application integrates, performs, and scales as required.

Fortunately, it turns out that we can use the technological advances that underlie virtualization to make the database provisioning process a simple, quick task. SQL Clone uses established disk virtualization technologies that are built into Windows to allow us to make clones – “editable copies” – of a SQL Server database within seconds, using very little additional disk space.

From SQL Server’s perspective, the database clone we create is just a normal database, complete with data, and we can query it and update it just as we would any other database. The Redgate website outlines the basics of what SQL Clone can do, and some of the use cases, but in this article I want to explain how SQL Clone actually works.

Core SQL Clone concepts

Before trying to understand how SQL Clone works, we should first understand the two core concepts behind it, which are data images and clones.

A data image is a point-in-time representation of your SQL Server database, which can be created from a backup file or a live SQL Server database. It contains a copy of the MDF (schema and data) and LDF (transaction logs) files of the database. A SQL Clone data image is immutable and cannot be altered after it’s created.

Producing the data image takes as much time as a backup or restore, depending on the size of the source backup file or database. Of course, we can automate and schedule this process so that it runs overnight, if required.


A clone, on the other hand, is an isolated copy of the database which is derived from a data image. One of the most magical parts about a clone is that it’s blazingly fast to produce one, no matter the size of the source database. For example, the clone of a several-hundred Gigabyte database will be tens of megabytes in size.

We can create multiple clones from the same data image and all of them will be isolated from each other. That means that any change we perform on a clone database will have an effect only on that clone, and will be isolated from the immutable, point-in-time data image of your database.

This means that we can easily create multiple isolated clones, each deriving from the same data image. This is very useful in many cases, but it’s especially valuable in a team environment where, for example, multiple developers can work with their own clones at the same time without affecting each other.

How SQL Clone works

SQL Clone takes advantage of several native Windows disk virtualization features such as Volume Shadow Copy and Differencing Virtual Hard Disk. These technologies form the core of SQL Clone, and it is the Differencing Virtual Hard Disk technology that allows us to isolate changes to a virtual hard disk.

SQL clone works at the file system level and creates a database clone that is essentially just a Virtual Hard Disk (VHD) mount point, which holds the source data image of the database, plus a differencing disk. When we modify anything in a clone database, SQL Server writes the change to the virtual differencing disk files. When we query data, the operating system reconstructs the required data pages from the differencing disk files plus the database files in the original data image.

All of this is transparent to SQL Server, which works with the clone just as it would any other database. Conceptually, we can compare this to the process of creating and then modifying a file in a version control system (VCS). The VCS does not create a new copy of the file each time we modify it. Instead, it stores the ‘deltas’ and then uses these plus the original source file to reconstruct the file at the correct version.

SQL Clone Architecture

At the architectural level, SQL Clone has two essential components. The first is the SQL Clone Server, which acts as source of all truth and the message router. It runs as a Windows Service and you can interact with it through its beautiful web client on a browser:


Ideally, you would install this component on a server that everyone on your team can then access.

The second important component is the SQL Clone Agent, which acts as the gateway between several resources (SQL Server Instances, Backup Locations, Image Destination Locations, etc.) and the SQL Clone Server.

The Agent component is responsible for performing the work you request through the SQL Clone Server, such as taking a data image from a backup file or creating a clone from a data image. You can obtain an Agent installer through the SQL Clone Server web client to install on machines which have access to your resources.


The SQL Clone Agent installer is packed with the necessary information to ensure all communication between itself and the SQL Clone Server is secure and legitimate.

Once you install the SQL Clone Agent, it should do the necessary handshake with the SQL Clone Server instantly to establish a trusted, secure connection and the SQL Clone Server should discover the newly installed Agent.



If you think about SQL Clone in terms of a restaurant for a moment, it can get a lot easier to digest how its components function.
The SQL Clone Server in this analogy is the waiter or waitress. It’s the face of the restaurant, and takes your order, records the order in a safe place and lets you know when it’s ready.
The Agent is the chef who does the heavy lifting and is actually responsible for cooking your meal.
However, all parts need to work in a coherent manner to function seamlessly and give you the best experience; in both the restaurant and SQL Clone case.

After you have both SQL Clone Server and Agent in place, you can now start taking SQL Clone data images.



Once the data imaging operation has completed, you can produce clones from that data image.



At any point, you can jump to the SQL Clone dashboard view to see your data images and clones associated to those images.



Provisioning test and development environments with large databases can be a painful and time-consuming task. SQL Clone exploits built-in disk virtualization technologies within Windows in order to create multiple, isolated clones of the original data images, each of which is a fraction of the size of the original source database or backup, but works just like any other normal database.

It has the potential to reduce to minutes the time taken to provision environments, makes it feasible for each developer to work in an isolated environment, performing testing against realistic data, and has many other possible uses.

Download the beta now and start creating database copies in seconds. Interested in finding out more? Visit the product page or take a look at the documentation prior to the release.

Tools in this post

SQL Clone

Create SQL Server database copies in an instant

Find out more

You may also like

  • Article

    A Database Developer's Guide to SQL Clone

    Phil Factor explains how SQL Clone works, and its multiple benefits when used in daily development and testing work, as well as for other tasks such as training staff to use a new application.

  • Webinar

    How to keep your delivery processes secure with Database DevOps

    Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.

  • Webinar

    How to boost team productivity with SQL Clone 4

    SQL Clone 4 introduces Teams, allowing you to have more control over the instances, images and clones that users have access to, enabling safe self-service and delegation for DevOps and CI.

  • Article

    Team-based database development with SQL Clone

    For most development teams, the database provisioning process involves some element of compromise. Either the process is slow, but the database is realistic or the process is fast but the database unrealistic. Chris Hurley explains why SQL Clone can allow the team to develop and test with a real database, without the compromises.

  • Article

    Easing the transition from shared to dedicated database development

    Working in dedicated development environments for the database is the ideal for many. This is the message we frequently hear throughout the industry from thought leaders, at conferences, and in many written pieces about implementing best practices. Yet 70% of developers still work in shared environments, as highlighted in the 2020 State of Database DevOps

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant