Everything email. Resources that will help your campaigns perform better and make you look like a genius.

Attack of the Clones – Part I

April 17, 2018  •  By: John Kuempel

Environment Cloning with Azure Automation

First Contact

At PowerInbox we host half of our services in Microsoft Azure.  For testing, we need to clone our production environment to have QA approximate it.  We use Azure Automation and PowerShell to do this.

Azure Automation has many uses.   Foremost of these is the ability to use scripts to control many maintenance and operations activities.  For instance, you can update VM operating systems or spin up new instances based on arbitrary criteria (e.g. testing; CPU, memory, or traffic spikes).  In our case, we use it to copy data from production and build and deploy the latest QA release to our test environment.

Run

Azure Automation relies on runbooks for controlling automation and process flow.  They are like Workflow Foundation scripts under the covers but implemented using a designer, Python, or PowerShell.  Runbooks can call other runbooks and other PowerShell scripts.  There are caveats that may require using an InlineScript block in certain cases, such as invoking methods on .NET objects.

To access secure shared variables, like credentials, we use Automation Variables.  You create these from the Azure Portal UI or using PowerShell or DSC configuration.  We use the Get-AzureRmAutomationVariable cmdlet to access them.

As with all computer related tasks, sometimes things go wrong, and it is for this reason that logging is a must. It is good practice to set your runbook’s VerbosePreference to a level that will provide you with, at the very least, some helpful output (at least until you work out the kinks).  But sometimes things go wrong. Logging is especially important when using cloud based infrastructure components like Azure Automation as you do not have access to the physical hardware for debugging.

Here’s the runbook we use to coordinate the entire clone process (edited to remove sensitive data):

