How to manage and capture database changes across several developers?

Question

We have three developers and one tester all working against the same database. We change the schema of the database quite often, and every time we do it tends to have a ripple effect of headaches for everyone else.

Are there good practices in place for .NET oriented development against MS SQL Server 2008 for managing this? I am thinking something similar to Rails Migrations and each dev and tester has their own local database. Or is that overkill? It'd at least be nice to have separate test and dev databases, but currently manually keeping two databases in sync is probably worse than our current predicament.

LiquiBase seems promising, has anyone successfully used it in a similar environment? Or are there better approaches?

We are using SQL Server 2008, VS 2008 and .NET 3.5 if that matters at all.

Answer 1

We have scripts to generate DB from scratch and this is what is sitting in the source control. Each developer (20 of them) is using script to generate 2 databases on their workstations. One for “work” – manual testing with sample data in it (there is script to populate sample data). Another DB is empty and used in unit tests (open transaction – do unit test – roll back).

Unit test are mandatory in multi developer environment. Also we always build “old” database and apply schema changes on it. Each developer is changing schema by creating upgrade procedures (this is how we have rebuild and upgrade ready at the same time).

For performance testing we have “loaders” – C# code to simulate users and populate millions of records over night on developer workstations.