Most DBAs work with different environments such as production, quality assurance and development where the same database schema exists. One of the biggest issues we have been facing while maintaining the same database schema in various environments is making sure that the objects exist in the databases are the same in all environments and if not, finding the differences and applying them to the relevant environments. How you have been doing this? Did you do it manually (imagine doing it manually for databases with hundreds of objects; definitely it is a nightmare). Did you use any third-party tools? You may have. There are many tools that have superb facilities to address this issue but do you know that Microsoft has a tool that we can use to address this issue. It is called Visual Studio 2005 Team Edition for Database Professional (a.k.a. Data Dude). It was released somewhere around December 2006 and not only addresses this issue but also provides many additional facilities for us. This article speaks about a feature that has come with Data Dude which is “Schema Compare”. This article will cover:
- Setting up the database schema with Project Model
- Comparing two databases schemas and examining the differences
Setting up the database schema with Project Model
Setting up a database project is required for comparing two databases schemas. Once created, the comparison can be done between the project and a database, or between totally different databases. Let’s simply create a database project for “AdventureWorks” database by follow the steps below:
- Open “Business Intelligence Development Studio” and create a new project. Select the project type as “Database Projects -> Microsoft SQL Server”. Select the template as “SQL Server 2005 Wizard”.
- Name the project as “AdventureWorksSchema” and set a location for the project.
Click “OK” to continue.
- The “New Database Project Wizard” opens. Click on “Next” in the “Welcome” screen.
- “Project Properties” screen opens. Type “Sales” in the given input box and leave other settings default.
Click on “Next”.
- Accept the default settings in the “Set Database Options” and click on “Next” to continue.
- “Import Database Schema” screen opens. Check “Import exiting schema” and set the “Source database connection” to the “AdventureWorks” database.
Click on “Next” to continue.
Continues…