VSTS Database Edition Schema Comparison

Question

We use VSTS Database Edition to version control our database schemas. I like the idea of how it is supposed to work but have a big issue with the Schema Comparison feature. I like to use it for my deployments to see what has changed in the project vs a target database. Unfortunately, it shows many changes for objects that have not been changed. That happens even if I do it immidiately after re-creating a db project from the database.

The differences it finds are mostly white space. Some are caused by a different representation of the same thing, for example default constraints, "DEFAULT ((0))" vs "DEFAULT (0)". While I can go ahead and deploy the changes anyway and it does not cause any problems, it is definitely not ideal because it is hard to find actual changes when at least 50% of all objects are shown as modified. Is there any way to make this work better?

Answer 1

I keep hoping VSTS Database Edition will get more reliable. In the meantime, I still trust and use redgate's SQL Compare to diff databases. You can tell it how you want it to treat white space, comments, etc. on a very granular level.

I have been using SQL Compare around 5 years and it has always been very reliable. I have automated build scripts that use the SQL Compare engine APIs to diff our production and dev database and generate change scripts automatically.

PS. I am not affiliated with redgate. I am just stating my opinion based on my experience. Also, there is nothing wrong with rooting for the little guy.

Answer 2

The solution to this was upgrading to the Database Edition GDR R2 release, which was suggested by Mitch Wheat in his comment. I was using the standard version that came with VS2008 before. GDR release appears to have some nice usability features and it fixed all my problems with the Schema Comparison.

General info on VSTS Database Edition GDR: http://msdn.microsoft.com/en-us/magazine/dd483214.aspx.

Latest version download is here.