Database Projects in Visual Studio 2010, Part 2

This continues the series of posts about the Database Projects in Visual Studio 2010. In the first part I introduced you to the Database Project. In this part I’ll explain how Schema Comparisons can be used to synchronize database schemas.

Why?

During database projects there are often (at least should be) multiple copies of the same database, for example:

• Production
• Testing
• Multiple development copies

When a change to the database structure is made, it’s important to synchronize the changes in a managed way. Schema comparison is a powerful tool, that allows you to copy the changes, no matter where they were made.

Creating a local copy of the database for developer

Let’s say you are a new developer in a project. You have just pulled the project out of the version control and have the latest offline copy of the project’s database in the Solution Explorer. Now you want a local instance of the database for development and testing purposes.

• Add a new Schema Comparison to the database project by right-clicking on the project and locating the Schema Comparison… under the Add -sub-menu. Select the item and Add:

• Immediately, another dialog appears where you can select the Source and Target –databases:

• The interesting parts are the Project, which contains the offline copy of the database and the Database –part, which points to a new or existing database connection in SQL Server. The changes can be synchronizes between any of these.
• In this case, however, we want to create a local copy of the database in SQL Server from the offline copy. This means that the Source Schema should be Project (NorthwindDB in this case) and the Target Schema should be a new database connection.
• Select New Connection… and enter the connection details:

• I have a local, named SQL Server 2008 R2 instance called “.\SQL2008R2”, so I point to it. As the target database doesn’t exist yet, I have to type the name manually (“Northwind_dev”, for example).
• After pressing “OK”, Visual Studio warns the db doesn’t exist, but after confirmation it can create one.
• You can also create an empty database in Management Studio and connect to it, if you want better control with how and where the database will be created.
• Now that the connection details are in place, Press OK.
• Optional: Before selecting OK, you can set Schema Compare Options with the Options… –button. In addition to the default values, I ignored SQL Files (filename conflicts due to an existing same DB), Role Memberships and Users.
• Press Ok, until you get this window with Schema Comparison results:

• As you can see from the screenshot, you can now review all the changes (Create/Update/Drop) and skip them, if you want.
• From the toolbar you can either Export the whole script to a Query Editor or file or Write Updates directly, which I’ll select this time.
• After executing the Write Updates, I have now an empty copy of the database in my local SQL Server.

Synchronizing Schema Changes

Now that you have a copy of the database, what happens if any schema changes? For example, if you have to modify columns in the development database due to requirements, you need to propagate the changes to the project and later to the test and production databases.

Let’s say I add a new table called “News” to the Northwind_dev –database and want to bring the changes to the offline copy.

• First, I’ll do the changes directly to the database using the Management Studio:

• Then, I’ll open the Schema Comparison –window (by double-clicking the .scmp-file in the Solution Explorer) and define the new parameters: Source: Database/Northwind_dev and Target: Project/NorthwindDB. After clicking OK, the comparison finds the new table:

• After writing the updates, you can see a new script file in the Solution Explorer:

• Now you can check-in the modified database schema along with other files in the changeset.
• The modified database can then be picked up by other team members and changes can then be applied to other databases.
• You could also write the changes to a script file and hand them to the DBA, who can then run the modifications on test and development databases - after validating the scripts.
• Schema Comparison works also between SQL Server/database instances directly, so you don’t necessarily have to maintain offline copy of the database schema. However, keeping it in the version control makes tracking and canceling changes later much easier.

Conclusion

This post was about Schema Comparison and how the tool can be used to copy databases and manage the schema changes. Synchronizing the offline copy allows you to version control the changes in a managed way.

In the next part I’ll tell you how to copy data with Data Compare and how to generate sample data.