• SQL Compare
• Database Builds and Deployments

From Source Control to Database using SQL Compare Command Line

Our team develops real-world applications for commercial organizations; which are mainly Georgian Banks. These applications are based on .Net-Windows-Forms applications with MS SQL Server as a database. They have a lot of business logic that is contained in database routines such as stored procedures, functions, views and SQL CLR.

Unsurprisingly, considering the nature of the business of our customers, we do not have access to customer databases, or data, for development, testing or deployment. We have only our development database, and its artificial test data, under TFS Source Control. Developers work on their own copy of the database, each with its own sample data, and they commit development changes using Redgate SQL Source Control. We then use SQL Compare Command line to automate database deployments. In this article, I’ll explain how we achieve this, demonstrating how, for example, we compare two revisions of a database in the same or different branches, and generate a deployment script.

Managing databases in source control

Our database source control, and branching, strategy is simple. We have the latest codebase in Trunk; the whole application is there, including the database part of it. All the new features and bug fixes happen initially in Trunk. Each branch we create is just a copy of Trunk, and so represents a complete, point-in-time-state of the codebase. After some change is applied and checked-in to the Trunk, we can then merge it down to any of these branches, as required. This usually happens to fix reported bugs, but we also need to be able to merge small functional changes when it’s important for our customers. For example, not every customer can afford to deploy every release, so often their deployed version is three or four releases behind. However, they still need us to deploy urgent fixes, an occasionally small bits of ‘exclusive’ functionality, to their current release.

So how does this all work when we’re developing our software? Let’s refer to it as ‘under-source-control-application’, or USCAPP for short. We have the latest code base in USCAPP_Trunk and some released versions of it under the TFS branches, called v241, v242 and so on.

All changes, made directly or through merge, are achieved by ordinary TFS check-ins both for Trunk and for its branches. With every check-in, TFS creates something called a changeset, which has a unique reference number. A changeset represents a snapshot of the whole code base in source control. Like any other source control system, TFS can generate the point-in-time-state of the codebase for any revision, corresponding to any given changeset number.

A TFS changeset number is, of course, global for all TFS projects in a collection, including their branches, and it increases incrementally with every single check-in for that project collection. For us, this means that USCAPP_Trunk and all its branches, v241, v242 and so on, all share the same, global, ever-increasing changeset number.

Developers make their changes, each of them working on their own dedicated database, and checking in their changes, via SQL Source Control, which update the code in USCAPP_Trunk. As needed, we merge the required changesets down to the other branches, creating new changesets in those branches. So, let’s say the latest version is v245, and we know that customer A has deployed v242 to production. This customer cannot yet upgrade to the latest version but has deployed an additional upgrade script to with some bug fixes and make some small improvements. In other words, customer A is running a very specific version of v242 and we can translate this into a TFS changeset number that uniquely identifies the point-in-time-state of the codebase of the branch v242 they have deployed.

Automatically generating change scripts with SQL Compare command line

Our aim is to automate the process of generating the synchronization SQL scripts that cover all the changes that happened since previously issuing a script.

Let’s assume that the customer A has deployed branch v242 and this released version of the database is marked with the human-readable version number of 2.4.2.0, which corresponds to the changeset number 87300, i.e. it was released when changeset 87300 was the current latest global changeset number for the codebase.

A month has passed since then, and we’ve made number of changes in the database and now the current changeset number we have in TFS is 88100. Now we want to generate a script that contains every single change that was made in this same month and so upgrades v2.4.2.0 of the database to the state represented by changeset number 88100, which we will call v2.4.2.1.

To do this, we need to retrieve from TFS both point-in-time-states of the database, one representing the source database, which will not change, and one representing the target database, which we want to upgrade. So, for customer A, changeset 88100 represents the source and 87300 the target. We need to compare the two states to work out the differences, and then generate a script to synchronize the target so that its state is the same as the source. For any database object that exists in both databases but with differences, the object’s definition in the target must be altered to match its definition in the source. Any objects that exist in the source but not in target should be created, and any that exist in target but not in source should be dropped.

The good news, of course, is that we don’t have to do that manually; SQL Compare GUI and SQL Compare command line both support this. We want to automate the process, so we use the command line and just pass the appropriate parameters to it, to generate the synchronization script. We also need to carefully record that this script upgrades version 2.4.2.0 of the database to v2.4.2.1. Of course, there are some safeguards we need here too; one of which is a check that will stop this script being run on any database that is not at v2.4.2.0. I won’t demonstrate that here, but I’ll discuss these sorts of requirements in a bit more detail, at the end.

Comparing two revisions in the same branch

First, I’ll describe how we issue the script we call ‘fix’, used mainly to deploy some bug fixes and minor improvements. The major version remains the same.

We do it using SQL Compare command line, passing an XML argument file (argfile), which contains the values for all the required command line switches that instruct SQL Compare how to perform the comparison. Alternatively, you can specify every single switch to the command line, or ‘splat’ the arguments in PowerShell.

