How I configured Red Gate SQL Release and Octopus Deploy to deploy SQL Server databases

Earlier this year I documented how I configured Octopus Deploy to use Red Gate’s SQL CI product to deploy SQL Server databases. Since then Red Gate has released a product that is better suited to the task: SQL Release.

The point with SQL Release is that the creation of the upgrade script and other associated deployment resources (more on what those are later) is separated from the execution of the deployment. This gives you the option to include some human review process before the deployment is executed.

Also, SQL Release is a bunch of PowerShell commandlets that talk to the Red Gate SQL Compare technology, rather than a GUI or command line tool. Since you interact with SQL Release through PowerShell, rather than the command prompt, your Octopus Deploy PowerShell scripts become much easier to write/read. I should also mention that SQL Release can also be called from PowerShell directly or from any other product that can execute PowerShell, such as TeamCity or Microsoft Release Management if you so desire.

How SQL Release works

Full documentation is available here so I won’t go into detail. In brief you will need to set a source and a target database state. These can be Red Gate NuGet packages (created by SQL CI), a set of scripts from source control (generated by Red Gate SQL Source control or SSDT), or a specific SQL Server Database. You will then generate a ‘Database Update’ which consists of five components:

  • A snapshot of the before state of the database
  • A snapshot of the after state of the database
  • A human readable HTML diff report of all the changes
  • An XML report of any SQL Compare warnings (such as data loss warnings when dropping tables etc)
  • An upgrade script

You will then have the option to export these resources to disk so that you can review them for yourself. When ready you will ‘publish’ the database update against a specific database of your choosing. When you do it will follow this process:

  1. Pre-deployment check that the target DB matches the snapshot of the before state
  2. Execute upgrade script
  3. Post-deployment check that the target DB matches the snapshot of the after state

My process: To review or not to review?

The beauty of SQL Release is that you can wrap the commandlets into whatever process you choose. I was recently tasked with creating a demo machine that used SQL Release with Octopus Deploy. My goal was to create something that would align reasonably well with most customers deployment processes. If I (or possibly someone else from Red Gate) has recently done a SQL Release demo for you or if you have attended a Red Gate training workshop it is likely that these are the exact PowerShell scripts that we used.

I should make it clear that I have not written all these scripts from scratch myself. I have mostly used examples on the official documentation page as a starting point and I’ve edited them to fit my purposes.

First, you need to consider if the environment you wish to deploy to requires a human review step. For example, if you automatically deploy to some integration or CI environment upon every check-in to source control it is unlikely you will want the burden of reviewing the deployment script every time. Similarly for many test environments you want developers and testers to be able to generate whatever version of the database they choose at the click of a button without DBA approval. Deployments to Staging and Production environments, on the other hand, may benefit from a review by an experienced DBA.

With that in mind, this is the PowerShell script I created for ‘non-Gated deployments’. (Where the update is created and immediately published). It will deploy a given NuGet package (created by SQL CI) to a target SQL Server database. You’ll notice that I ignore the pre and post-deployment drift checks since there will be no time between generating the update and executing it.

# SQL Release simple deployment

# This PowerShell uses the SQL Release PowerShell cmdlets
# to create a database update (a SQL upgrade script with
# some other files). It then deploys that update to a SQL DB.

# Defining the desired pre and post state of the DB as the
# target database and the NuGet package.
$scriptsFolderPath = $OctopusParameters["Octopus.Tentacle.CurrentDeployment.PackageFilePath"]
$target = New-DatabaseConnection -ServerInstance $DatabaseServer -Database $DatabaseName -Username $DatabaseUserName -Password $DatabasePassword

# Creating the database update
$update = New-DatabaseRelease -Source $scriptsFolderPath -Target $target

# Deploying the database update to the target DB whilst
# skipping redundant checks to save time
Use-DatabaseRelease $update -deployTo $target -SkipPreUpdateSchemaCheck

To use this in Octopus Deploy you will need to install SQL Release on the same machine as the deployment agent/tentacle that you plan to use for database deployments (requires reboot) and you’ll need to configure a process step in an Octopus Deploy project to “Deploy a NuGet package”. You’ll then need to click “Configure Features” and select only the “Custom PowerShell scripts” option. You’ll need to add the script above as a post-deployment step.

Finally, you may wish to scope this deployment step purely to the environments that do not need a manual approval step.

Also, this script requires that you set the following variables in Octopus Deploy. You can scope these per environment:

  • DatabaseServer
  • DatabaseName
  • DatabaseUserName
  • DatabasePassword (you can mark this as sensitive and it will be encrypted in Octopus Deploy and in all logs)

Deployments to staging/production

I’ve decided to use the following process for Staging deployments:

  1. Sync the schema in staging with the current production schema so that this dry-run is as accurate as possible
  2. Generate the update using a NuGet package as the source and the Staging DB as the target and save the update to disk
  3. Manual review of update resources by DBA
  4. Publish update against Staging (later we can use exactly the same update on production)

To do this first I’m going to add three more variables to Octopus Deploy. This script assumes Staging and Production are on the same server – which is a consequence of the fact that on our demo machines they are. If there are not in your environment (which I suppose is likely!) you’ll need to modify the script and variables appropriately:

  • DatabaseUpdateResourcesDirectory
  • ProductionDB
  • StagingDB

