• SQL Provision
• Development and Testing with Clones

Scripting Custom SQL Server Clones for Database Development and Testing

A clone database doesn’t have to end up being identical to the source. To make changes, you have the choice of modifying the image, the individual clones, or both. If the alterations take time and they apply to all clones, then it makes sense to alter the image. You can alter the image after it is created, before it is used to create clones, by temporarily mounting it on a SQL server and executing scripts against it. Obviously, all the changes you make are applied to every clone.

It is also likely that you’ll need to automate certain, additional scripted changes to each clone, either during clone creation, or once the clone is in place. These scripts might modify the database, or database configuration settings, or make changes to server-level security, or other server configuration settings.

I’ll demonstrate how to customize one, several or all clones using SQL Clone’s built-in image and clone T-SQL modification capabilities. These changes can be applied entirely by SQL Clone, either via scripting, using the SQL Clone Powershell cmdlets, or by the GUI. If you need to make changes that involve the SQL Server instance as well, then you’ll need to run these additional modifications scripts independently, via PowerShell.

PowerShell Scripting extends the usefulness of SQL Clone greatly, because it allows the DevOps team to provision development and test database as closely as possible to what is required.

Customizing Clones

As a scripter who is providing development and test databases for a team, there are several reasons why you might need to customize a clone, every clone, before the development team start working with them. There are three ways to do it, depending on requirements:

1. Modify the image using one or more modification scripts – useful for changes that all clones need, before use, such as to apply data masking. The image modification scripts are run by a temporary user with limited privileges, who can modify the clone database as db_owner, and so can perform all configuration and maintenance activities on the database but nothing else.
2. Modify one or more clones using a clone template – useful for creating ‘bespoke’ clones, such as by applying a migration script that allows a developer to work on a special variant, or branch, version of the database. Also, where the development team is working to the security policy principle of ‘least privileges’, then use of clone templates is the way to set up access control for the existing development server logins, to give them the required database role membership, or user access,to the fresh clone. As for the image template, a clone template can alter only the clone database, not the server.
3. Modify one or more clones using PowerShell scripting – clones may have general requirements that, because they involve the SQL server instance hosting the clone, cannot be met using either image modification scripts or clone templates. For example, any database application usually has scheduled jobs and alerts associated with it, which aren’t part of the cloned database, but which will need to set up and tested.

Scripting a solution

We can do all these extra modifications with the PowerShell script that I presented in the article Deploying and Reverting Clones for Database Development and Testing, and by modifying its associated configuration file. 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.

Using the image modification scripts

Not all images come from a clean build. To deal with some of the problems of images taken from other sources, even a production setting, SQL Clone allows the image to be modified, after the image is taken but before it is cloned, as would be necessary if it contained inappropriate users and permissions, if there was data that was neither masked nor anonymized, or if the image was taken from a context, such as a replication group, that wasn’t appropriate for the clones.

This is best done with one or more image modification scripts, one or more of which can be applied to an image once it has been taken from the original database or backup, as part of the creation process. These are just SQL scripts that can modify the image. SQL Clone does this by attaching the image temporarily to the SQL Server instance from which it originated, or from a temporary instance you specify (if you’re starting from a backup). The SQL Clone Agent executes the scripts via a temporary dbo login and user, which also has VIEW ANY DEFINITION and VIEW SERVER STATE permissions on the server.

Here, we’re going to use an image modification script simply to add an extended property to the image, which will allow us to see quickly that the script was successfully run, and to identify each of the clones in SQL and see its associated image.

Fortunately, SQL Clone (version 2.6.2 onwards) creates some variables for each batch that it executes (one per file) and we can use these values. They are:

• @SQLClone_ImageName – the name of the image being created
• @SQLClone_MachineName – the name of the machine hosting the agent that is creating the image
• @SQLClone_UserName – the username of the user who created the image, ‘DOMAIN\Username’
• @SQLClone_OriginDatabaseName – the name of the database from which the image was created (e.g. ‘Production’)

Listing 1 shows the SQL for an image modification script that created a SQL_Clone_Image_Info extended property. We chose to write an extended property into the database because it is useful for testing; these extended properties can be viewed very easily in SSMS to verify that the PowerShell script worked.

Listing 1: Image modifications: adding the SQL_Clone_Image_Info extended property

We can save this batch in a file and then pass it to SQL Clone using PowerShell. I call it imageModificationScript.sql for this example but you may, of course, call it what you will.

To use this as part of or automated process for deploying and reverting (rolling back) clones, we just add a line to the image data in CloneConfig.ps1, to specify the location of the SQL script file that modifies the image. The aim is to do all our configuration of the clone-creation process without touching the code at all; just the configuration data.

Listing 2: Specifying the image modification scripts in the configuration file

Note that the image can have several scripts applied in sequence, so the 'Modifications' section of the definition is an array of script paths, rather than a value.

Although the addition of an image modification script simply requires an extra parameter in the configuration, it makes the process significantly longer, because the image must be recreated as a database in order to make the modifications. This may not matter for an overnight provisioning process, but it could prevent this method being used with a huge image.

Each clone, when it is created, now has an extended property giving some details about its creation. The extended property values are stored as a JSON document that can be parsed for each key/value pair.

Clone modifications using clone templates

