Dealing with Database changes between version bran

I have an ASP.NET project. Naturally, through different releases and development branches, the db schema changes.

What are some ways to cleanly handle the schema changes in a friendly way so that I can easily switch between development branches?

I use SQL Server 2005, but general techniques probably work.

回答1:

One good way to keep track of schema changes across multiple branches of a development project would be to follow a database refactoring process. Among other benefits, this sort of process incorporates the use of delta and migration scripts to apply schema changes to each environment (or branch in your case). The setup could look something like this:

main
  src <-- ASP.NET project source
  db <-- Database create scripts
  delta <-- Database change scripts (SQL delta files)

branch
  src
  db <-- usually has the same contents as the copy in main branch
  delta <-- only the changes necessary for this branch

Every time you need to change the database schema for a particular branch you create a SQL delta script that is used to apply the change. To make it easier I would suggest naming each script file to include create date and time to keep them in sequence. Example would be:

201102231435_addcolumn.sql
201102231447_addconstraint.sql
201103010845_anotherchange.sql

Add the delta files to source control in the branch where the schema change needs to be made. You should end up with each branch containing exactly what is necessary to change the corresponding database. Some details might need to be tweaked for your situation depending on things like your branching scheme and whether or not your database is preserved during your release process (as opposed to re-created).

Finally, to try and make these concepts simple, I would recommend a tool to help manage the process. My suggestion is to take a look at DBDeploy / DBDeploy.NET. I've been happily using it for years on all my projects.

回答2:

We put our schema changes in source control in the same place the rest of the code being deployed for that version is.