Grant Fritchey (8/23/2010)
The one thing that I see missing from this process is any hint that you're using some type of source control. As a matter of fact, I think a lot of your process, the naming & storage standards in particular, is built up because you're not using source control. At the root of it, this process is not dissimilar to the one we use, but because we have all our scripts stored in source control, in general, all we have for deployment is a listing of which scripts, from source control, by label, need to be run. This allows us to integrate our database deployments very tightly with the application deployments so that for any given application release, we have a well matched database release.
For this, I agree with you, but we (for better or worse) leave the source control up to our development teams. Since my team is primarily production support, we probably don't store scripts in the best way possible. With this folder layout, I was simply trying to provide a way that would make sense for purposes of the example.
Also, I'd suggest changing your QA process so that you only ever do full deployments to it. If you build some type of production rollback process, so that prior to a deployment you reset the QA databases to what production looks like, you'll get a better test of the deployments before you go to production. That will help to eliminate a lot more errors that creep in because of people introducing changes to script outside of the process. This also would be helped by having the scripts in source control.
Generally speaking, we do go with the full deployment in a lower environment tested against a copy of production. On occasion, we have last-minute updates come in, and we simply append them to the release because we already know the previous scripts were successful, so we only need to verify the implementation and rollback for the addition updates. Instead of trying to rebuild the full release, adding the implementation and rollback for the added changes logically works in our organization, but it may not in yours.
Thanks for taking the time to check out the article. 🙂