Warning: This post contains corny humor. Only read if you are well caffeinated!

One of the things that I always struggled with has been how to keep source code "NSync" with a database (LOL). It is common for people to use source control for application code but it seems like a lot of people do not have the same habits when it comes to their database schema. To me this is a major problem, especially when you consider that people put business logic into stored procedures and functions that make up a part of the overall application.

Usually when you develop an application that is database dependent, you are modifying your database schema in parallel with your application code. Each time you make changes to the application you will generally find yourself updating the database schema as well. If your database is not in sync with your application code then chances are that it will not run properly. So doesn't it make sense to have your schema changes linked to your application build numbers? In my opinion, this is an absolute requirement!

So how can this be achieved? Well to date, I have found only one viable solution. I will share my strategy with you and if you disagree or have a better way to fulfill the requirement then please voice your opinion by leaving a comment:

  1. In your project create a folder to store your database scripts. It doesn't matter what it is called as long as you are consistent and it makes sense to other people on your team.
  2. The first time you check in your code script out the entire database. Almost all database vendors have tools that you can use to script out the database. Visual Studio Team System for Database Professionals tends to generate a separate SQL file for each object in the database. Since my process is database agnostic I chose not to go this route. I used SQL Server Management Studio to script out the database instead.
  3. When developing your code you will need to track the changes made to your database. There are two ways to do this:
    • Manually Tracking Database Changes - Each time you modify the database you can add the DML/DDL statements to a text file. For example if you add a new column to  a table you would add the alter table add column statement to the text file.
    • Use 3rd Party Tools to Generate Delta Scripts - Visual Studio Team System 2008 Database Edition has a feature which will let you create a delta script automatically for SQL Server databases. This is done by comparing two databases and generating a bunch of SQL statements to get the databases in sync. If you don't have a Visual Studio license then you can use OpenDBDiff on CodePlex instead. If you are using something other than SQL Server then chances are you can find a tool to generate a delta script. I did a search for Oracle comparison tools and I was provided with a laundry list of options. If you have any experience with these other tools then please leave a comment.
  4. Every time you check in code you generally will increment your build number. Use the build number to name your SQL delta file. If you are using TFS then you could opt to use a label in place or in conjunction with renaming the file. The important thing is to have a mapping between your application build number and your database changes.

Finally, if you are going to use Visual Studio Team System 2008 Database Edition while developing against a SQL 2008 database then you will need to download the GDR patch. However, I will warn you that they changed the tool quite a bit with the GDR patch. They actually modified the tool so you can use it without having to have a local SQL instance to host the "Design Database". You can read more about this here.

Admittedly, this is not a perfect solution. There is some labor involved and I would like to find a more automated solution as I move forward. Perhaps one idea is to produce a script which would pass command line arguments to a executable in the "post-build" event of the IDE. This could automatically generate the delta file and rename it to match the build number.

Finally, do you store your database changes in source control? If so, how are you managing the changes? Please let me know.