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-serverScript: This script uses a set of project variables that we will define later:

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.

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.

Octopus Deploy is used by thousands of developers across the globe, from small companies to large enterprises. Find out if it meets your deployment automation needs by taking advantage of our free 30-day trial. You can spin up an instance with just a few clicks!