SQL Clone is a very handy device for database developers. The main thing stopping me from using it more widely, initially, was culture shock. It is a new and strange experience to be able to have several local copies of the database I’m developing, without worrying about disk space, and without having to fuss about keeping them up to date. This means that I can create a series of cloned databases, and then migrate each one to a different version, without requiring enormous amounts of storage.
Even more of a shock is being able to make radical changes to the data or schema while testing, in the knowledge that it takes only a few seconds to revert the database back to its original state, ready for the next test run. I’ll show how to use SQL Clone, the database provisioning component of SQL Provision, to achieve all this. With a bit of additional scripting, you can even revert a copy of the database without worrying too much about losing any schema changes, by using SQL Compare to compare the altered database with an unadulterated clone, and save any changes as scripts, in files.
With these various tricks, SQL Clone changes the whole chore of testing into something that is almost akin to a pleasure. In fact, more testing becomes possible, within the tight schedules we now experience.
Like most developers, I hate doing a chore more than once, so I script as many developer tasks as possible. Although SQL Clone has a console, accessible via a browser, I prefer to run via a script. In this article, I’ll provide two scripts, which will cover the chores of:
- Provisioning as many development databases as I want from a functioning database build that has data.
- Reverting, or resetting, a clone database to which I’ve made changes, returning it to its original state.
These two scripts are designed to get you started. You can turn them into parameterized functions or use them as templates. You can access all the scripts for this series of articles about working with clones for development and testing work, at my GitHub repository: https://github.com/Phil-Factor/SQLCloneFamily.
As you can imagine, there are plenty more tasks you might want to script. After you’ve made and tested your changes, you might want to revert all the clones, or simply delete all clones, as well as the current image. You might want to apply a migration script to each clone in order to test out all variants of a particular version of a database (e.g. different language, nationality, legislative area or culture). I’ll cover some of these in subsequent articles.
Defining the development setup
I’m assuming you’re already familiar with the basics of SQL Clone, and how to create images and clones. If not, check out the Redgate Product Learning articles. Here’s the setup I used for this article:
Before you attempt any scripting with SQL Clone, make sure that everything is properly set up and running from the console.
The Clone Configuration file
The sort of work that benefits from scripting are those processes that are rather repetitive with very little variation. To manage them, I’ve put all the static details into a data structure, a configuration file, which is shared by all the other scripts. This config file, called CloneConfig.ps1, gives all the necessary details of the topology of the development environment for the imaging and cloning process. it’s structure is as follows:
This means that the scripts in this series are slightly unusual in that you get the them to do different things, or manage different clones or images, by changing the data rather than using different parameters to a function. If you want to make a change to a clone by applying a series of scripts, you merely list the requiter scripts in the data: you shouldn’t need to touch the code. You change the location of clones or the number of clones just by changing the structure. For production work, this means that you require few if any code changes, just change the data.
Everything is scripted in the PowerShell data language, which is executable by PowerShell. A sample version is provided here:
Naturally, you just change it to match your topology.
Creating the image and its clones
When we run this next script, it creates an image of the source database, and then rolls out all your clones. You will notice that one of the clones is designated to be the ‘original’. It is a bad name, but what I mean is that one clone is read-only, and so provides a comparison point to find out the extent of any changes in the other clones. You need this both in testing and in development. In testing, you need to know of all data changes after a test run, and in development we use it as a ‘long stop’ to make sure we’ve saved any changes to the schema, before reverting or dropping the clones. It is all too easy to get distracted and forget to save your work. This script therefore sets this ‘original’ clone as read-only as soon as it has been created.
With this script for making new clones we have the basic. When we want to reset the image to the latest build, we’ll first need to delete all existing clones, and then the current image, and for the time being this step is still manually. To automate this, we will need a separate article and script because we need to do it carefully.
We’d want to ensure that we’ve checked every clone for changes, and saved those changes, before deleting it, just in case the developer left work suddenly for the day without saving the work into Source Control. Well, I’d want to ensure this, anyway, because I’m a kindly, avuncular, sort of person.
Resetting the clones
For our next trick, we will do a tear-down. By this, I mean reverting a clone to the state it was in when first created. I use this for running destructive tests on databases. Most integration tests are destructive, because they change the state of the data, and sometimes even the schema. This routine deals with all this. It is like the database version of a hot pressure wash.
Before deleting a clone, and creating a fresh one from the current image, I’ll use SQL Compare to compare to the clone I’m going to tear down to the original, read-only clone, and generate a synchronization script, which it will save in the work folder. This means that developers who have forgotten to save off any changes to source control can simply access the fresh clone the next day and then run the synchronization script to recover their changes. With SQL Data Compare, you can do the same thing for data, but I haven’t shown that.
The ‘clone reset’ script uses the same configuration data file as before (from Listing 1). We place in the beginning of the script the name of the database name and server of the clone that we want to revert. I usually have this loaded in the PowerShell ISE and run it whenever I want to revert the database; but if a ‘tear-down’ reset were part of a test script, it would need to be run automatically so it might then be better to have the reset information in a separate file.
Once one gets over the diffuse anxiety surrounding any unfamiliar technology, SQL Clone opens plenty of opportunities. For development work, it means I can contemplate the sort of integration tests and regression test tasks that I generally recoil from because of the tedium and hassle. I have spent too many years with install-disks in hand, staring disconsolately out of server room windows at the evening sky, as red flashing lights in racks signal more tedious waiting. We now seem to have a better way of testing databases!
I can set up structured tests in parallel, run them and clean up afterwards ready for the next test run, with the confidence of knowing that the data is in the same state. Not only that, but one can use test servers that don’t require the usual huge disk capacity, because the bulk of the data is on a nice, fast file share, on a nice fast network.
SQL Clone is easily scripted in PowerShell, so automated integration tests, as part of a CI process, should never hold any terror. In this article, I’ve shown how easy it is to get started with creating and reverting clones. Hopefully it is a kick-start into automating away some of the more tedious aspects of development work.
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.