Alex Yates

27 January 2017

• SQL Compare
• Database Builds and Deployments
• Database Source Control

How to build multiple database versions from the same source: post-deploy scripts

This is the second post in a three-part series that explains how to use SQL Compare to maintain a single source of truth in version control for a SQL Server database which can be deployed to multiple production instances, despite the fact that some production instances have customizations.

My previous article explained how to use SQL Compare object filters to restrict deployments to a certain set of database objects. This piece explains how to use a combination of post-deploy scripts to handle deployments in cases where a code object (anything that isn’t a table) exists in multiple production instances, but in different states. Tables add an extra layer of complication, and we’ll deal with those in part 3, using pre-deploy scripts.

Set up

To help demonstrate the concepts, I’ll continue to use my sample databases, Twilight Sparkle and Fluttershy, which represent two customized production instances of the same database (Ponies). We’ll also continue to use the ‘master development database’,  Ponies_dev, and the SQL Compare filters that we created in part 1.

You can create the Twilight Sparkle and Fluttershy databases by executing this script. You can create the Ponies_dev database, source control repository and filter files by following the instructions in part 1.

You’ll need a SQL Server instance and a copy of the Redgate SQL Toolbelt. which you can download with a fully functional 14-day free trial.

Handling objects that exist in both environments, but are different

By the end of part 1, we’d created in source control our ‘canonical source’, namely a master development database called Ponies_dev, by deploying to it all the objects in the Twilight Sparkle database, followed by the objects that existed in Fluttershy only. We then used SQL Compare filters to ensure that when deploying from Ponies_dev, we selected only the appropriate objects.

Figure 1

However, where objects existed on both Twilight Sparkle and Fluttershy (objects B, C and D in Figure 1), we were effectively deploying the Twilight Sparkle version, so relied on the fact that these objects were identical in Fluttershy.

However, let’s say Fluttershy has been customized to support local functionality, and one or more of the objects in Fluttershy are different from the version that exists in Twilight Sparkle (and therefore in Ponies_dev)? We risk deploying the wrong version of these objects. In Figure 2, we’d overwrite object Bf with object B.

Figure 2

In this article, we’re going to do the ‘easy’ bit, dealing with any object that is not a table, then in the final part of this series, we’ll deal with the knottier issue of trying to deploy a change made to a table in the ‘canonical source’ when the same object in the target database is deliberately different.

Dealing with differences in code objects with post-deploy scripts in SQL Compare

To solve this problem for code objects only (assuming all tables are in-sync), we are going to create a post-deployment script that we run only when deploying to Fluttershy.

1. Open Redgate SQL Compare.
2. From the options tab select Use DROP and CREATE instead of ALTER.
3. Set Fluttershy as the source and Ponies_dev as the target, and compare.
4. Select all the in both, but different objects except tables. In this case, that leaves just one object, spOnBothButDifferent, but in a real world example you’ll likely have more.

Figure 3

1. Click Deploy… and in the wizard choose the option to Create a deployment script.
2. You should be able to create a script that drops and recreates all the in both, but different objects (except tables) in the Fluttershy state. All the drop and creates should be neatly wrapped into a single transaction. This will be your post-deploy script for the Fluttershy database.
3. Create a new directory in source control and save your post-deploy script as FluttershyPostDeploy.sql. I saved mine to the directory: Documents\Ponies_dev\postDdeploys.

From now on, if developers wish to modify the Fluttershy version of these objects, they must do so by editing the FluttershyPostDeploy.sql, instead of the actual database.

Since these objects are now handled by the post-deploy script, we can revisit the Fluttershy filter we created in part 1 and filter out all the objects that we are now managing with the upgrade script.

Now, assuming your tables are in sync, you can run a single script, shown in Listing 1, to deploy your source code to Twilight Sparkle and Fluttershy:

Listing 1: DeployFluttershy-NoTables.ps1

We only have one object covered in our post-deploy script, but if you had many objects you might prefer to create a post-deploy script for each object and run each script in sequence. This would be easier to manage in development, because you’ll get fewer merge conflicts and it will be more obvious which objects are managed by post-deploys and which are not. However, you’ll want to run all these scripts within a single transaction, as demonstrated in this example on Stack Overflow.

Summary

This article demonstrated how we can deal with non-standard changes to database code objects, in various target environments, while maintaining a single, master source copy of the database, and automating our deployments to each environment from a single script.

That was the easy part; once we start introduce non-standard changes to tables, the solution grows in complexity, as we’ll see in Part 3, where I’ll describe the solution and discuss its shortcomings.

As I’ve stated before, the best way to solve this problem is to fix the root cause and get our databases in sync. However, when that is not possible or practical in the short term, employing strategies such as those discussed in this series can allow us to apply some sort of version control, and gain some measure of control over our deployments.

If you’d like to know how to use SQL Compare object filters to restrict deployments to a certain set of database objects, read my first article in this three-part series.

Read part 3 of the series if you want to maintain a specific table, or set of tables, with different structures in different production versions of the database, using a combination of a migrations tool and a pre-deploy script.