In this case, the only parameter that need to pass to SQL Compare is the qualified filename of the XML Argfile, called “shared.xml”

"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"shared.xml"

The content of the argfile should be filled out exactly according to the instructions in the online documentation of the SQL Compare command line. Here’s the real-life example:

The Argfile contains five command line switches, among others, which we use to define the required behavior. The /Sourcecontrol1 and /Sourcecontrol2 switches specify that our source, and target, is a folder of source-controlled scripts, in this case at revisions 88100 and 87300, respectively.

The <ScriptsFolderXML> switch contains the full filepath to an XML file, SourceControlAddress v242.xml. This file, show below, contains the source control address of the database of the branch v242:

This is the address from which SQL Compare should recover 87300 and 88100 changesets. When the command line version of SQL Compare is executed, it will restore these changesets as Scripts Folders (into a folder within Windows Temp at the time of writing) and compare them using 88100 as source and 87300 as target, to generate the final upgrade script.

Comparing databases in two different branches

The process that we use to release all the new features that we have completed in the Trunk is slightly differently to the bugfix releases, but the main concept remains the same. In this case too, we must compare two different states of the database schema. Even though their ‘source of truth’ exists as versions in TFS source control, they become exported to a folder as something that Redgate calls a Scripts Folder. They can then be compared as two database schemas. In this case, the difference is that we’re not comparing two revisions (or point-in-time-states) represented by changesets in one TFS branch, but between two branches that now represent releases.

To go step by step: the process starts with creating a new branch from the Trunk branch and giving it an appropriate name. For example, if v2.4.2 is the last released version of the USCAPP application, then at the time of that release we had created a branch called v242. We’ve now made more changes to Trunk, and logically we are next going to release version v2.4.3, so our new branch will be called v243, which, in that moment, in regards of the content it contains, starts off as the exact copy of the Trunk branch.

Now, we should compare two changesets of two separate branches. The changesets we use for comparison must be the changeset for the new v243 branch which has been just created, and the changeset that corresponds for the last released deployment script for the previous branch, v242, that the customer A has applied. This comparison will reveal the changes that happened only to the Trunk’s database and are missing from the database of the previous branch, v242.

To achieve this, we need to specify the location of not one but two source control folder locations, one for the TFS branch containing the source, /ScriptsFolderXML1, and one for the branch containing the target, /ScriptsFolderXML2. We use the SQL Compare reserved keyword “HEAD” to specify that we want the very latest possible source control changeset of the source branch. The resulting Argfile looks like this:

Here is the source control Scripts Location XML file for the target (SourceControlAddress v242.xml):

And here is the one for the source (SourceControlAddress v243.xml):

Once again, we simply call SQL Compare command line with the address of the Argfile as the only argument:

"%programfiles(x86)%\Red Gate\SQL Compare 13\sqlcompare" /Argfile:"shared.xml"

And after SQL Compare command line will finish its job, in the file “Shared.sql“, we have upgrade script which we can run on the target database to bring it up to the latest major version.

Further requirements

In real life processes, we always need to check auto-generated scripts carefully, adding checks and controls to make sure, for example, that we are applying all the required upgrade scripts, in the right order, to the intended database version. We also need to make small additions and customizations to SQL Compare’s the auto-generated deployment script, for example to deal with data insertion, or to add header information to each script (when it was created, copyright information; contact information, and so on), or to append to the end of each auto-generated script some dynamically generated piece of SQL Script, identifying the customer.
Much of this is possible by adapting the SQL Compare deployment using custom migration scripts, although in truth we’ve experienced a few difficulties with them slowing down the operation of SQL Source Control, or with pre- and post-deployment scripts.

An additional consideration for us was that SQL Compare migration and pre- or post-deployment scripts are static, whereas our requirement is for dynamically generated scripts. We have instead built a simple, lightweight tool in Visual Studio that allows developers to make small, dynamic additions and customizations to the SQL Compare script.

Another complication that I avoided delving into here is that for each of our customers, our trunk in source control combines all the shared logic for our customers’ databases together with small routines that contain customized code that are exclusive to that organization. In this article, I demonstrated how we use SQL Compare command line to do deployments of the database structures and code that are common to all customers. While the process is essentially the same for the customer-specific routines, there are a few small adjustments to ensure this exclusive functionality should always be deployed only to that customer’s production database and that no customer can see logic written specifically for another customer. I’ll describe how we achieve this in my next article!

Conclusion

Our experience is that Redgate Source Control and SQL Compare can work well in tandem and does a great job for us to automate the script generation process. SQL Compare allows very fine control over the way it can extract scripts from Git or TFS source control, which saves us a great deal of manual scripting. We can see further possibilities in being able to automate the generation of corresponding rollback (downgrade) scripts as well as the upgrade scripts. It requires little more than reversing the changesets we are using for Source and Target and launching SQL Compare command line! It is a versatile tool.

Tools in this post

Redgate Deploy

Automate database deployments across teams and technologies

Find out more

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Source Control

Connect your databases to your source control system

Find out more