Introduction
This
article explains the new tool in visual studio 2010 which most of the
users require seeing the comparison report of the 2 different database
schema (say a development and a production db) in order to get the
object that has been changed for the current releases.
In
market there are many third party tools available to overcome this
process, but in Visual studio 2010 Microsoft has given the inbuilt
functionality as a tool to compare the different schema so that the
changes can be easily compared and synced in different environment based
on the object difference. Microsoft Database server version has
provided with many features like database development, version
controlling, unit testing, code analysis, code maintenance etc.
Types of Comparison options in Visual Studio 2010
Microsoft
has provided different types of schema comparison in Visual Studio
2010. User have given option to compare the below types
· SQL Server database - Sql server 2008 or 2005 instance compare
· Database project - a project which produces .dbschema file for sql server 2008 and 2005 instance
· Server
project - a project which produces .dbschema file for sql server 2008
and 2005 instance that contains definitions for server objects and
objects in the 'master'
· Data-tier application component (DAC) project - a project that produces a .dacpac file
· .dbschema file - database or server project output
· .dacpac file - data-tier application component (DAC) project output
Step by Step Process to Compare
Step 1 - Open Visual studio 2010 by moving to Start > Program > Microsoft Visual Studio 2010 > Microsoft Visual Studio 2010.
Step 2 - Once Visual Studio is opened, go to Data a Schema Compare a New Schema Comparison
Step 3
- You will see a dialog box which requests for the source and
destination db and the properties to connect to those db servers as
below.
Step 4
- Choose the source and the destination DB servers which you want to
compare with or you can select the database file .dbschema to compare
with the source and the destination. Later on you can save these
settings as .scmp file to use it later.
Step 5
- Once you are done with the selection of source and the destination
comparison object click on the OK button, it will take some time to get
the comparison report based on the size of the data base and the objects
to get compared.
Step 6
- Once the comparison is completed the below screen will pop up which
gets the result in a new list view where we can see the difference
between the objects whether the object is same in both the environments,
or change in the environments etc. We can customize the view based on
the requirement like, to see only the changed objects or same objects in
both the schema.
Step 7 - You can see the detail result of the object changed in the below window of the list view as shown in the below image.
Step 8 - You can customize the schema comparison setting by going to the settings Data àSchema Compare a Schema Compare options.
Step 9 - The options available for the schema comparison can be seen in the below image.
Step 10
- We can update the schema at the later stage by exporting to the file
and save it by selecting Export to Editor Window as below.
Conclusion
In market many third party
tools available to handle these tasks, but from the developer
perspective to maintain the credibility of the application we can have a
check day to day and get the objects impacted and keep synced.