Be careful with scoping these to specific environments as we want to reference exactly the same values when deploying to Staging and Production. This is why I’ve created separate variable for the Production and Staging database names which will both be used when deploying to Staging.

Next I’m going to add a second step to my process (assuming I’m using the first script (above) for my low level environments). This time I’m using the “Run a PowerShell script” option to perform step 1 of my staging deployment (and I’m going to scope it to my Staging environment only):

# SQL Release simple deployment

# This PowerShell uses the SQL Release PowerShell cmdlets
# to create a database update (a SQL upgrade script with
# some other files). It then deploys that update to a SQL DB.

# Defining the desired pre and post state of the DB as the
# staging and production database
$staging = New-DatabaseConnection -ServerInstance $DatabaseServer -Database $StagingDB -Username @DatabaseUsername -Password @DatabasePassword
$production = New-DatabaseConnection -ServerInstance $DatabaseServer -Database $ProductionDB -Username @DatabaseUsername -Password @DatabasePassword

# Creating the database update 
$release = New-DatabaseRelease -Source $production -Target $staging 

# Deploying the database update to the target DB whilst 
# skipping redundant checks to save time 

Use-DatabaseRelease $release -DeployTo $staging -SkipPreUpdateSchemaCheck

Now I want to create the update and save it to disk. This time I’m using a “Deploy a NuGet package” step with the following post-deploy PowerShell:

# This step uses SQL Release to create a directory containing the
# Database Update Resources: all the artifacts relating to the deployment.
#
# The directory, 'DatabaseUpdateResources', has the structure:
#    - States
#        - BeforeUpdate.snp: SQL Compare snapshot of the state the database
#                            is in BEFORE the deployment.
#        - AfterUpdate.snp: SQL Compare snapshot of the state the database
#                           will be in AFTER the deployment.
#    - Update.sql: The SQL change script that updates the target database from the
#                  BeforeUpdate.snp state to AfterUpdate.snp state.
#    - Reports
#        - Changes.html: An HTML report showing which database objects
#                        will be changed in the deployment and how.
#        - Warnings.xml: An XML report containing warnings relating to the
#                        deployment.

# Makes sure the directory we're about to create doesn't already exist.
If (Test-Path $DatabaseUpdateResourcesDirectory) {
    rmdir $DatabaseUpdateResourcesDirectory -Recurse -Force
}

# Defining the desired pre and post state of the DB as the
# target database and the NuGet package.
$target = New-DatabaseConnection -ServerInstance $DatabaseServer -Database $StagingDB -Username $DatabaseUserName -Password $DatabasePassword
$scriptsFolderPath = $OctopusParameters["Octopus.Tentacle.CurrentDeployment.PackageFilePath"]

# Creates the DatabaseUpdateResources directory.
New-DatabaseRelease -Target $target -Source $scriptsFolderPath | Export-DatabaseRelease -Path $DatabaseUpdateResourcesDirectory

# Imports the changes report, deployment warnings, and update script
# as Octopus artifacts, so you can review them in Octopus.
New-OctopusArtifact "$DatabaseUpdateResourcesDirectory\Reports\Changes.html"
New-OctopusArtifact "$DatabaseUpdateResourcesDirectory\Reports\Warnings.xml"
New-OctopusArtifact "$DatabaseUpdateResourcesDirectory\Update.sql"

This will have the nice consequence of exposing the upgrade script, diff report and warnings as Octopus artifacts in the Octopus GUI:

SQL Release artifacts

 

Now you want to add an Octopus Deploy “Manual intervention required” step so that a DBA can approve the update. This should be scoped to your Staging environment and, if you feel the need, your Production environment as well.

All that is left to do is run this specific update on the Staging and Production environments. To do that you simply add a final build step to “Run a PowerShell script”:

# Use SQL Release to deploy the database update.

# Defining the target database.
$target = New-DatabaseConnection -ServerInstance $DatabaseServer -Database $DatabaseName -Username $DatabaseUserName -Password $DatabasePassword

# Importing and deploying update
Import-DatabaseRelease $DatabaseUpdateResourcesDirectory | Use-DatabaseRelease -DeployTo $target

When finished your process should look like this:
Octopus SQL Release process

Limitations

The production deployment will always deploy the last update that was deployed to Staging (whatever release you actually wanted to deploy). I think this is a wise workflow but for the moment there is no easy way to make that behaviour obvious to users. I can imagine a scenario where a user tries to deploy a different release to Production and is then surprised when the release in the Staging environment is deployed instead. However, this won’t be the case for long. You can already try out the Octopus 2.6 pre-release which includes a ‘Lifecycles’ feature that will support this: https://octopusdeploy.com/blog/2.6

Alternatively, you could try do do something clever by using a Octopus variables in your “DatabaseUpdateResourcesDirectory” to output all your updates to unique locations and then grabbing the right one in your production deployment but that sounded quite complicated for my requirements.

Also (at the time of writing) SQL Release is still in BETA. I would never recommend using a BETA product for production deployments. Given that SQL Release has specifically been designed with production deployments in mind Red Gate is very aware of the irony that it is still in BETA and they plan to get out of BETA early in 2015.

Finally (at the time of writing) the public version of SQL Release does not support deployment of static data. However, there is a version in house that does and it will be shipped it very soon.

  2 comments for “How I configured Red Gate SQL Release and Octopus Deploy to deploy SQL Server databases

Leave a Reply

Your email address will not be published.