SQL Clone templates contain a series of T-SQL scripts and that will be run on a clone just after it is created. These are the most obvious method of modifying a clone, because they are unobtrusive and can be done using the GUI as well as in PowerShell.

As with T-SQL image modifications, the T-SQL clone modifications that form the template will be run by the SQL Clone Agent with a temporary user that has database ownership, but no access to the server. These scripts can do all configuration and maintenance activities within the database, and can create, alter or delete objects within the database or to make other database changes. They can’t change database settings or add jobs or alerts to the SQL Server Agent, and nor can they change server security, and so on.

These scripts are added to a template, which is assigned to an image. You create one or more templates for an image, and each template consists of one of more SQL files. You can choose which of these modification “templates” to use when you create a clone, but any one clone can have only one ‘template’ applied to it.

SQL Clone will concatenate the files in a template into a single script of one or more batches, in the order you specify them, and run it, while maintaining the Clone local variables(such as @SQLClone_CloneName) across batches. Because there is only one template, it means that you can’t change the order in which the component files run, for individual clones, and nor can you specify that it should run files 1, 3 and 4 but not 2, for a clone.

The advantage of using a clone template over just making a connection to the clone and executing a script is that in the GUI, the images and clones show that they have had templates applied to them, and they can be administered entirely within the GUI, if you don’t need PowerShell automation. Also, templates are automatically reapplied when resetting a clone back to its original state, whereas with scripts you need to run them again manually.

The following SQL script for our clone template merely adds another Extended property, called SQL_Clone_Clone_Info, to an individual clone. We use it to prove that the script ran and that the SQL Clone template has the variables @SQLClone_CloneName, @SQLClone_MachineName and @SQLClone_UserName set. Before the template is run, SQL Clone defines these special variables at the start of each batch, in the same way as it does for image modification scripts.

Listing 3: Clone templates: adding the SQL_Clone_Clone_Info extended property

In my case, I’ve saved this as a file called cloneModificationScript.sql. We want to execute this on just one clone, but we could equally easily run it on all if we specify it on all.

SQL Clone scripts are associated with the image rather than the clone, because they can be applied to one or more clones. This means that they are usually attached when the image is created and then referenced by name at the time that the clone is created. We therefore need to provide a list of key/value pairs providing both the name of the template, in this case DatabaseProperties, and the path to the clone modification script. We specify them in the configuration file like this, within the definition of the image (I’ve only created one template here).

Listing 4: Associating a clone template with an image, in the configuration file

When we create a clone, we check to see if a clone template is associated with it in the config file and apply it accordingly. You therefore need to make a second edit to the config file to specify the template to apply to a clone Just go to the list of clones and choose the template you want to use, by giving its name as the value to the key 'Modifications'.

Listing 5: Specifying the template to apply to a clone

When we run it, we can see that the appropriate clone is modified.

Modifying the Server environment for the clones

The clones may have general requirements that, because they involve the server, cannot be met by modifying the image or by using clone templates.

We have already shown how to deal with this in Listing 2 of Deploying and Reverting Clones for Database Development and Testing though it won’t be entirely obvious. It was originally a by-product of having to do it in order to set the database we were using for reference, the ‘original’ clone, to be read-only. This can’t be done with a clone template

Although, we can’t do server-level changes from within clone, we can do them in a PowerShell script. Fortunately, the PowerShell script from the previous article already has to create a connection to SQL Server in order to make the reference clone read-only, so it is easy to add extra scripts.

These extra scripts are specified as a list of files to execute as SQL Scripts, under the same user as runs the script. This is done just after the clone is created. Naturally, your real server-level scripts will perform tasks such as setting up Agent jobs, configuring security and so on.

Here, however, we just want to confirm that the script ran without error, so we’ll continue our theme of writing to the extended properties of the clone database, this time creating an Extra_Info extended property that collects some server information, about the current login, name of the database and so on.

Listing 6: Server modifications: adding the Extra_Info extended property

In this case, I save this as a file called ServerModificationScript.sql. In the config file, you specify the server modification scripts to run simply by adding the key named “AdditionalScripts” to the appropriate clones.

Listing 7: Specifying the server modifications to apply during clone deployment

The script checks to see if you’ve specified one or more scripts and, if so, it uses a similar connection to the one that is used to make a clone read-only.

And now, for one clone, three scripts have left their mark.

Conclusions

One of the most amusing and useful things that happens when you run a PowerShell script, using the SQL Clone PowerShell cmdlets, is that the SQL Clone GUI follows what you are doing as you do it and reflects the state of the clone setup. As images and clones are created or deleted, so the screen updates with the information. As the clones are created, their state changes on-screen. If you run a template, the clone is labelled appropriately with the name of the template. It is whilst watching all this that it becomes obvious that scripts should be done wherever possible using templates, and external scripts only used when it is impossible to do the work with a template.

By using scripts that take their data from a shared data file, you ensure that when clones are recreated repeatedly, they are always done in the same way. For me, the greatest value is in extending the range of what is possible in the test cell. The most uncomfortable thought is that, now I have SQL Clone, I no longer have the excuse that we don’t have the time or resources to do the full range of tests in the limited time allowed by the Continuous Integration process.

Tools in this post

SQL Clone

Create SQL Server database copies in an instant

Find out more

SQL Provision

Provision virtualized clones of databases in seconds, with sensitive data shielded

Find out more