Database deployments with Octopus and Redgate SQL Release

If you work with SQL Server, you're probably familiar with our friends at Red Gate. They're the leading developer of SQL server tooling. While some of their best known tools (like SQL Compare) focussed on making manual processes easier, lately they've invested a lot of time and energy into database automation. Their latest creation is a plugin called SQL Release, which integrates with Octopus to enable automated database deployments. The following is a guest post by Brian Harris from the SQL Release team.

What is SQL Release?

On the SQL Release team we want to make releasing a new version of your database as easy as releasing other applications. The trouble is that databases contain data; so rather than deploying an entirely new database, you need to upgrade the state of the live database state to match the new version.

The solution we’ve implemented with SQL Release is to generate a SQL update script that upgrades the existing database to the new version. Before running this script, you can review the changes that will be made, and look at any warnings that are raised (for example, data loss when dropping a table).

When you’re happy with the update, SQL Release can run the update script. For additional security, before running the script, SQL Release checks that the target database has not changed since the script was generated.

SQL Release is implemented as a series of PowerShell cmdlets for easy integration with Octopus Deploy. In the future we plan to integrate with other release management tools.

Walkthrough: Using SQL Release with Octopus Deploy

To illustrate how this works in practice, we’ll go through a simple scenario using SQL Release and Octopus to deploy directly from a development database to a production database.

Before we start, we need the following to be set up:

• Octopus Deploy
• An Octopus Deploy environment called Production
• An Octopus tentacle configured with the role of db-server
• SQL Release installed on the same machine as the Octopus Tentacle (the Tentacle needs to be restarted after installing SQL Release)

Creating a new Octopus Deploy project

First we need to create a new Octopus project and give it a name. The project consists of a series of steps which manage the process of deploying the database. When we’re done it will look like this:

Note that we don’t have to specify a target environment for any of the steps in this project. The project will deploy to all environments by default if you do not specify an environment, which in this example is fine, as there is only a single Production environment.

Adding the "Make database release" step

The first step we add creates the deployment SQL script. On the project Process tab, select Add step and select Run a PowerShell script.

Enter these details in the fields:

Name: Make database release Machine roles: db-server
Script: This script uses a set of project variables that we will define later:

# This step uses SQL Release to create a directory containing the
# Database Release: all the artifacts relating to the deployment.
#
# The directory, 'DatabaseRelease', has the structure:
#   - States
#       - Target: SQL Compare scripts folder of the state the database
#                 is in BEFORE the deployment.
#       - Source: SQL Compare scripts folder of the state the database
#                 will be in AFTER the deployment.
#   - Update.sql: The SQL change script that updates the target database from the
#                 target state to source 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 $DatabaseReleaseDirectory) {
    rmdir $DatabaseReleaseDirectory -Recurse -Force
}
# Sets up connection string for the target database.
$developmentDatabase = "Data Source=$DevelopmentDatabaseServer; `
                        Initial Catalog=$DevelopmentDatabaseName; `
                        User ID=$DevelopmentSQLServerUsername;Password=$DevelopmentSQLServerPassword"
$productionDatabase =  "Data Source=$ProductionDatabaseServer; `
                        Initial Catalog=$ProductionDatabaseName; `
                        User ID=$ProductionSQLServerUsername;Password=$ProductionSQLServerPassword"
# Creates the DatabaseRelease directory.
New-DatabaseRelease -Target $productionDatabase `
                    -Source $developmentDatabase `
                    -Verbose `
| Export-DatabaseUpdate -Path $DatabaseReleaseDirectory
# Imports the changes report, deployment warnings, and update script
# as Octopus artifacts, so you can review them in Octopus.
New-OctopusArtifact "$DatabaseReleaseDirectory\Reports\Changes.html"
New-OctopusArtifact "$DatabaseReleaseDirectory\Reports\Warnings.xml"
New-OctopusArtifact "$DatabaseReleaseDirectory\Update.sql"

Click Save.

Adding the "Review database release" step

Next we add a step to pause the deployment for manual review of the script and other resources, so we can make sure we’re happy with the release before it goes ahead. The previous step imported a Change Report, the Update Script, and a Warnings file, which will be visible in the Octopus UI for review.

On the project Process tab, click Add step and select Manual intervention required.

Enter these details in the fields:

Name:
"Review database release"

Instructions:

Please review the deployment artifacts on the right:
(1) Update.sql: SQL change script that updates the target database.
(2) Warnings.xml: XML report containing warnings relating to the deployment.
(3) Changes.html: HTML report showing which database objects will be changed in the deployment and how.

Adding the "Run the update script" step

The final step deploys the script we’ve generated upon approval of the review step.

On the project Process tab, click Add step and select Run a PowerShell script. This is the deployment step.

Enter these details in the fields:

Name: Run the update script
Machine roles: db-server
Script:

#This step uses SQL Release to deploy the database release we previously generated and wrote to disk.
$targetDatabase = "Data Source=$ProductionDatabaseServer; `
                   Initial Catalog=$ProductionDatabaseName; `
                   User ID=$ProductionSQLServerUsername;Password=$ProductionSQLServerPassword"

Import-DatabaseRelease $DatabaseReleaseDirectory | Use-DatabaseRelease -DeployTo $targetDatabase

Click Save.

Set up the project variables

Running the deployment

We’re now ready to deploy a database update in Octopus. Deploying a database release is the same process as deploying a normal update using Octopus Deploy.

Summary

I’ve shown you a simple use case: deploying from one database schema to another as part of your Octopus Deploy release, but SQL Release can be used in more complex situations.

We can use SQL Release to release to a pre-production environment first, and after validating the deployment was successful, we can promote it to our production environment.

The benefit of this approach is that we can run exactly the same script against production that we tested on our pre-production database. As part of the deployment SQL Release checks the production database is still in the initial state we expect, so we can be sure the trial run against pre-production was a good test of the production release, and the script can be re-used safely.

You can also deploy changes from a database NuGet package. This means it’s possible to source control your database, build and test it in your build server, and then deploy that build using SQL Release, with all the security and reliability that provides.

If you’d like to try SQL Release, you can download it here.