Thursday, April 8, 2010

Using Schema Comparison in visual studio 2010

Introduction

I am happy to share one of the interesting feature of Microsoft visual studio 2010, and the feature is under data named “Schema Comparison”. I am sure every now and then we need two pretty important stuff regarding database while deploy or test deploy our application’s data and those are

  • Data Synchronization
  • Schema Synchronization

In this particular post we are going to take a look at the schema comparison stuff. I am sure we have used some kind of schema comparison tool before, I my self is a big fan of red get sql belt, which has few really life saving tools and support to ease our life while working with data, but today I am bringing this because visual studio 2010 have same schema comparison option.

Process of Schema Comparison

To compare the database schema you need at least two database, this sounds stupid but still mention it you can select same data source in both target and source, which does not make sense. Any way to lunch the new schema comparison, go to top menu and find “Data” under data we have “Schema Compare” Sub menu under that we have a third level menu named “New Schema Comparison”. After selecting the menu it will bring up a new window where you would define the target database and source database. Bellow a screen shot is been given.

image

Figure: Schema Comparison Source selection

Please select both source and target database and click on “OK” to start the process after the process is been finish you would see a window like the bellow screen shot on visual studio. its simply the result and mismatch list that database have. And when you select each mismatch item you would see that required sql statement is also shown in object definition section. 

image

Figure: Schema Comparison Result

And bellow this you can find the entire script needed to update the target database, in “Schema Update Script” window. You can change the action that need to perform in the mismatch window and then click on refresh script option to regenerate the script again.

image

Figure: Schema Comparison update script

Actions to perform

You can perform the actions using the tool bar on top of the tool bar section or using menu from > data > Schema compare > [and the select any third level menu] to perform you desired action. You have the option to directly sync via write update command or perhaps you can save the generated script on a file using Export to File option. 

image

image

Figure: Schema Comparison Actions

Note

A few note before ending the post. This feature is available in Microsoft visual studio 2010 ultimate edition. which is a little bit disappointing for the developers, its such an useful tool and needed badly if we mess with data each day.