Update, 5 March 2015: This post has been updated to reflect the API changes in version 1.1.0 of SQL Release.
Update, 31 March 2015: This post has been updated to reflect the release of Redgate’s DLM Automation.
Update, 15 December 2015: This post has been updated to reflect recent renaming of the cmdlets.

Introduction

SQL Release is a new tool we’ve developed at Redgate as part of DLM Automation. It enables users to make database changes to production safely and efficiently. It comprises several PowerShell cmdlets that can be easily integrated into any release management tool. This article will show you how to automate database deployments safely, by using SQL Release from within PowerShell scripts.

Syncing two databases

The first example I’m going to consider is syncing schema changes from one database to another. Consider two databases, Integration and Production, where the Integration database contains schema changes that support new functionality not yet in the Production database. The aim is to push the schema changes from Integration to Production.

This schema sync can be achieved using this PowerShell script:

Let’s examine this script to see what’s going on. First of all, the two $integration and $production variables both use the New-DlmDatabaseConnection cmdlet to specify the connection details to the two databases. The output of the New-DlmDatabaseConnection cmdlet is a Database Connection object that contains the connection details for a SQL Server database. By assigning these to variables, we can re-use them in calls to subsequent cmdlets in the script, which is a common pattern in all of the examples here. The -ServerInstance and -Database parameters are required, but there are also optional -Username and -Password parameters, if you want to use SQL Server authentication rather than Windows authentication.

The next step is the use of the New-DlmDatabaseRelease cmdlet. There are two required parameters to this cmdlet

1. The new database state after deploying the release should be specified with -Source. In this case, we specify $integration, ie the Integration database, since it represents the new database state that we want to deploy to production.
2. The current state prior to deploying the release should be specified with -Target. In this case, we specify $production, ie the Production database, since it has the currently deployed database state that is going to be updated.

The output is a Database Release object that can be used to update the schema of the Production database to match that of the Integration database, assigned to the $release variable. Internally, the cmdlet uses the SQL Compare engine to compare the two databases and generate an update script. The Database Release object is essentially a wrapper for that SQL script, though as well see later, it also contains more.

The final step is to deploy the Database Release to the Production database, using the Use-DlmDatabaseRelease cmdlet. This cmdlet executes the update script against the target Production database.

The upshot is that the schema of the Integration database has been synced to the Production database. So why did we have to do this with two separate cmdlets? Why isn’t there a single sync cmdlet? This is a deliberate design decision in SQL Release. We wanted to completely separate using the SQL Compare engine to generate an update script from the application of that script to update a database. It’s important that, once the update script has been created, there is no further SQL Compare magic required to apply it to a database. In fact, the Use-DlmDatabaseRelease cmdlet does little more than execute the update script against a target database. You could achieve the same result by writing out the update script to a .sql file and then using SSMS to execute the script against the Production database. Incidentally, you can export the script to a file using the following PowerShell.

Okay, so just syncing two databases isn’t a particularly revealing use case for SQL Release, because actually there’s more going on, that is a crucial part of what SQL Release offers. The complete process involves an intermediate step, where SQL Release creates a “Database Release” object containing various information relevant to the deployment, and looks like this:

Nonetheless, it’s a useful example that we can build upon in subsequent examples. Incidentally, if you do want to use SQL Release to perform a database sync, I would suggest a change to the above script, as follows:

There are two changes here. Firstly, I’ve eliminated the unnecessary $release variable by piping the Database Release object directly from the New-DlmDatabaseRelease cmdlet to the Use-DlmDatabaseRelease cmdlet (this prevents the Database Release from “leaking” into the rest of your script). Secondly, the -SkipPreUpdateSchemaCheck and -SkipPostUpdateSchemaCheck parameters eliminate some checks that are unnecessary in this specific use case. Hmm! What could be going on there? We’ll find out in due course.

Exporting a Database Release to review the update SQL script

Once the New-DlmDatabaseRelease cmdlet has created a Database Release, a sensible DBA will typically want to review the update script before applying it to a Production database. The best way to achieve this varies according to which release management tool you’re using, but it typically involves exporting the Database Release to disk, so that a DBA can more easily examine its contents. After the review, the Database Release will then be imported by a subsequent script. So now the process looks like this:

