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

We basically do things the way you are, with the generation script checked into source control as well. I'm the designated database master so all changes to the script itself are done through me. People send me scripts of the changes they have made, I update my master copy of the schema, run a generate scripts (SSMS) to produce the new DB script, and then check it in. I keep my copy of the code current with any changes that are being made elsewhere. We're a small shop so this works pretty well for us. I realize that it probably doesn't scale.

Answer 3

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

Answer 4

If you are not using Visual Studio Database Professional Edition, then you will need another tool that can break the database down into its elemental pieces so that they are managable and changeable in an easier manner.

I'd recommend seriously considering Redgate's SQL tools if you want to maintain sanity over all your database changes and updates.

• SQL Packager
• SQL Multi Script
• SQL Refactor

Answer 5

Use a tool like RedGate SQL Compare to generate the change schema between any given version of the database. You can then check that file into source code control

Answer 6

Have a look at this question: dynamic patching of databases. I think it's similar enough to your problem to be helpful.

Answer 7

have a look at DB Ghost - you can create a dbp using the scripter in seconds and then manage all your database code with the change manager. www.dbghost.com

This is exactly what DB Ghost was designed to handle.

Answer 8

My solution to this problem is simple. Define everything as XML, and make sure that both the database, the ORM and the UI are generated from this XML, no exceptions. That way, you can use code generation tools to quickly regenerate the database creation script, which will alter your schema while (hopefully) preserving some data. It takes some effort to do, but the net result is well worth it.