I meant to say this in one of the previous threads on this topic. The conventional approach seems to be to manage a set of sql files per database (one file per table, stored proc, view, function, etc.) which when run in the correct order (constraints after tables, views after tables, etc.) will recreate the schema from scratch. The database projects in VS2010 (and earlier) are an attempt to allow developers to make whatever database changes they want using GUI tools and then generate an sql script at the end to apply those changes. It is then the responsibility of the developer to add conditions to detect whether the change has already been applied so that all scripts can be rerun (they are idempotent).
While this approach might be necessary in some circumstances (such as where you're working as part of a large distributed team or there are many projects contributing to the same database), it seems much uglier and more complicated than necessary to me. The approach of having some tool work out the changes you just made yourself just seems completely broken (you made those changes after all - you were there). The reason it seems appealing is that it is consistant with the way we typically manage code changes. The way code changes are applied is very different to the way schema changes are made (ie. we rebuild everything every time with code whereas database changes are always a delta between two schema states with some possible data migration). Developers make schema changes for specific purposes so it seems better to create a single sql file per change and include the purpose or nature of the change in the name of the file. The most successful application of this practice was to have a folder containing sql scripts with a "YYYY-MM-DD HH-MM-SS <PURPOSE OF CHANGE>" naming convention. The automated build would start by restoring the base schema version (ie. the schema that exists in production - this may just be a blank database) and then applying these scripts in collation sequence before running all integration/functional tests. This means the upgrade process is always being tested and schema changes can be freely mixed with data migrations. I'm clearly talking about something similar to rails migrations (rails didn't invent the concept but it seems to have become work popular as a result of rails) but without "down" scripts. Down scripts are only necessary if it is impossible to back up a copy of production and restore in the case of a problem (undoing lossy data migrations is a nontrivial problem). Once you've done a release and applied these changes, you can change the build to load a copy of the production database with that set of deltas already applied and move the previous deltas to an archive folder. Automation with this approach is then completely trivial and everyone's happy. I'm very curious to know why the "get the tool to work out what changes have been made" approach is so popular. Mark. On Thu, Jun 10, 2010 at 12:23 PM, silky <[email protected]> wrote: > On Thu, Jun 10, 2010 at 12:21 PM, Preet Sangha <[email protected]> > wrote: > > We had this working beautifully in this way at a place I worked in the > UK. > > DB changes where automagically red-gated to the source system, and added > to > > the auto build, deploy and test. > > Awesome. There's nothing better then everything being automated and > cool. Okay then. > > > > -- > > regards, > > Preet, Overlooking the Ocean, Auckland > > -- > silky > > http://www.programmingbranch.com/ >
