We've been trying to get Redgate migration scripts going for our next release, and have found that Redgate Compare isn't picking up on the migration scripts already committed to TFS. We're using SQL Server 2008 and TFS 2010.

When going through the deployment wizard the step for selecting the migration scripts to include is skipped over as it says there are none.

There doesn't seem to be any way of selecting where the migration scripts are kept in SQL Compare (like you would in linking a database to source control in redgate source control in SSMS). How does SQL Compare pick up on the location of the migration scripts folder?

I'm having a problem with it not picking up migration scripts for some databases as well. I'm using SVN and SQL Server 2008. Is anyone else seeing this?

Note: in your "from" database if you right click and go to properties and then look in the extended properties tab you'll see that the version number, URL, and Migration scripts URL are all listed there.

Expected result: Migration script symbol next to objects for which migration scripts were created. Step in wizard to select migration scripts to utilize.

Actual result: No migration script symbol for objects for which migration scripts were written. No step in wizard to select migration scripts to apply.

This behavior is different than if starting from SSMS, selecting a database to compare from initially, then switching to source control as the source.
In that case the symbol appears and the wizard step exists.

Problem: Migration script includes a separate meta file with .migrationScript extension that has URL (and revision number) to the database script repository.

I presume these are so SQL Compare can calculate the changes up to the migration script, then apply the migration script, and finally generate a script to bring the DB up to date. Essentially telling SQL Compare what the database should look like before and after the script.

I'm not sure there is a reliable work-around to this one. While I can (and did) change the URL in the .migrationScript to match the new branch. This resulted in SQL Compare showing the existance of the script and including the wizard step to include it in the deployment. However the specific revisions referred to do not exist in that branch. It is easy to think of some scenarios where the result may not be as intended.

So for the moment, deploying from a tagged or release branch using migration scripts is problematic.

David, I hope the reason you are available so late is that you are on the west coast preparing for SQL in the City Seattle. Caught your presentations in Chicago and I recommend them to anyone doing version control or deployment.

You have indeed identified a limitation of our migrations implementation. It works well for a single branch, but it falls short when generating deployments cross-branch.

We used to simply not pull in migration scripts if the branches weren't the same, which is why we store the branch url in the .migrationscript file. In the latest version of SQL Compare, however, we've relaxed that requirement as it will allow deployments to work in some cases. However, because we don't know the correct order to use as revisions numbers are only linear within a branch, there's certainly no guarantee that it will work.

We're hoping to tackle this problem soon and hopefully come up with a workable solution.