Terry,

I think you're doing your company a favor by having a solid process in place. Adding additional software into your process often requires your process to change without significant benefits in control or quality. Keep it simple, keep it flexible.

We also use a similar process to yours, but with some minor differences. The differences are probably based on our different organizational structure and methodology, but fundamentally the same.

Database developer sandbox.

Shared Application developer playground.

QA/Testing environment.

Production/Customer delivery.

In the database developer sandbox, the developer can go hog-wild here. When they have tested their solution, they push it into the next mode (or zone).

For tables and data, the scripts that they write are simply the diff scripts to get a table from one build to the next. We do not modify the Create table script in order to generate a change script. It's not that hard to write a change script to modify one or more tables and manage any data migration that is necessary as the result of the schema change. So I don't see the need for software to produce diff scripts. Also, we have setup a naming convention for SQL files that allow any developer to know if another has also changed the same object among the "myriad" of changes that build up over many months or years. Once a table is modified using a change script, you can easily generate the Create Table script in your sleep -- from SSMS, SMO, or other free utilities. We only use the Create table script for record-keeping purposes. All new database are created from a "clean" database backup, maintained by the DBA and created by the change scripts (after they leave the QA/Test zone). All of this is done using batch files, osql/sqlcmd, and SQL script files -- and all of the bat and sql files are also under the same version control (We use MS VSS, but it doesn't matter which one you use).

For textual objects (stored procedures, views, functions) we don't bother with Alter statements. We simply have Drop-n-Create statements in the SQL file that defines the object. The db developer simply modifies the syntax in the file and checks it back into version control, where it will be ready for the next step. The only special-cases for these, are dynamically created objects and functions used in Check or Default constraints.

We automate (batch files) pushing builds into QA/testing zone. The automation labels/marks all DB, application, and unit-testing code in source control. The labeled version is then pushe into QA/Testing. Part of the automation runs scripts to check for conformance to standards and inclusion of certain files or scripts. Because of the automation and version control, we have better consistency and quality. Automation also means we spend less than 2 minutes running and documenting a build! Additionally, our final push into Production/Customer delivery is the equivalent of the last QA-Approved build, so there's almost no additional work involved there.

Good luck, Terry.

~Marc