What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:
- Script the change in a SQL file and attach that to a TFS work item.
- The work is peer-reviewed
- When the work is ready for testing it the SQL is run in QA.
- When the work is ready for production it the SQL is run in the production databases.
The problem with this is that it is very manual. It relies on the developer remembering to attach the sql or the peer-reviewer catching it if the developer forgets. Sometimes, it ends up being the tester or QA deployer who discovers the problem.
A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.
Our setup: Our development shop is full of developers with a lot of DB experience. Our projects are very DB oriented. We are mainly a .NET and MS SQL shop. Currently we are using MS TFS Work Items to track our work. This is handy for code changes because it links the changesets to the work items so I can find out exactly what changes I need to include when migrating to QA and Production environments. We are not currently using a DB project but may switch to that in the future (maybe that is part of the answer).
I am very used to my source control system taking care of things like this for me and would like to have the same thing for my SQL.