Deploying and Reverting Clones for Database Development and Testing

SQL Clone is a very handy device for database developers. The main thing stopping me from initially using it more widely 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 pleasure. In fact, more testing becomes possible within the tight schedules we now experience.

Scripting

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 that is 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.

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 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 files that can be shared by several scripts. Listing 1 shows the main config file, called CloneConfig.ps1, which gives all the necessary details of the topology of the development environment for the imaging and cloning process. Everything is scripted in the PowerShell data language, which is executable by PowerShell.

$database = 'MyDatabase' #the name of the database we are cloning e,g, AdventureWorks
  @{
    "Database" = '$database';
    "WorkDirectory" = "$($env:USERPROFILE)\Clone"; #a directory for placing scripts, logs etc
    "tools" = @{ 'SQLCompare' = 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' }
    #where we have SQL Compare installed. Yours could be a different version
    "Original" = @{
      #We will clone from this database. This is the original, maybe a build stocked with data
      'Server' = 'BuildServer'; #The SQL Server instance
      'Instance' = 'Our2017'; #The SQL Server instance
      'Database' = "$($Database)"; #The name of the database
      'username' = ''; #leave blank if windows authentication
      'SQLCompareOptions' = 'NoTransactions' # you sometimes need these for a complex build
    }
    "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 
      'ServerURL' = 'http://MyCloneServer:14145'; #the HTTP address of the Clone Server
      'ImageDirectoryURL' = '\\MyFileServer\Clone' #the URL of the image directory
    }
  <# here is where we put the list of clones. 
  You can specify as many as you wish and they'll all be created #>
    "Clones" = @(
      @{
        "NetName" = "MyFirstServer"; #the network name of the server
        "Database" = "$($database)Original"; #the name of the Database
        'username' = ''; #leave this blank for windows security
        'Original' = $true
      }, #is this the original (only one should be 'true'
      @{
        "NetName" = "MySecondServer"; #the network name of the server
        "Database" = "$($database)Yan"; #the name of the Database
        'username' = 'PhilFactor'; #leave this blank for windows security
        'Original' = $false
      } #is this the original
      @{
        "NetName" = "MyThirdServer"; #the network name of the server
        "Database" = "$($database)Tan"; #the name of the Database
        'username' = 'TonyDavis'; #leave this blank for windows security
        'Original' = $false
      } #is this the original
      @{
        "NetName" = "MyFourthServer"; #the network name of the server
        "Database" = "$($database)Tethera"; #the name of the Database
        'username' = 'AuntiKathi'; #leave this blank for windows security
        'Original' = $false
      } #is this the original
    )
  }

Listing 1: CloneConfig.ps1

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.

<# Before running this, you need to make a list of all the clones you want, and choose
which one will be the reference database. This is done in a config file that will 
also need to have the various details of where the image is to be stored and where
the Clone server is. You need to tell it the name of the database, the 
directory where you want to store the log files and scripts an so on. This is in
a data file called CloneConfig.ps1. A sample version is provided.

You need to have set SQL Clone up properly so it is in working order.    #>
<# first, find out where we were executed from each environment has a different way
of doing it. It all depends how you execute it#>
try
{$executablepath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)}
catch
{$executablepath = "$(If ($psISE)
  { Split-Path -Path $psISE.CurrentFile.FullPath }
  Else { $global:PSScriptRoot })"}

$VerbosePreference = "Continue"
set-psdebug -strict
$ErrorActionPreference = "stop"
#First we read in the configuration from a file (do it so we can use the ISE as well)
$Data = &"$executablepath\CloneConfig.ps1"
<# we read in the data as a structure. #>
$Errors = @()
<#Then we do some sanity checking to make sure that the data is reasonably viable.
We apply defaults if possible The parameter verification is OTT at the moment 
but my scripts tend to grow ...#>

