How to keep code base and database schema in synch?

Question

So recently on a project I'm working on, we've been struggling to keep a solution's code base and the associated database schema in synch (Database = SQL Server 2008).

Database changes occur fairly regularly (adding columns, constraints, relationships, etc) and as a result it's not uncommon for people to do a 'Get Latest' from source control and find that they also need to rebuild the database as well (and sometimes they forget to do the latter).

We're not using VSTS: Database Edition (DataDude) but the standard Visual Studio database project with a script (batch file) which tears down and recreates the database from T-SQL scripts. The solution is a .Net & ASP.net solution with LINQ to SQL underlying as the ORM.

Anyone have ideas on an approach to take (automated or not) which would keep everyone up to date with the latest database schema?

Continuous integration with MSBuild is an option, but only helps pick up any breaking changes committed, it doesn't really help in the scenario I highlighted above.

We are using Team Foundation Server, if that helps..

Answer 1

We try to work forward from the creation scripts.

i.e a change to the database is not authorised unless the script has been tested and checked into source control.

But this assumes that the database team is integrated with your app team which is usually not the case in a large project...

(I was tempted to answer this "with great difficulty")

EDIT: Tools won't help you if your process isn't right.

Answer 2

Ok although its not the entire solution, you should include an assertion in the Application code that links up to the database to assert the correct schema is being used, that way at least it becomes obvious, and you avoid silent bugs and people complaining that stuff went crazy all of the sudden.

As for the schema version, you could use some database specific functionality if available, but i personally prefer to declare a schema version table and keep the version number in there, that way its portable and can be checked with a simple select statement