How can a group track database schema changes?

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database. A possible structure (example in MySQL) is:

create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;

insert into schema_version(key, extra) values ('001', 'schema version');

Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

…

I think the best method is to have the database generated as part of your build process. Keep all of the scripts in source control with the rest of the code, and everyone is responsible for their own environments.

Failing that, RedGate has a tool to integrate source control into SSMS and SQL Compare is useful for comparing/synchronizing MS SQL Server schemas. Visual Studio Database Edition also has a built-in schema compare tool.

Another SO question lead me to Migrator Dot Net which I am going to begin investigating during my copious free time. It looks like a good method, but it might be more of a time/overhead investment than you are willing to make.

eiefai already mentioned A table that should exist in all projects with a database. This is a great blog post, but IMO it only goes part of the way to a working solution for database revision control. I think any attempt to "answer" this question in the real world needs to consider some of the other information about VCS and databases:

• The Agile Data methodology
• The "Database Refactoring" book
• Martin Fowler's article on "Evolutionary Database Design"
• The Liquibase tool
• A decent video with Scott Ambler presenting his thoughts on "Agile Databases"