As the review by the DBA may take some time, the whole process is divided into two separate scripts. Here’s the first PowerShell script that creates and exports the Database Release for review:

This will export the Database Release to a folder named C:\Work\MyDatabaseRelease. This folder will contain several items. Right now, the most interesting item is the C:\Work\MyDatabaseRelease\Update.sql file, which is the update script that the DBA would like to review. The Export-DlmDatabaseRelease cmdlet can also export the Database Release to a zip archive file rather than a folder, if that turns out to be more convenient. To do this, simply specify an export path that ends with .zip, such as C:\Work\MyDatabaseRelease.zip.

Once the DBA has approved the update script, here’s the second PowerShell script that subsequently imports the Database Release and then applies it to the Production database:

What’s in a Database Release?

Okay, so at this point it should be pretty obvious that a Database Release is more than just an update script. Let’s have a look at it in more detail. An exported Database Release folder typically contains the following items:

The most obvious item is the Update.sql file, which contains the update script. For the purposes of this article, the RedGateUpdateMetadata.xml file is not especially interesting (it’s essentially where we store information from the exported Database Release object that doesn’t have a natural home anywhere else in the exported folder). What about the Reports and States folders?

Reports sub-folder

The Reports sub-folder contains the following items:

Changes.html contains an interactive report that contains information about all of the changes between the Source and Target database schema, similar to the reports that can be generated by SQL Compare.

Warnings.xml contains a list of the warnings that were generated by the SQL Compare engine when the two databases were compared to create the update script.

We intend to change the contents of the Reports folder in the future. In particular, the Warnings.xml file isn’t particularly easy on the eyes, though it’s amenable to automated processing. Conversely, the Changes.html file is much more readable, but is difficult to process in an automated manner.

States sub-folder

The states sub-folder contains two scripts folders that represent the Source and Target database schemas. In the earlier example, the Source represents the Integration database state and the Target represents the Production database state.

Why?

The copies of the Source and Target schemas are present to support some built-in safety checks in SQL Release. Specifically, the Use-DlmDatabaseRelease cmdlet uses these to perform sanity checks against the deployment target database both before and after the update script is executed.

Before the update script is executed, the Use-DlmDatabaseRelease cmdlet will compare the deployment target database and the Target schema to ensure they are equivalent. If the schemas don’t match, the cmdlet will raise an error and refuse to execute the update script. This guards against any accidental schema changes that may have occurred in the target Production database between the time the Database Release was created and the time it is being applied (ie during the time when the DBA was reviewing the update script). It also guards against accidentally applying the Database Release to the wrong target database!

After the update script is executed, the Use-DlmDatabaseRelease cmdlet will compare the updated deployment target database and the Source schemas to make sure that they are equivalent. If the schemas don’t match, again the cmdlet will raise an error. In this scenario, it’s too late to prevent any changes to the database, but it is worthy of further investigation, possibly leading to a decision to roll-back the changes. This guards against any changes made to the update SQL script, perhaps during the DBA’s review, that prevent it from successfully updating from the Target schema to the Source schema.

Both of the above checks can be disabled with Use-DlmDatabaseRelease‘s -SkipPreUpdateSchemaCheck and -SkipPostUpdateSchemaCheck parameters.

Using a database scripts folder instead of a database

If you’re using SQL Source Control to develop your database schema along with your application, then instead of using an Integration database for the Source parameter of the New-DlmDatabaseRelease cmdlet, you can use the source-controlled database scripts folder directly. You can also use a scripts folder created in SQL Compare, perhaps because the Integration database may not be directly accessible from the location you are running SQL Release. In this case, the process now looks like this:

The only change that needs to be made is to the -Source parameter of the New-DlmDatabaseRelease cmdlet. Instead of a Database Connection object, just specify the path of the scripts folder, like this:

Using a NuGet package instead of a database

