One the features long sought after by developers is a reliable and easy way to maintain source control on SQL Server databases. Especially on Agile projects where multiple developers are making changes to a database schema at the same time, database source control is an absolute necessity.

Fortunately, Microsoft now provides a tool to synchronize schema changes across databases and store the change history in source control. This tool is availble in Visual Studio 2010 and is called the SQL Server 2008 Database Project.

To illustrate how to use this feature, I have created a simple database schema to use for testing. Below is a screenshot from SQL Server's table diagram for the database schema I am using. There are no restrictions on the number and type of tables you can use but this schema provides a simple example to work from.

To create a new project, from within your Visual Studio Solution, add a new project and choose the SQL Server 2008 Database Project type. Name the project and click OK to add it to the solution.

Once Visual Studio finishes the setup of the project, you will see the directory structure that is automatically built for you. This contains all of the relevant information about a database including schema and settings. If you really like scripting, you can just right-click in these folders to add tables or views or whatever, make changes, and delete items.

But there is an easier way that requires no scripting whatsoever. In the Schema Comparisons folder, right click and Add new Item. Find the Schema Comparison item, give it a name, and add it to your project.

A dialog will immediately open. This dialog allows you to select where the feature will read the schema information from and where it will publish the schema information to. Since we have already set up the database using the SQL Server tools, we will choose that as our source. Then we will choose the target to be the database project we just created and click OK.

The schema comparison tool will analyze the target and the source and provide you with a nice list of changes it will make in order to synchronize the schema from the source to the target. All differences will be listed so this can be very helpful in making sure the correct changes will be made. To commit the changes, click the Write Updates button in the top left corner.

After the changes are made, you will see that your database project now has new files for each table and all other changes. If you open a table file, you will see the SQL script to build that table automatically written for you.

The nice thing about the Schema Comparison tool is that it can work both ways. In the previous example, we moved changes from our database to the database project. But the opposite can be performed as well. For instance, each time you get latest, there may be new changes checked in to the database project. You will probably need these changes to be made in your local database you develop against also. The easiest way to do this is to open the schema comparison tool and choose the database project as your source and your local database you want to update the changes to as the target.

As in the previous example, you are shown all differences between your local database and the changes checked into your database project. Click the Write Updates button to commit those changes. After the process completes, the local database will have all changes from source control.

This tool is invaluable in managing complex database changes across multiple developers. It can even be used to publish changes to staging or test databases without having to manage SQL Scripts or access logs.