This article explains the new tool in visual studio 2010 which most of the users use to see the comparison report of the 2 different database schemas.
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.