I've just been listening to the .Net Rocks, ORM Smackdown show with Oren Eini and Ted Neward. It was a very interesting show with a couple of bright people who know their technology. There was one theme that came up a few times that I find nagging.
Oren referred to the point where you are ready to ship V1 of your product, at which point the way you manage your database has to change. He inferred that once you have deployed your application and you have production data in it then your database should be relatively stable and the changes should be minimal.
I find this perspective at odds with the agile philosophy of getting your product to the customer early with a subset of functionality and then iteratively adding capability. Maybe I am just not understanding the agile process, but I don't think that "here's your V1 product, please don't ask for too many changes now" was the message that the Agile Manifesto was trying to convey. Neither do I subscribe to the belief that the customer can really get involved without using software "in production". Sorry, but I've seen customers try and evaluate software and it is not usually very effective. I guess it depends who your target customer is, but many of us write software for people who are not software experts.
We need a process for managing change in a database that will work from day 1 of developing and continue to work when doing maintenance work 10 years down the road. Here at Tavis Software we use a process that is quite different from the usual "DB Diff generates change script" upgrade process that I see most people using. We developed a tool years ago that would generate an XML metadata file that contains individual scripts for all of the database objects. An upgrade tool would then take that metadata and build an empty database with no constraints or triggers, migrate the data from old version of the database to the new version and then add the triggers and constraints. This upgrade process has worked extremely well for over 150 upgrades of real production data.
There are definitely pros and cons to our approach to upgrades, but it seems more compatible with the iterative agile development processes than the DB Diff/Change Script method, at least it appears that way if I understand what Oren is saying.