Product articles SQL Clone Database Builds and Deployments
Database Continuous Integration with…

13 December 2018

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.

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.


You may also like

  • Article

    Practical steps for end-to-end data protection

    If you plan to make production data available for development and test purposes, you'll need to understand which columns contain personal or sensitive data, create a data catalog to record those decisions, devise and implement a data masking, and then provision the sanitized database copies. Richard Macaskill show how to automate as much of this process as possible.

  • Webinar

    How to cut time & disk space for database provisioning by 99%

    In this webinar, you’ll see how SQL Clone cuts out the pain of provisioning development databases, regardless of which database development model you use.

  • Webinar

    Data Masking: Insights & Actions

    In this 30-minute webinar, Microsoft Data Platform MVP Kendra Little explores the key challenges and recommendations to prevent exposure of private data in your next data breach, featuring insight from Gartner's 2018 Market Guide for Data Masking. GARTNER is a registered trademark and service mark of Gartner, Inc. and/or its affiliates in the U.S. and internationally, and is used herein with permission. All rights reserved.

  • Article

    Simple SQL Change Automation Scripting: The Release Object

    Once you understand the SCA data objects, it can give you a certain glow of to discover, suddenly, that SCA can do some complicated and time-consuming tasks with just a few lines of code. Phil Factor demonstrates how to get the most of SCA's Release object.

  • Article

    You just Build It don't you? 12 Common Database Build Blockers

    Database deployments, like the sheep of exasperated hill-farmers, often find strange and unexpected ways to self-destruct. Phil Factor describes the most common things that can go wrong, and how a reliable automated database build process can prevent messy accidents.