In part 2 of my previous series regarding Red-Gate’s tool, SQL Source Control, I warned about an aspect of the tool that could cause you to lose data if you were not careful.  A few days ago I was allowed a sneak peak* at the upcoming fix which Red-Gate has termed SQL Migrations.

To recap, because SQL Source Control does a point-in-time comparison of your database with the current version in your source control system, there are some types of changes that you can perform which SQL Source Control will interpret differently and which could result in loss of data if their script is used for deployment.  The easiest example of this type of change is the renaming of a column or table.  For example, suppose you read my rant from several years ago about separating your singular from your plurals, and you have bought into the idea that all table names should be singular, but you have in your database a table named in the plural form of bacon and you want to rename it in its singular form of bacon.  No, wait a minute, that doesn’t work.  Okay, how about this one, you have a table named People, and now you want to rename it to Person.  So, you go ahead and use whatever is your favorite method to rename the table; whether that is using the right-click menu in the object explorer or simply issuing an sp_rename command, and voila it is done.  You switch over to the Commit Changes tab and discover that SQL Source Control does not know that you simply renamed an existing table.  Instead, all it can tell is that the table People is no longer there, and the table Person now is there, so it sets up the changeset as DROP TABLE People, and CREATE TABLE Person…  The problem with that is if you run that in a database that had data in the People table, you just lost all of that data, even though the change that you actually made in development, simply a rename, did not lose any data at all.  That would be bad.

But the good news is that the folks at Red-Gate have been working on a way to address that issue, and it is scheduled for an upcoming release (version 3.0, I believe).  The solution is an ingenious technique that allows you to override their generated script for the changeset with your own custom script.  Now, you still have to be smart enough to realize you need to do this yourself (and the tool reminds you that this might be a good idea) and when you do, the functionality to support you will be there.  This is a manual process because there is no way for the tool to know whether you really wanted to drop one table and create a new one, or whether it was this rename issue.  So, on the Commit Changes window, there will be an option to mark the changes that SQL Source Control has identified, and tell it that you want to use your own conversion script (or as Red-Gate is calling it, a migration script) in place of their generated scripts (see figure 1 below for example).

 
Figure 1: Create a Migrations Script from the Commit Changes window.

This will lead to a new editor window (figure 2) where you can write your own T-SQL to make the changes (or presumably paste in a copy of the script you originally wrote for the change in development).  There is no significant difference in what you could put in here as compared to what you could put in any query editor window, so if you have a truly complex set of data conversion and migration commands that need to be implemented, just go for it.

 
Figure 2: Migration Script Editor

One of the keys to using this migration script is that you are custom writing all of the commands that have to take place to convert from one version of your source controlled system to another.  That is, if you look at the history of changes checked-in to your source control repository, you will see each check-in has incremented an internal version number.  The custom migration script must contain ALL of the commands that need to execute in order to get from one version to another.  Therefore, I strongly encourage you to be conservative in the scope of your check-ins, and only include the items that require custom changes.  Then any other changes can be checked-in normally, and you have limited the scope that the custom script must cover.

 
Figure 3: Commit Changes list with Migration Script.  Notice the changes that were highlighted when you chose
to create the migration script are now indented underneath it to show that the one overrides the others.

There are two additional really cool attributes of these migration scripts which Red-Gate is implementing.  The first, is that you can make changes to the change script after you have checked it in, and your changes to the migration script retain a history of their own.  The second really cool attribute is that you can tell the system which versions (from and to) of source control that your script overrides (see figure 4).  So, if you have changes that span multiple check-ins, as long as they are consecutive, you can tell SQL Source Control that your migration script actually covers all the changes represented by the changesets from version A to version E, not just A to B, then a separate one for B to C, and so on.

 
Figure 4: Migration Scripts and Source Control Versions Covered by each

The value of this is immediately apparent to anyone who has ever made a mistake.  Suppose the change you were making was to drop the Employees and Managers tables, in order to replace them with a single Person table, and furthermore suppose that when you did the Commit of your change script, you had only marked the DROP of Employees and CREATE of Person for the changeset.  Then, you realize that Managers is still in your pending changes display and you need to commit it.  Go ahead and commit it, and then go back and edit the migration script settings, telling the system that your migration script actually spans those two check-ins.

I am really looking forward to this new functionality.  We have fully embraced SQL Source Control for all of our SQL Server development, and we have one large system in particular where we are performing some significant refactoring of the database.  For now, we have to do some coordinated management of manually produced and tracked custom change scripts.  But when SQL Migrations make their appearance in the next major version, that will eliminate one more headache for me and simplify the development efforts of the whole team.

Before I wrap this all up, I would like to thank the folks at Red-Gate for authorizing me to talk about an upcoming feature like this, and for providing the screen shots.  This is a refreshing difference compared to the handful of experiences I have had with Microsoft where I was sworn to secrecy over usability studies.  It shows once again that Red-Gate understands the value of the community, and of being a good partner within the community.  Yet another reason that I am happy to continue to mention them and their products in this blog.  I make no money from doing so, but their products do make my job easier.  A nice little win-win, if you ask me.

*If you would like to see it and try it for yourself, sign up for the SQL Source Control Early Access Program.

EDIT:  Fixed layout errors and image sizing.

EDIT:  Updated with screen images from the Early Access release which is now available for download.