Migration scripts failing to be picked up by SQL Compare
ian.gull
Posts: 4
SQL Compare: 9.5.0.519
SQL Source Control: 3.0.3.15
Visual Studio 2010 Premium 10.0.40219.1 SP1Rel + Team Explorer
Microsoft Visual Studio 2008 Team Foundation Server
I've just started looking at SQL Source Control 3.0 and having a look at the new migration script feature. I've looked at version 2 in the past but always been stumped by the need to maintain data integrity when making database structure changes. Migration scripts look as though they should go a long way to solving the data issue.
However I have hit one problem which is best illustrated by the following scenario:
Created an empty database SourceControlTest and linked it to SQL Source Control and TFS. Project is also called SourceControlTest.
Created table Table_1 and commited the change to Source Control. Checked the object in TFS and it had a Changeset of 30976.
Created a second empty database DeployTest and then used SQL Compare to deploy the project SourceControlTest to it. After completion the database is tagged with the revision 30976 in sys.extended_properties. So far so good.
Edited Table_1 to add a new field and then added a migration script for the uncommitted object to insert a row into the table.
Committed the changes to Source Control and the script was created as "Database 30976 to 30977.sql". However looking at the script in TFS it was given a Changeset value of 30978. This looks a bit odd as I'd expect the script name and Changeset to refer to the same value!
Then did a refresh in SQL Compare to set it up for deploying the latest changed to the DeployTest database. This worked and listed the migration script.
Ran the deployment and table updated correctly and row inserted. The database revision was updated to 30978.
Then did a new edit on the table and again added a migration script to insert a data row.
Committed and new migration script created as "Database 30977 to 30981.sql". In TFS the script has a Changset of 30982.
The did another refresh in SQL Compare to set it up for deployment of the latest script. This time the latest migration script was not listed for deployment.
The problem seems to be that the database revision was 30978 whereas the latest migration script refered to 30977!
To prove this I manually updated the database revision in sys.extended_properties to be 30977 and did a SQL Compare refresh.
The migration script was now listed and was successfully deployed.
The problem seems therefore to be the mismatch between Changeset and script name.
If you need any more information let me know.
SQL Source Control: 3.0.3.15
Visual Studio 2010 Premium 10.0.40219.1 SP1Rel + Team Explorer
Microsoft Visual Studio 2008 Team Foundation Server
I've just started looking at SQL Source Control 3.0 and having a look at the new migration script feature. I've looked at version 2 in the past but always been stumped by the need to maintain data integrity when making database structure changes. Migration scripts look as though they should go a long way to solving the data issue.
However I have hit one problem which is best illustrated by the following scenario:
Created an empty database SourceControlTest and linked it to SQL Source Control and TFS. Project is also called SourceControlTest.
Created table Table_1 and commited the change to Source Control. Checked the object in TFS and it had a Changeset of 30976.
Created a second empty database DeployTest and then used SQL Compare to deploy the project SourceControlTest to it. After completion the database is tagged with the revision 30976 in sys.extended_properties. So far so good.
Edited Table_1 to add a new field and then added a migration script for the uncommitted object to insert a row into the table.
Committed the changes to Source Control and the script was created as "Database 30976 to 30977.sql". However looking at the script in TFS it was given a Changeset value of 30978. This looks a bit odd as I'd expect the script name and Changeset to refer to the same value!
Then did a refresh in SQL Compare to set it up for deploying the latest changed to the DeployTest database. This worked and listed the migration script.
Ran the deployment and table updated correctly and row inserted. The database revision was updated to 30978.
Then did a new edit on the table and again added a migration script to insert a data row.
Committed and new migration script created as "Database 30977 to 30981.sql". In TFS the script has a Changset of 30982.
The did another refresh in SQL Compare to set it up for deployment of the latest script. This time the latest migration script was not listed for deployment.
The problem seems to be that the database revision was 30978 whereas the latest migration script refered to 30977!
To prove this I manually updated the database revision in sys.extended_properties to be 30977 and did a SQL Compare refresh.
The migration script was now listed and was successfully deployed.
The problem seems therefore to be the mismatch between Changeset and script name.
If you need any more information let me know.
Comments
Thanks for reporting your issue.
We have tried to recreate the problem using your reproduction steps but have not been able to do so. Specifically, we have not seen the behaviour you describe in your post as "Ran the deployment and table updated correctly and row inserted. The database revision was updated to 30978". Instead, we saw the revision number we expected.
The difference may be because we are using a newer version of both SQL Source Control and SQL Compare. In fact, we released these two products yesterday - see http://www.red-gate.com/MessageBoard/vi ... hp?t=14419 and http://www.red-gate.com/MessageBoard/vi ... hp?t=14421!
Would it be possible for you to try and recreate the issue with the latest versions of the products? These can be navigated to via the above forum posts.
In repsonse to your point regarding the script name not matching the changeset number of the migration script - this is expected behaviour. The migration script revision number should be greater than the TFS changset covered by the script itself.
Thanks
Chris
Redgate Software
Thanks for your reply.
I've now installed the latest versions, i.e. SQL Compare 10.0.0.140 and SQL Source Control 3.0.4.24.
Unfortunately they haven't made any difference to the behaviour I'm seeing.
Is there anything I can do at my end that would provide you with more information on what is going on behind the scenes?
Thanks, Ian.
I'm afriad we are not able to recreate the problem by following the steps in your original post.
Could you possibly try the same steps using an evaluation repository (rather than connecting to your existing TFS server) - it is an option available from Link Database to Source Control dialog? If it does work with the evaluation, is the repository type the only difference in the steps you used to display the issue?
Best regards & Merry Christmas,
Chris
Redgate Software
Using the evaluation repository rather than TFS works fine using the same scenario. The only difference therefore in in the repository.
Regards, Ian.