Migration scripts not included in deployment scripts

dwade

Context: SQL Source Control 3.0 (trial), SQL Compare 10.0 (trial), SourceGear Vault, SQL Server 2008 R2, and Windows Server 2008 R2. I am using a fresh copy of a production database that is restored on a development SQL Server (I'll refer to it as WidgetDevDB)

In order to test migration scripts (great idea by the way) I added a check constraint to an existing column that requires the first character to be a 'v'. Next I created a migration script for that checkin. In the migration script I added an UPDATE statement just before the ALTER TABLE command to make sure all of the column values began with a 'v' prior to the check constraint being applied.

After creating and checking in the migration script I made other checkings that included other schema hanges. Now off to test creating deployment scripts.

For the first test, I restored a second copy of the production database on the development SQL Server (named WidgetDevDB2). In SQL Compare I generated a deployment script using the "Latest (Head)" version of the source controled database as the Source and using WidgetDevDB2 as the Target database. When SQL Compare listed the differences, it showed that there was migration scripts on the appropriate table and I was able to right-click and view the migration script as expected. I checked all objects and then used the Deployment Wizard to generate the deployment script. And as expected the migration script was utilitized in the deployment script just as SQL Compare indicated it would be.

Well, that wasn't a real world test because in our environment I am not able to connect to the production SQL Server (it is offsite at a customer's loction). So for the second test I used SQL Compare to generate a deployment script between two different versions of the source controlled database. I selected the "Latest (Head)" version as the Source and I selected the original checkin version (9165) as the Target. I verified that SQL Compare listed all the appropriate schema changes and that all were checked. Next I launched the Deployment Wizard and it showed me the list of migration scripts (mine was listed and was checked). It also showed be the nifty little graphic that shows the deployment script would be generated by SQL Compare for 9165 -> 9167; Migration Script for 9167 -> 9168; and SQL Compare for 9168 -> 9181. Everything looked good at this point. So I clicked "Next" and then "Open Script in Editor". It popped open my new deployment script in SQL Server Management Studio but unfortunately the script that was generated included everything except UPDATE statement that I added to the migration script. It appears that the entire script (9165 -> 9181) was generated by SQL Compare and it skipped using the migration script even though every indication was that it would.

Brian Donahue

Hi,

I've had a look into reproducing this and can't think of an obvious reason how this could happen. Unless you did forget to commit the migration script to source control, or the .migrationScript file is committed and the .sql file is missing.

dwade

Thank you for the reply. I verified that both the migration script (.sql) and the .migrationScript files are checked into SourceGear Vault. I don’t believe this is a source control issue because both my first test and my second test were performed using the “Latest (Head)” version out of Vault as the Source. Also, in both tests, the SQL Compare tool indicated visually that it would be using the migration script and SQL Compare allowed me to view the migration script. The issue is that under my first test SQL Compare actually included the migration script in the generated deployment script, and in the second scenario it skipped using the migration script when generating the deployment script (even though it had visually indicated it would use it). The only difference I can discern is that my first test used a SQL Server database as the Target and the second test used a source controlled version as the Target.

Please clarify what you meant by “I’ve had a look into reproducing.” Does that mean you tested the scenario and that SQL Compare actually used the migration scripts when generating a deployment script to go from one source control version to another?

Brian Donahue

It works when you are comparing source control->source control.

Brian Donahue

But even if you are comparing to live, it has picked up the metadata about the migration script or it would not have come up saying you have a migration script. So it's a complete mystery why it used the comparison-generated script instead of the migration script. Unless the migration script was not on disk.

When you compare a live database, it puts the migration script location in an extended property of the database.

dwade

O.k. I will start with a clean slate and retry the scenario from scratch when I get a chance. I'll post the results once I'm finished.

Brian Donahue

I just emailed a link to a patch to SQL Compare that should fix this issue.

dwade

Thank you, Brian. The patch you sent me (SQL Compare Pro v10.0.0.162) did resolve the issue I was experiencing. The migration scripts are now being included when I generate a deployment script using source control versions for both the source and target.