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 databases 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 starts working with them. There are three ways to do it, depending on the requirements:

1. Modify the image using one or more modification scripts — This is 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 can perform all configuration and maintenance activities on the database, but nothing else.
2. Modify one or more clones using a clone template — This is useful for creating "bespoke" clones, such as by applying a migration script that allows a developer to work on a special variant, branch, or version of the database. Also, where the development team is working to the security policy principle of "least privileges," then the 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.

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 and 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 VIEWANYDEFINITION and VIEWSERVERSTATE 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:

DECLARE @CloneInfo NVARCHAR(3750)
  SELECT @CloneInfo =
    (
    SELECT @SQLClone_ImageName AS "Name", @SQLClone_MachineName AS "MachineName",
      @SQLClone_UserName AS "User", GetDate() AS "Modified",
      @SQLClone_OriginDatabaseName AS "DatabaseName"
    FOR JSON PATH
    );
  IF not EXISTS
    (SELECT name, value  FROM fn_listextendedproperty(
       N'SQL_Clone_Image_Info',default, default, default, default, default, default) )
      EXEC sys.sp_addextendedproperty @name=N' SQL_Clone_Image_Info', @value=@CloneInfo
  ELSE
    EXEC sys.sp_Updateextendedproperty  @name=N'SQL_Clone_Image_Info', @value=@CloneInfo

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 our 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:

        "Image" = @{# this has the details of the image that each clone uses as its base
          #we use these details to create an image of what we built
          'Name' = "$($database)image"; #This is the name we want to call the image 
          'Modifications' = @("$($env:USERPROFILE)\Clone\imageModificationScript.sql") 
          'ServerURL' = 'http://MySQLCloneServer:14145'; #the HTTP address of the Clone Server
          'ImageDirectoryURL'='\\MySQLCloneServer\Clone' #the URL of the image directory
      }

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 from 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 that will be run on a clone just after it is created. These are the most obvious methods 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 make other database changes. They can't change database settings or add jobs or alerts to the SQL Server Agent, nor can they change server security, etc.

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 or 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.

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:

Declare @CloneInfo NVARCHAR(3750) 
  SELECT @CloneInfo = (
    SELECT * FROM (Select
      @SQLClone_CloneName, 
      @SQLClone_MachineName, 
      @SQLClone_UserName,
      GetDate()
    )f(CloneName, MachineName, UserName, Created) FOR JSON auto );
  IF not EXISTS
    (SELECT name, value  FROM fn_listextendedproperty(
       N'SQL_Clone_clone_Info',default, default, default, default, default, default) )
      EXEC sys.sp_addextendedproperty @name=N'SQL_Clone_clone_Info', @value=@CloneInfo
  ELSE
    EXEC sys.sp_Updateextendedproperty  @name=N'SQL_Clone_clone_Info', @value=@CloneInfo

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 and 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:

     "Image" = @{# this has the details of the image that each clone uses as its base
          #we use these details to create an image of what we built
          'Name' = "$($database)image"; #This is the name we want to call the image 
          'Modifications' = @("$($env:USERPROFILE)\Clone\imageModificationScript.sql")
          'ServerURL' = 'http://MySQLCloneServer:14145'; #the HTTP address of the Clone Server
          'ImageDirectoryURL'='\\MyImageServer\Clone' #the URL of the image directory
          'CloneTemplates'=@{
                              'DatabaseProperties'="$($env:USERPROFILE)\Clone\CloneModificationScript.sql"
                              }

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. Therefore, you 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

  "Clones" = @(
      @{
        "NetName" = " MySQLCloneServer ";#the network name of the server
        'Modifications' = 'DatabaseProperties'; #the name of the template to run
        "Database" = "$($database)Ours";#the name of the Database
        'username' = 'MyId';#leave this blank for windows security
        'Original' = $false}#is this the original

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 the 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 that 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:

Declare @CloneInfo NVARCHAR(3750) 
  SELECT @CloneInfo = (
    SELECT * FROM (Select
      SYSTEM_USER, 
      db_name(), 
      App_Name(),
      GetDate()
    )f(SystemUser, [Database], [Application], Created) FOR JSON auto );
  IF not EXISTS
    (SELECT name, value  FROM fn_listextendedproperty(
       N'Extra_Info',default, default, default, default, default, default) )
      EXEC sys.sp_addextendedproperty @name=N'Extra_Info', @value=@CloneInfo
  ELSE
    EXEC sys.sp_Updateextendedproperty  @name=N'Extra_Info', @value=@CloneInfo

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:

  "Clones" = @(
      @{
        "NetName" = " MySQLCloneServer ";#the network name of the server
        'Modifications' = 'DatabaseProperties'; #the name of the template to run
        "Database" = "$($database)Ours"; #the name of the Database
        "AdditionalScripts"="@($($env:USERPROFILE)\Clone\ServerModificationScript.sql";
        'username' = 'PhilFactor'; #leave this blank for windows security
        'Original' = $false} #is this the original

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.

Conclusion

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 labeled appropriately with the name of the template. It is while watching all this that it becomes obvious that scripts should be done wherever possible using templates, and external scripts should 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 now that 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.

Let us know your thoughts in the comments below.