How best to handle database refactoring within a team?

Question

We are currently usign a roll-forward approach to DB changes, akin to Migrations, where each developer creates and checks in a script that promotes the latest version of the DB to a new state. Problems arise when multiple developers concurrently work on non-trivial tasks and end up making changes to the DB that interfere with each other. The 'non-trivial' bit is significant because if the tasks take long enough, and if DB changes occurr early enough in the cycle, neither dev ends up being aware of the other's changes, and we end up with either a DB merge nightmare (preferred case) or an inadvertently broken database.

Can these situations be easily avoided? Are there database refactoring strategies that effectively handle the scenario of multiple developers actively changing the schema?

If it matters, we use SQL Server.

Answer 1

Martin Fowler and Pramod Sadalage have written an excellent article on this subject:

Evolutionary Database Design

I suggest you follow his advice, e.g. that a dedicated DBA reviews the changes and merges them into the database master, while each developer can test things on his very own database.

In my own company, we've also found that Database Comparison Tools can greatly aid this process, because they can create update scripts automatically, e.g. to update production environments.

Answer 2

Establish a Reviewing Process. Everyone who implemented a migration has to review the next migrations. Check in a single migration documentation file, in which everyone who modifies the database has to provide a synopsis of the new migration. If two people implement changes they need to be aware of, the resulting merge conflict in your CVS will alarm them.

Answer 3

http://www.liquibase.org

Quote from the front page:

You never develop code without version control, why do you develop your database without it?

Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes. It is built on a simple premise: All database changes are stored in a human readable yet trackable form and checked into source control.

Answer 4

Daily stand-up meetings prevent this problem.

http://martinfowler.com/articles/itsNotJustStandingUp.html