Phil Factor

6 March 2019

• SQL Clone
• Development and Testing with Clones
• Database Testing

Safely Deleting Clones and Images during Database Development and Testing

This is the third article in a series that explains how to use SQL Clone, part of SQL Provision, plus a collection of PowerShell scripts, all with a shared configuration data file, to deploy, revert, customize, delete and refresh clones, for database development and testing work.

The first article, Deploying and Reverting Clones for Database Development and Testing, mapped out a Clone installation consisting of a source database, an image and some clones. It presented a shared configuration data file and then a PowerShell clone installation script that used this config file to create a suite of clones, for testing or development work. A second PowerShell script showed how to revert, or roll back, a clone to its original state, ready for the next set of tests or development work to begin, first ensuring, if necessary, that any changes made since the clone was created were saved to source control.

The second article, Scripting Custom SQL Server Clones for Database Development and Testing, showed how one could apply T-SQL modification scripts during image and clone creation, to customize all clones, or individual clones, prior to use. Simply by adapting the data in the config file, we could use Image modification scripts to alter the image before the clones are taken from it, and both Clone templates and SQL Scripts to check and alter the individual clones. These scripted modifications would then run automatically as part of installing the clones, without any need to touch the code in the installation script.

Here, I present a PowerShell script that you can use to safely delete all clones, and then the parent image, in readiness for refreshing all development and test instances with the latest version of the database. As for the rollback process, this script aims to manage the deletion process to ensure that work doesn’t get lost. By combining the deletion script with the installation script, you can, in effect, refresh all clones when the image is updated, to reflect changes in the original database. The scripts, taken together are intended to manage a typical provisioning cycle to keep the clones in sync with the current version of the database.

Updating clones to the latest build

In testing or development, it is important to be sure of working with a known version of the database, usually the latest build. With SQL Clone, you take an image from the database that represents that build, plus data, and clone identical copies of it. Whenever the version changes, these clones need to be migrated to the new version. When you are working with a cell of test and development servers, this can present a challenge.

You can certainly apply a synchronization script in order to bring each clone up to the latest version, although this isn’t always practical, because each clone could be in a different state, and so each clone would require its own synchronization script. Also, because the changes will be held on a differencing disk on local storage on the server, you will gradually lose one of the great advantages of SQL Clone, which the huge saving in disk space. Also, in doing so, you would be getting around a problem that has ceased to exist, which is the length of time it takes to copy a database. With SQL Clone, it is a matter of seconds.

When working with clones, it is much better, once a new build has been successfully made, stocked with appropriate data and tested, to delete all the clones and then the image that they used. Once that is done, then you can create the new image from the successful build, with the same name, and recreate the new clones under their previous names. As a refinement of this, with a large development or test database, you can create the new image first, under a different name, before dropping the old clones and recreating them with the new image, and then finally dropping the old image.

Deleting the old clones and parent image is, in fact, a very simple process, in either the GUI or in PowerShell, using the Remove-SqlClone and Remove-SqlCloneImage cmdlets.

Is this likely to meet your needs? If so, read no further.

However, the main problem for developers is that it is all too easy to do some scripting on a clone, such as creating a routine or redesigning some tables, and forget to save the work to source control. Worse, someone might still be using their local clone, when suddenly it disappears! Yes, it is perfectly possible for the clone to be deleted while there are active users on the database, unless you check first to see when the last read or write took place, or some other measure of activity. The deletion of the image is also remarkably easy once the clones are deleted, but this is less likely to cause collateral damage.

This means that, to be safe, we’ll probably want to run some checks before the clones are deleted. The Remove-SqlClone Cmdlet does not support the use of clone templates, so we can’t run any SQL queries using these cmdlets, or through the GUI, before a clone is destroyed.

This is fine if you feel lucky, or don’t have tight timescales for test-runs. However, I’ll show how to get around these problems, and delete clones safely.

Safety checks before deleting the old clones and image

Deleting clones can require additional SQL scripts to be executed, such as exporting the results of automated tests. You can specify these in the config file as a BeforeDeleteScript. You might also require the use of an external tool to save off any recent schema changes. As an example, we’ll use SQL Compare to save all the differences between the current clone and the image. We can’t compare directly to the image and wouldn’t want to. Instead, we create a ‘reference’ clone, which I refer to as the ‘original’, set it to read-only and, compare to the original, and save the resulting synchronization script. This is the same technique as I used in the script to revert clones to their original state, as described in the first article.

Here, we also need to find out if there is anyone who is still actively using a clone. It is no use just checking for open SPIDs; we need to find out how recently they’ve used the system, which we can do using a query like this (you need to provide the name of the database of course!):

We run this query from the RemoveClonesandImage PowerShell script (presented shortly), and terminate that script, after the initial checks, if the result returned by the query indicates that there was recent activity in any of the clones. I use the last forty minutes as the threshold, but you can change this in the PowerShell script.

The RemoveClonesandImage Script

This script is used where your requirement is for a regular process that can automate more complex provisioning tasks. If you prefer just a simple deletion process via basic PowerShell scripts, then look at the SQL Clone documentation, which has several examples.

This script uses the same PowerShell configuration data file as we’ve used in the previous two articles. This will allow you to opt clones out of the ‘backstop comparison process’ that uses SQL Compare to save any potentially-unsaved work. The structure of the config file is as follows:

You can access the RemoveClonesandImage.ps1 script, shown below, and all the other 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.

Re-creating the new image and clones

Having deleted all the clones, ensuring not to disrupt ongoing development work nor lose any unsaved changes, the next part of the process is to use the take an up-to-date image of the latest build, and create the batch of fresh clones, using the CreateOrRenew.ps1 script (described here).

It is very likely that each new clone will need to be customized for the needs and access control requirements of the user of that clone, so the use of a shared configuration file ensures that these differences are maintained when the new clone is created. Every time you run this ‘refresh’ process, the CreateOrRenew script will use the image modifications and clone templates specified in the configuration data file, as described here.

When you need to revert, or rollback, a clone to its origin state, such as after a test run that affected the schema or data, you can use the RollbackClone.ps1 script, and it will reapply any existing clone templates.

Conclusions

SQL Clone is easily managed via the GUI that is provided, and the features of the PowerShell scripting are generally kept in sync, so that whatever you can do in the GUI is also possible in PowerShell. This means that any routine process can be automated easily, once it has settled down.

The method of producing a series of scripts that use a shared data structure, rather then using a procedural approach based on functions, can be rather a culture shock for an application developer. From my own work with Ops people, and when I was in an Ops team, I found that it was the preferred approach, which is why the arts of Bash and DOS scripting survives in a healthy state to this day. It means that a script can be scheduled and (hopefully) left, allowing the team to make any changes purely from the data structure, and by checking logs.

There is a great deal of choice in the way that all this is automated in PowerShell, and here I’m illustrating just one approach, but it should be easy to take what I’ve done here as an example or a starting point.