<# .SYNOPSIS Deploys the service to the Testing environment, using the Production databases. .DESCRIPTION This runbook deploys the testing branch of the service, drops the existing testing database, and replaces it with the production database. .PARAMETER $SourceDatabase Optional with default of 'production-database'. The name of the database to clone into the testing environment. .PARAMETER $SourceBranch Required The name of the source code branch to deploy to the testing environment. #>
workflow testing-deployment
{
  Param
  (
      [parameter(Mandatory=$false)]
      [string] $SourceDatabase = ‘production-database’,

      [parameter(Mandatory=$true)]
      [string] $SourceBranchName
  )
   $oldVerbosity = $VerbosePreference
   $VerbosePreference = “Continue”

   $start = [System.DateTime]::Now

   Write-Verbose “Deployment began at $start.”

   $serviceName =  “test-service-name”        
   $dbRsrcGrpName = Get-AutomationVariable –Name ‘DatabaseResourceGroupName’
   $srvrName = Get-AutomationVariable –Name ‘ServerName’
   $db = “testing-database”      
   $copyDatabaseName = $SourceDatabase + ‘_Copy’    

  # clone testing to backup
  $backupDb = $db + “_Backup”

   Write-Verbose “Copying $db to $backupDb“

   clone-database `
       –Server $srvrName `
       –ResourceGroupName $dbRsrcGrpName `
       –SrcDatabase $db `
       –TargetDatabase $backupDb

  Write-Verbose “Copy complete”

  # clone production database
   Write-Verbose “Copying $SourceDatabase to $copyDatabaseName“

  clone-database `
       –SqlServer $sqlSrvrName `
       –ResourceGroupName $dbRsrcGrpName `
       –SrcDatabase $SourceDatabase `
       –TargetDatabase $copyDatabaseName   

  Write-Verbose “Copy complete”

  # Delete some data from Copied Database
  $connectionString = “connection string”

  InlineScript {
      $cn = New-Object System.Data.SqlClient.<ConnectionType>($using:connectionString)
      $cn.Open()

      # deleting rows from target database
      Write-Verbose “Deleting rows from tables in using:copyDatabaseName”

      $deleteCmd = $cn.CreateCommand()
      $deleteCmd.CommandText = “DELETE FROM table3 WHERE 1=1”
      $rowsDeleted = $deleteCmd.ExecuteNonQuery()
      Write-Verbose “$rowsDeleted rows(s) deleted”

      $deleteCmd.CommandText = “DELETE FROM table4 WHERE 1=1”
      $rowsDeleted = $deleteCmd.ExecuteNonQuery()
      Write-Verbose “$rowsDeleted rows(s) deleted”
      
      $cn.Close()
  }

  # stop service
  Write-Verbose “Stopping $serviceName“
  Stop-AzureVMs –ServiceName $serviceName

  # drop testing and copy copied database to its name
  Write-Verbose “Dropping $db database”

  drop-database `
       –Server $srvrName `
       –ResourceGroupName $dbRsrcGrpName `
       –TargetDatabase $db

   Write-Verbose “Copying $copyDatabaseName to $db“

   clone-database `
       –Server $srvrName `
       –ResourceGroupName $dbRsrcGrpName `
       –SrcDatabase $copyDatabaseName `
       –TargetDatabase $db

  Write-Verbose “Copy complete”

  # drop copied database
  Write-Verbose “Dropping $copyDatabaseName database”

  drop-database `
       –Server $srvrName `
       –ResourceGroupName $dbRsrcGrpName `
       –TargetDatabase $copyDatabaseName

  # publish new build
  Write-Verbose “Queuing build”
  queue-team-services-build –VstsAccount “account” –VstsProject “project” –VstsBuildDefinitionId Id –VstsSourceBranch $SourceBranch –BuildConfiguration ‘release’ –Environment ‘test’ –DatabaseEnvironment ‘testing’ –StorageDestinationKey ‘storage destination key’

  # service will be restarted as part of build

  # perform maintenance tasks in service
  $username = Get-AutomationVariable “Username”
  $password = Get-AutomationVariable “Password”
  $credential = New-Object pscredential($userName, (ConvertTo-SecureString $password –AsPlainText –Force))

  Invoke-WebRequest –UseBasicParsing –Uri “service endpoint” –Credential $credential –Method Post –TimeoutSec 1500 –ErrorAction Stop

  # drop backup database
  Write-Verbose “Dropping $backupDb database”

  drop-database `
     –Server $srvrName `
     –ResourceGroupName $dbRsrcGrpName `
     –TargetDatabase $backupDb

   $now = [System.DateTime]::Now
   $deploymentLengh = $now – $start
   Write-Verbose “Deployment ended at $now.  It lasted $deploymentLengh.”
   $VerbosePreference = $oldVerbosity
}

This is a lot to take in at once, but pieces of it will be explored in more detail in the rest of the post.

Liberate the Data

The main service being tested in our QA environment relies on a database.  This requires dropping the existing database in the QA environment and copying the production database.  There are Azure cmdlets that handle the heavy-lifting for these operations. However, we need to change the data in certain tables.  That requires executing arbitrary SQL commands against the copied database. To do that, we use ADO.NET objects. Because we’re invoking .NET objects, we must put these in an InlineScript block.  The downside to this approach is it’s a little clunky to access variables outside the block. Also, creating .NET objects in PowerShell can be a little verbose, but everything works as you’d expect otherwise.

Here’s the script we use for dropping the old database:

<# .SYNOPSIS Removes the given database from the given server under the given Azure resource group. .DESCRIPTION Removes the given database from the given server under the given Azure resource group. REQUIRED AUTOMATION ASSETS An Automation connection asset called “AzureRunAsConnection” that is used for authenication when copying the database. To use an asset with a different name you can pass the asset name as a runbook input parameter or change the default value for the input parameter. An Automation variable asset called “AzureSubscriptionId” that contains the GUID of the Azure subscription. To use an asset with a different name you can pass the asset name as a runbook input parameter or change the default value for the input parameter. .PARAMETER AzureConnectionName Optional with default of “AzureRunAsConnection”. The name of the Automation connection asset to use to “log in” to Azure and perform the database copy. .PARAMETER AzureSubscriptionIdAssetName Optional with a default of “AzureSubscriptionId”. The name of the Automation variable asset that contains the GUID of the subscription and is used to select the correct subscription. .PARAMETER ResourceGroupName Required The name of the Azure resource group to which the host SQL server belongs. .PARAMETER Server Required The name of the server which hosts the target database. .PARAMETER TargetDatabase Required The name of the database to which to copy the source database. #>
workflow drop-database {
   param (     
       [parameter(Mandatory=$False)]
       [string] $AzureConnectionName = “AzureRunAsConnection”,
     
       [parameter(Mandatory=$False)]
       [string] $AzureSubscriptionIdAssetName = “AzureSubscriptionId”,

       [parameter(Mandatory=$True)]
       [string] $ResourceGroupName,

       [parameter(Mandatory=$True)]
       [string] $Server,

       [parameter(Mandatory=$True)]
       [string] $TargetDatabase
   )   

   log-into-azure-arm –AzureConnectionName $AzureConnectionName –AzureSubscriptionIdAssetName $AzureSubscriptionIdAssetName 

   Write-Verbose “Removing $TargetDatabase“
   $removed = Remove-AzureRmSqlDatabase –ResourceGroupName $ResourceGroupName –ServerName $Server –DatabaseName $TargetDatabase –Force –ErrorAction SilentlyContinue
   if ($removed) {
       Write-Verbose $removed
   }
}

Invoking the log-into-azure-arm runbook is necessary for working with Azure Resource Manager cmdlets. Here’s the script:

workflow log-into-azure-arm {
   param (
       [parameter(Mandatory=$False)]
       [string] $AzureConnectionName = “AzureRunAsConnection”,        

       [parameter(Mandatory=$False)]
       [string] $AzureSubscriptionIdAssetName = “AzureSubscriptionId”
   )

   try
   {
      $servicePrincipalConnection = Get-AutomationConnection –Name $AzureConnectionName
      Write-Verbose (Add-AzureRmAccount –ServicePrincipal –TenantId $servicePrincipalConnection.TenantId –ApplicationId $servicePrincipalConnection.ApplicationId –CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint)
   }
   catch {
       if (!$servicePrincipalConnection)
       {
           $ErrorMessage = “Connection $AzureConnectionName not found.”
           throw $ErrorMessage
       } else{
           Write-Error –Message $_.Exception
           throw $_.Exception
       }
   } 

   $subscriptionId = Get-AutomationVariable –Name $AzureSubscriptionIdAssetName
   Write-Verbose (Set-AzureRmContext –SubscriptionId $subscriptionId –ErrorAction Stop)
}

This script copies the target database:

workflow clone-database
{
  param(
       [parameter(Mandatory=$False)]
       [string] $AzureConnectionName = “AzureRunAsConnection”,

       [parameter(Mandatory=$False)]
       [string] $AzureSubscriptionIdAssetName = “AzureSubscriptionId”,

       [parameter(Mandatory=$True)]
       [string] $Server,

       [parameter(Mandatory=$True)]
       [string] $SrcDatabase,

       [parameter(Mandatory=$True)]
       [string] $TargetDatabase,

       [parameter(Mandatory=$True)]
       [string] $ResourceGroupName
  )
   log-into-azure-arm –AzureConnectionName $AzureConnectionName –AzureSubscriptionIdAssetName $AzureSubscriptionIdAssetName

   Write-Verbose (New-AzureRmSqlDatabaseCopy –ServerName $Server –DatabaseName $SrcDatabase –CopyDatabase $TargetDatabase –ResourceGroupName $ResourceGroupName –ErrorAction Stop)
   
   Write-Verbose “$TargetDatabase is online”
}

To Be Continued…

In the next installment, we’ll cover the rest of the process of cloning the production environment.  This will include triggering and monitoring the build, inserting test data, and running data migrations.  Thank you for reading.

Subscribe

Get the inside track on the latest AdTech & MarTech news, trends and strategies.

Blog Form

You direct-sold ads in email that easily translates into money. How? Check out AdServer for Email.

Drive revenue in every email you send. Learn how with RevenueStripe.

Subscribe

Get the inside track on the latest AdTech & MarTech news, trends and strategies.

Blog Form

You direct-sold ads in email that easily translates into money. How? Check out AdServer for Email.

Drive revenue in every email you send. Learn how with RevenueStripe.