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:

 Conclusions

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.

References

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

    Global Data Privacy Tales: The GDPR Effect

    Our expert global panel will be discussing what’s happened worldwide since the GDPR came into force one year ago. Discover how legislation has changed around the world, which companies have been caught out, who’s been fined, and what’s likely to come up in the year ahead. 

  • Article

    Baselining a SQL Change Automation project from an existing database

    This article show how to create a 'baseline' for your Visual Studio SQL Change Automation project, from an existing, target SQL Server database, so that the team can start making changes and easily deploy them to the target database.

  • Webinar

    Addressing Global Data Privacy at PASS 

    This case study session will discuss how the IT team at PASS introduced compliant database DevOps in order to be compliant with the GDPR as well as upcoming legislation in the USA like the California Consumer Privacy Act (CCPA). 

  • Article

    Deploying Multiple Databases from Source Control using SQL Change Automation

    How to automatically build multiple test databases, from source control, and then fill them with standard test data sets, using SQL Change Automation, BCP and some PowerShell magic.

  • Article

    The Whys and Wherefores of Untrusted or Disabled Constraints

    Having untrusted or disabled FOREIGN KEY or CHECK constraints in your databases will degrade data consistency and integrity and can cause query performance problems. Phil Factor explains how to detect these and other table-related issues, during development, before they cause trouble further down the line.

  • University

    Take the SQL Change Automation course

    In this course, you’ll learn how to use SQL Change Automation to capture database changes in migration scripts that can be customized and used for deployments. This gives you full control over the deployment. You’ll also learn how to use these migration scripts as part of an automated CI/CD pipeline.

    If you’re interested in learning more about automating database changes with the state-based approach, then see the SQL Change Automation with SQL Source Control course.