13 December 2018
13 December 2018

Database Continuous Integration with SQL Clone and SQL Change Automation

Phil Factor provides the basis for a Database Continuous Integration process, using SQL Change Automation to build the latest database, and then SQL Clone to distribute it to the various team-based servers that need it. Having honed the process, you can run it every time someone commits a database change.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

When you are working as part of an application development team, it is worth settling down into a routine of doing a daily build from source control, and then provisioning test and development instances of the database. One major advantage is that the code and database can be scrutinized as early as possible, and you can prove that the working database can be built entirely from the code. This means that management can be confident that the assets are all in source control. It also means that tests can pick up problems as early as possible.

This requires automation. The most difficult part is in assembling individual object scripts in dependency order or, in the case of a migration approach, in the order dictated by the manifest. The most tedious part is in loading the data into the database. Of course, the time-consuming part is preparing the data, but this is out of scope for this article. We will assume that you have a standard dataset for testing, and all servers will share it.

I use SQL Change Automation (SCA) to build the database. Microsoft’s SMO has a minor integration role, and I also use it to create the database. I use SQL Clone, a component of SQL Provision, to distribute the finished database to the various team-based servers who require the latest build. Once you’ve honed the process to your requirements, you can run it every time someone commits a database change.

A brief introduction to SQL Clone

There is always a slight culture shock when tackling SQL Clone for the first time, so I’ll explain briefly (see How Clone Works for more). SQL Clone uses the Virtual Disk Service in x64 Windows to allow the same bytes (the ‘data image’) to be reused as ‘clone’ databases, as many times and on as many SQL Server instances as you need. SQL Server is entirely unaware that the Windows Operating system is engaging in any ‘smoke and mirrors’. The data image is held only once, in a single location on the network, and never changes. Any changes that are made on each clone are stored in a differencing disk on the local clone server.

It takes little time to set up a clone, which is the time it takes to set up the Virtual Hard Disk (.vhdx) and mount it. Whatever the size of the actual database, the initial clone created from this image requires only about 40MB of disk space.

You’ll need one central server, the SQL Clone Server, on which to install the SQL Clone web application, which must be visible over the network to all other machines. Your servers don’t need to be part of a Windows Domain to run SQL Clone, but if you are running in a mixed development environment, the simplest approach is to provide the shared directory, for the images, on the same machine as the Clone Server. The clones can go on any SQL Server where the user assigned to the Clone Agent has network access to that shared image directory.

Any instance of SQL Server in the network can be used to host clones. To do this, they need SQL Clone agents installed on them that are assigned a local User with sysadmin rights.

Once a database starts to require a lot of data for development and testing work, the chore of provisioning all the necessary database instances with the current version of the database and data can get out-of-hand. You can do it via schema synchronization if you can be certain that the clones have the correct data, but this simply isn’t realistic. Any test run is bound to make data changes. To do a series of integration tests, you need to restore the database to a known state after each test. Traditionally, this was done by backup/restore or repeated detach and attach.

Instead, I’ll use SQL Clone, which makes it very quick and easy to delete and reinstate the clone as part of the tear-down, and this process can be easily automated in one line of PowerShell.

PowerShell-automated CI

The PowerShell script I present next will allow you to maintain the provision the various databases required for test and development with the latest version of the schema, from source control, and one or more versions of data for test or development. I’ve provided an AdventureBuild zip file that contains both the source of the AdventureWorks database and the data we need for development and testing (as bcp files). If you’re starting from a dataset that contains sensitive or personal data, you’ll need to mask the data, before provisioning the development and tests servers.

I am assuming that the origin database in this case is a database being developed, and with its source kept in Source Control, so I don’t backup existing clones, or save changes before deleting clones, and I don’t check to see if anyone still has a session running with the clone.

The script uses SCA to do the build (but you could also use SQL Compare for this), by sync’ing to an empty database, created using SMO. You’ll want to build the database on an instance running a SQL Server version that is equal to or lower than the oldest version to which you need to deploy clones. A clone can’t be made to run on a version of SQL Server that is older than its parent.

We use BCP to import the data into the empty build, and then SQL Clone to create an image of the database and deploy clones to a list of ‘clone’ databases on one or more servers. The clones will either be created, or existing clones refreshed, with copies of the database as built by the build process. This should end up with all databases with identical metadata and data. You’ll need to have the latest versions of the Clone PowerShell cmdlets installed (Settings | PowerShell, in the Clone application).

If you have SCA but no SQL Clone, you can still do this, but it will be much slower and it will take more space. I’ve shown how to do this in two previous articles, one using BCP, and the other using JSON.

The solution omits some of the finer details, most of which you can find in other articles. For example, I don’t generate a documentation website for the database, run a check on the code with SQL Code guard or send out information about the progress of the build. I don’t show how you can apply scripts and templates to each individual clone to customize the installation, or to make the database conform with the server’s settings.

The config file

To keep things simple, I’ve put all the settings in a separate script. This contains a list of clones to create or update, the details of the database that must be built, where the data and source code is to be found, and the location of the Clone server and shared image directory. Obviously, you’ll need to fill it all in.

This needs to be in the same directory as the following PowerShell script.

The CI script

Here’s the PowerShell script to build and fill the database and deploy the clones:


There are a remarkable number of ways of doing automated CI with SQL Server databases. This is just as well, considering the rich variety of team methodologies. For me, the great advantage of SQL Clone is not just the ease of automation and the saving of disk space, but the ease of jettisoning a database you are testing and reverting to the build version. The larger the size of the database, the greater the advantage of being able to do this.

This script that I’ve shown here is to illustrate the possibilities. There are many things that you can add such as data generation, script checking, and data masking, but the basic facilities of SQL Clone are certainly a big help in providing Continuous Integration for the larger team and database.


Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Webinar

    Keep sensitive data secure as it moves through your SQL Server estate

    In this webinar, Chris Unwin, will show you how to implement a consistent and repeatable process designed with privacy in mind up front. You will learn how to create an accurate picture of your SQL Server estate and steps you can take to ensure the ongoing protection of sensitive data.

  • Article

    Protecting production data in non-production environments

    Grant Fritchey discusses the need to ‘shift left’ the database and associated database testing, while keeping sensitive data secure when it is outside the production environment, and how SQL Provision can help you achieve this.

  • Event

    WinOps London 2018

    The world’s only dedicated conference to ‘DevOps in a Windows World’. The conference is about discovering and sharing experiences of using products and tools within the Microsoft DevOps world such as: PowerShell, TeamCity, Octopus Deploy, Azure, Vagrant, Chocolatey, AppDynamics, ScriptRock, Chef, Puppet, Ansible, Docker etc… Register for the event and meet the Redgate team.

  • Event

    Microsoft Future Decoded

    Microsoft Future Decoded is your essential guide to disruptive technology and how it will impact people and businesses, today and tomorrow. Back for a fourth year, Microsoft Future Decoded will focus on current and emerging trends at the cross over of business and technology. With cloud, big data and artificial intelligence already rewriting the rules

  • Article

    SQL Provision adds fully integrated data masking

    Karis Brummit announces SQL Provision, which combines SQL Clone's fast, lightweight database copying and centralized management of provisioning, with Data Maskers's ability to obfuscate sensitive or personal data, prior to distribution.

  • University

    Take the SQL Change Automation course

    In the SQL Source Control course, you can learn how to capture database changes, add them to a source control repository and make manual deployments.

    This course will take you a step further and show you how to use SQL Change Automation alongside SQL Source Control to introduce automation into your pipeline.

    SQL Source Control uses a state-based approach. If you’re interested in learning more about a migrations approach, then see the SQL Change Automation with Migrations course.