If you’re using SQL CI as part of Continuous Integration for your database development, then instead of using an Integration database or a database scripts folder for the Source parameter of the New-DlmDatabaseRelease cmdlet, you can use a NuGet package generated by SQL CI. In fact, you can actually use any zip archive file that contains a database scripts folder in the db\state folder within the archive. In this case, the process now looks like this:

Again, the only change that needs to be made is to the -Source parameter of the New-DlmDatabaseRelease cmdlet. Just specify the path of the NuGet file, like this:

Deploying to a pre-production database first

And now for my final example, which is the general workflow we’re recommending for continuous delivery of databases. In the previous examples, the general process has comprised of three steps.

1. Create and export a Database Release, based on the current Production database and the new database schema in the form of a NuGet package or a scripts folder or Integration database.
2. The DBA reviews the update script.
3. Import the Database Release and apply it to the Production database.

Ideally, a DBA should be interested in a further level of validation. Instead of applying the Database Release directly to the Production database after the script review, we introduce an additional dry run against a pre-production database. This extended process comprises of five steps.

1. Preparation
2. Create a copy of the Production database by synchronizing its schema to a Pre-production database.
3. Create and export a Database Release, by comparing the Production and Pre-production databases with the new database schema in the form of a NuGet package.

The DBA reviews the update script. Import the Database Release and apply it to the Pre-production database. Validate that the new Pre-production database functions correctly. Import the Database Release and apply it to the Production database.

Since steps 2 and 4 involve breaks for review by a DBA, steps 1, 3 and 5 all require separate scripts.

Script for step 1

This script does two things:

1. Copy the Production database to the Pre-production database.
2. Create and export the Database Release.

There are many different ways in which the Production database can be copied to the Pre-production database, and many potential problems. The ideal approach would be to create a backup of the Production database and restore it to the Pre-production database. (This method is outside the scope and capability of SQL Release). Sometimes this simply isn’t practical, perhaps because the Production database is located remotely or is simply too large for a backup and restore to be practical. The bottom line is that you need to somehow get the Pre-production database to match the Production database as closely as possible. The more similar they are, then the greater your confidence that, if the Database Release works on the Pre-production database, it’s going to work on the Production database too.

For the sake of simplicity, in my example I’m actually going to use a straightforward method to copy the Production database to Pre-production, using the schema-only sync method outlined in the very first example in this article.

Here’s the first script:

Hopefully this script is fairly straightforward and easy to understand. The one new feature here is the value passed to the -Target parameter of New-DlmDatabaseRelease. Instead of passing $production or $preproduction to the parameter, we’re passing a list that contains both database connections. What does this mean? Well it’s not always possible for the Pre-production database to be an identical copy of the Production database. Sometimes you just have to get as close as you can. By passing both databases in a list to the -Target parameter, the New-DlmDatabaseRelease cmdlet will perform a check prior to creating the Database Release, comparing the two databases to make sure that they are identical in all the ways that actually matter to the update script.

Script for step 3

After the Database Release has been reviewed and approved by the DBA, the next step is to apply the Database Release to the Pre-production database. The script for this step should be fairly familiar by now. It simply involves importing the Database Release and then applying it to a database. In this case, it’s the Pre-production database. Here’s the script:

Script for step 5

After the updated Pre-production database has been validated, the last step is to apply the Database Release to the Production database. Here’s the script for step 5. It’s almost identical to the step 3 script. The only difference is the target database.

Summary

This article has demonstrated some key features of SQL Release running within PowerShell. The basic operations are:

1. Use New-DlmDatabaseRelease to create a Database Release
2. Use Export-DlmDatabaseRelease to export the Database Release to disk for an external review.
3. Use Import-DlmDatabaseRelease to import the Database Release after the review.
4. Use Use-DlmDatabaseRelease to deploy the release to a target database.

If you’re interested in trying out SQL Release, download and install a free trial of DLM Automation. Then start playing with the cmdlets in your own PowerShell scripts. Finally, start thinking about how to integrate SQL Release into your own Release Management process. We’d also love to hear from you if you want to know more details, or have any questions. Why not send us an email?