# the fourth value means -1 provide a blank default, 0 = not a directory-must be there, 
# 1=create if not exist, 2 = must already exist
@(($data.WorkDirectory, 'WorkDirectory', '', 1),
  ($data.original.username, 'original', 'username', 0),
  ($data.original.instance, 'original', 'instance', 0),
  ($data.original.Server, 'original', 'Server', 0),
  ($data.original.SQLCompareOptions, 'original', 'SQLCompareOptions', 0),
  ($data.Image.Name, 'Image', 'Name', 0),
  ($data.Image.ImageDirectoryURL, 'Image', 'ImageDirectoryURL', 0),
  ($data.Image.ServerURL, 'Image', 'ServerURL', 0)
) | foreach{
  if ($_[0] -eq $null) #if the parameter has'nt been provided
  {
    # we give a default '' else flag up an error
    if ($_[3] -eq -1) { $data.$_[1].$_[2] = '' } #should be blank
    else
    { $Errors += "There is no $($_[1]).$($_[2]) defined" }
  }
  elseif ($_[3] -ge 1) #it is a directory that needs to be tested
  {
    if (-not (Test-Path -PathType Container $_[0]))
    {
      if ($_[3] -eq 2)
      {
        New-Item -ItemType Directory -Force -Path $_[0] `
             -ErrorAction silentlycontinue -ErrorVariable +Errors;
      }
      else { $Errors += "the path '$($_[0])'in $($_[1]).$($_[2])  does not exist" }
    }
  }
}

if ($Errors.count -eq 0) # if we have soft errors fall out through the application
{
  $CloneImageName = "$($data.Image.Name)"
  #Initiates a connection with a SQL Clone Server.
  #If no credential is specified then the current user's credentials will be used.
  Connect-SQLClone -ServerUrl $data.Image.ServerURL   `
           -ErrorAction silentlyContinue   `
           -ErrorVariable +Errors
  $CloneExists = Get-SqlCloneImage -Name $CloneImageName -ErrorAction silentlyContinue
  if ($CloneExists -ne $null) #does an image with this name already exist?
  { Throw " Image named $($CloneExists.Name) already exists. Delete it or chose another name" }
  write-verbose "Connecting to $($data.Image.ServerURL) Clone Server to create the image called $CloneImageName"
  #we specify the source of the image, which must have an agent and be known to the Clone Server
}
if ($Errors.count -eq 0) # if we have soft errors drop out through the application
{
  $AllArgs = @{
    'Name' = $CloneImageName; #what is specified for its name in the data file
    'SqlServerInstance' = (Get-SqlCloneSqlServerInstance | Where server -eq $data.Original.Server);
    # we fetch the SqlServerInstanceResource for passing to the New-SqlCloneImage cmdlets.
    'DatabaseName' = "$($data.Original.Database)"; #the name of the database
    'Destination' = (Get-SqlCloneImageLocation |
           Where Path -eq $data.Image.ImageDirectoryURL) #where the image is stored
  }
  # Starts creating a new image from either a live database or backup.
  $ImageOperation = New-SqlCloneImage @AllArgs -ErrorAction silentlyContinue -ErrorVariable +Errors   `
                    # gets the ImageResource which then enables us to wait until the process is finished
  write-verbose "Creating the image called $CloneImageName from $($data.Original.Database) on $($data.Original.Server)"
  Wait-SqlCloneOperation -Operation $ImageOperation
}
if ($Errors.count -eq 0) # if we have soft errors spin out through the application
{
  # check that we have a valid clone image
  $ourCloneImage = Get-SqlCloneImage  `
                     -Name $CloneImageName  `
                     -ErrorAction SilentlyContinue -ErrorVariable +Errors
  if ($ourCloneImage -eq $null)
  {
    $Errors += "couldn't find the clone $CloneImageName That has just been created"
  }
  if ($ourCloneImage.State -ne 'Created')
  { $Errors += "We hit a problem with the image. It's state is $($ourCloneImage.State)" }
}

#clone it as whatever database is specified to whatever sql server clone hosts are specified

if ($Errors.Count -eq 0) # if we have soft errors tumble out through the application
{
  # we now just iterate through our list of clones to create each one
  $data.clones | foreach {
    $clone = $null; $Thedatabase = $_.Database;
    #get the correct instance that has an agent installed on it.
    $sqlServerInstance = (Get-SqlCloneSqlServerInstance | Where server -ieq $_.NetName);
    if ($sqlServerInstance -eq $null) { Throw "Unable to find the clone agent for $($_.NetName)" }
    write-verbose "Cloning $($_.Database) on $($_.NetName)"
    #see if there is a pre-existing clone
    $clone = Get-SqlClone  `
                -ErrorAction silentlyContinue  `
                -Name "$($TheDatabase)"  `
                -Location $sqlServerInstance
    if (($clone) -ne $null) #one already exists!
    {
      write-warning  "Removing Clone $Thedatabase that already existed on $($_.NetName)"
      Remove-SqlClone $clone | Wait-SqlCloneOperation
    }
    Get-SqlCloneImage -Name $data.Image.Name |
    New-SqlClone -Name "$($Thedatabase)" -Location $SqlServerInstance |
    Wait-SqlCloneOperation
    write-verbose "cloned $($_.Database) on $($_.NetName)"
    if ($errors.Count -gt 0)
    {
      break
    }
        <# we need to make the original database RO #>
    if ($_.original -eq $true)
    {
      $ConnectionString = "Data Source=$($_.Netname);Initial Catalog=$Thedatabase;"
      if ($_.username -ieq '')
      {
        $ConnectionString += ';Integrated Security=SSPI;'
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
      }
      else
      {
        #create a connection object to manage credentials
        $encryptedPasswordFile = "$env:USERPROFILE\$($_.username)-$($_.Netname).txt"
        # test to see if we know about the password un a secure string stored in the user area
        if (Test-Path -path $encryptedPasswordFile -PathType leaf)
        {
          #has already got this set for this login so fetch it
          $encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString
          $Credentials = New-Object System.Management.Automation.PsCredential($_.username, $encrypted)
        }
        else #then we have to ask the user for it once and once only
        {
          #hasn't got this set for this login
          $Credentials = get-credential -Credential $Username
          $Credentials.Password | ConvertFrom-SecureString |
          Set-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt"
        }
                $ConnectionString += "uid=$($_.username);pwd=""$($Credentials.GetNetworkCredential().Password)"";"
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
      }
      $SqlConnection.Open()
      $sqlCommand = $sqlConnection.CreateCommand()
      $sqlCommand.CommandText = "USE [master] ALTER DATABASE [$Thedatabase] SET READ_ONLY WITH NO_WAIT"
      $sqlCommand.ExecuteNonQuery()
    }
  }
}
# do all the error reporting in one place
  if ($errors.Count -gt 0)
  {
    $errors | foreach {
      Write-error $_; "$((Get-Date).ToString()): $($_) the clone-creation was aborted">>"$Data.WorkDirectory\Errors.log";

      write-error("$($_)")
    }
  };

Listing 2: Image and Clone Creation

With this script for making new clones, we have the basics. 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 manual. 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 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-dow"' reset were part of a test script, it would need to be run automatically, so it might be better to have the reset information in a separate file.

<# fill these values with the name of the clone that you want to revert/reset; a clone 
is best identified by its NetName (server) and database #>
$Reset = @{
  'Database' = 'NameOfDatabase';
  'Server' = 'TheNetNameoftheServer'
}

$VerbosePreference = "Continue"
<# 

#>
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop"

<# just to make it easier to understand, the various parameter values are structured in a 
hierarechy. We iterate over the clones when making or updating them #>
$Errors = @()
#First we read in the configuration from a file (do it so we can use the ISE as well)
<# first, find out where we were executed from #>
try
{$executablepath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)}
catch
{$executablepath = "$(If ($psISE)
  { Split-Path -Path $psISE.CurrentFile.FullPath }
  Else { $global:PSScriptRoot })"}

$Data = &"$executablePath\CloneConfig.ps1"
<# we read in the data as a structure. #>
if ($data.tools.SQLCompare -ne $null)
#we define the SQLCompare alias to make calling it easier
{ Set-Alias SQLCompare $data.tools.SQLCompare -Scope Script }
else
{ $Errors += 'the path of SQL Compare must be defined' }
<#connect to clone #>

Connect-SQLClone -ServerUrl $data.Image.ServerURL   `
         -ErrorAction silentlyContinue   `
         -ErrorVariable +Errors
if ($Errors.count -eq 0)
{
  $image = Get-SqlCloneImage -Name $data.Image.Name    `
                 -ErrorAction silentlycontinue    `
                 -ErrorVariable +Errors

  if ($Errors.Count -gt 0)
  { Write-Warning "The image $data.Image.Name can't be found" }
}
<# now we need to find out the clone that we need to use to compare with the clone
that we want to revert to save any differences. #>

if ($Errors.count -eq 0)
{
  $originalClone = @()
  $data.clones | foreach {
    if ($_.original -eq $true)
    { $originalClone = $_ };
    if ($_.database -eq $Reset.Database -and $_.NetName -eq $Reset.Server)
    { $ResetClone = $_ }
  }
<# check that we have got everything correctly #>
  if ($originalClone.original -ne $true)
  {
    $errors += 'You have not defined which clone represents the original'
  }
  if ($ResetClone.database -ne $Reset.Database -or $ResetClone.NetName -ne $Reset.Server)
  {
    errors+=  'You have not defined which clone represents the one you wish to reset'
  }

}
<# save any schema differences between the two #>
if ($Errors.count -eq 0)
{
  write-verbose "checking whether anything has changed on clone $($ResetClone.Netname) $($ResetClone.Database) compared with  $($OriginalClone.Netname) $($OriginalClone.Database)"
  <# make sure all the connections are servicable #>

  # we need to get hol;d of the passwords for any connection that has a userid
  # attached to it. We save these in a file within the user area, relying on NTFS security and 
  # encryption (gulp)
  @($ResetClone, $OriginalClone) | foreach{
    if ($_.username -ine '')
    {
      #create a connection object to manage credentials
      $encryptedPasswordFile = "$env:USERPROFILE\$($_.username)-$($_.Netname).txt"
      # test to see if we know about the password un a secure string stored in the user area
      if (Test-Path -path $encryptedPasswordFile -PathType leaf)
      {
        #has already got this set for this login so fetch it
        $encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString
        $_.Credentials = New-Object System.Management.Automation.PsCredential($_.username, $encrypted)
      }
      else #then we have to ask the user for it
      {
        #hasn't got this set for this login
        $_.Credentials = get-credential -Credential $Username
        $_.Credentials.Password | ConvertFrom-SecureString |
        Set-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt"
      }

    }
  }
  #Now we have the connection information 
  #we need to make sure that the work directory is there and
  # also that there isn't a script file  there already. 
  if (-not (Test-Path -PathType Container "$($data.WorkDirectory)"))
  {
    New-Item -ItemType Directory -Force -Path "$($data.WorkDirectory)" `
         -ErrorAction silentlycontinue -ErrorVariable +Errors;
  }
  $OutputMigrationScript = "$($data.WorkDirectory)\$($ResetClone.Database)-$($OriginalClone.Database).sql"
  # if there is already a script there, we rename it
  if (Test-Path -PathType Leaf $OutputMigrationScript)
  {
    rename-item -literalpath $OutputMigrationScript -NewName "PreviousScript$(Get-Date -format FileDateTime)" -Force `
          -ErrorAction silentlycontinue -ErrorVariable +Errors;
  }
<# We assemble all the commandline arguments required for SQL Compare#>
  $AllArgs = @("/server1:$($OriginalClone.Netname)", # The source server
    "/database1:$($OriginalClone.Database)", #The name of the source database on the source server
    "/server2:$($ResetClone.Netname)", #the clone
    "/database2:$($ResetClone.Database)", #The name of the database on the clone server
    "/scriptfile:$OutputMigrationScript",
    "/include:Identical")
<# We add in extra parameters if necessary to deal with sql server authentication #>
  if ($OriginalClone.username -ne '')
  {
    $AllArgs += "/password1:$($OriginalClone.Credentials.GetNetworkCredential().Password)"
    $AllArgs += "/username1:$($OriginalClone.username)"
  }
  if ($ResetClone.username -ne '')
  {
    $AllArgs += "/password2:$($resetClone.Credentials.GetNetworkCredential().Password)"
    $AllArgs += "/username2:$($ResetClone.username)"
  }
<# now we can at last run SQL Compare to save the script changes just in case #>
  SQLCompare @AllArgs  > "$($data.WorkDirectory)\$($ResetClone.Database)-$($OriginalClone.Database).txt" #save the output
  if ($?) { "The clones have now been compared (see $($data.WorkDirectory)\$($ResetClone.Database)-$($OriginalClone.Database).txt)" }
  else
  {
    if ($LASTEXITCODE -eq 63) { 'Databases were identical' }
    else { $errors += "we had a comparison error! (code $LASTEXITCODE)" }
  }
}
if ($Errors.count -eq 0)
{
  write-verbose "Reverting/resetting the clone $($ResetClone.Netname) $($ResetClone.Database)"
  $location = Get-SqlCloneSqlServerInstance | Where server -eq $ResetClone.Netname;
  if ($location -eq $null)
  { $errors += "could not find sql server corresponding with $($ResetClone.Netname) " }
  if ($Errors.count -eq 0)
  {
    Get-SqlClone -Name $ResetClone.Database.ToString() -Location $location `
           -ErrorAction silentlyContinue   `
           -ErrorVariable +Errors |
    Reset-SqlClone  `
             -ErrorAction silentlyContinue   `
             -ErrorVariable +Errors |
    Wait-SqlCloneOperation
    write-verbose "The clone $($ResetClone.Netname) $($ResetClone.Database) is now reset"
  }

}
<# We collect all the soft errors and deal with them here.#>
if ($errors.Count -gt 0)
{
  $errors | foreach {
    Write-error $_; "$((Get-Date).ToString()): $($_) the rollback was aborted">>"$($Data.WorkDirectory)\Errors.log";

    write-error("$($_)")
  }
}; 

Listing 3: Reverting a Clone

Summary

Once you get 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 afterward to get ready for the next test run with the confidence of knowing that the data is in the same state. Not only that, but you 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's a kick-start into automating away some of the more tedious aspects of development work.