How do you track database changes in source contro

Question

We use SQL Server 2000/2005 and Vault or SVN on most of our projects. I haven't found a decent solution for capturing database schema/proc changes in either source control system.

Our current solution is quite cumbersome and difficult to enforce (script out the object you change and commit it to the database).

We have a lot of ideas of how to tackle this problem with some custom development, but I'd rather install an existing tool (paid tools are fine).

So: how do you track your database code changes? Do you have any recommended tools?

---

Edit:

Thanks for all the suggestions. Due to time constraints, I'd rather not roll my own here. And most of the suggestions have the flaw that they require the dev to follow some procedure.

Instead, an ideal solution would monitor the SQL Database for changes and commit any detected changes to SCM. For example, if SQL Server had an add-on that could record any DML change with the user that made the change, then commit the script of that object to SCM, I'd be thrilled.

We talked internally about two systems: 1. In SQL 2005, use object permissions to restrict you from altering an object until you did a "checkout". Then, the checkin procedure would script it into the SCM. 2. Run a scheduled job to detect any changes and commit them (anonymously) to SCM.

It'd be nice if I could skip the user-action part and have the system handle all this automatically.

Answer 1

I just commit the SQL-alter-Statement additional to the complete SQL-CreateDB-statement.

Answer 2

If you are using .Net and like the approach Rails takes with Migrations, then I would recommend Migrator.Net.

I found a nice tutorial that walks through setting it up in Visual Studio. He also provides a sample project to reference.

Answer 3

We developed a custom tool that updates our databases. The database schema is stored in a database-neutral XML file which is then read and processed by the tool. The schema gets stored in SVN, and we add appropriate commentary to show what was changed. It works pretty well for us.

While this kind of solution is definitely overkill for most projects, it certainly makes life easier at times.

Answer 4

Rolling your own from scratch would not be very doable, but if you use a sql comparison tool like Redgate SQL Compare SDK to generate your change files for you it would not take very long to half-roll what you want and then just check those files into source control. I rolled something similar for myself to update changes from our development systems to our live systems in just a few hours.

Answer 5

In our environment, we never change the DB manually: all changes are done by scripts at release time, and the scripts are kept in the version control system. One important part of this procedure is to be sure that all scripts can be run again against the same DB the scripts are idempotent?) without loss of data. For example, if you add a column, make sure that you do nothing if the column is already there.

Your comment about "suggestions have the flaw that they require the dev to follow some procedure" is really a tell-tale. It's not a flaw, it's a feature. Version control helps developers in following procedures and makes the procedures less painful. If you don't want to follow procedures, you don't need version control.

Answer 6

In SQL2000 generate each object into it's own file, then check them all into your source control. Let your source control handle the change history.

In SQL 2005, you'll need to write a bit of code to generate all objects into separate files.