Automated SQL Server database objects scripting and deployment in continuous integration environment

The initiatives

In the normal development lifecycle, database objects (tables, views, stored procedures and etc) evolve as implementation progressed. The despaired changes in each developer’s environment need to aggregate into the central CI build server then cascade to each developer’s environment. Normally this is done manually as there is at lack of source control mechanism for database objects.
What we would like to have is an automated db objects scripting and release processes that we can plug into CC.Net or NAnt build process.

The Requirements:
Iteration one:
1) Automated (SQL Server) database scripting (DBGen.sql)
2) Version control DBGen.sql. – Only update source/roll out changes when new changes are made.
3) Assuming only one development machine is making changes to DB – so there is no need to consider builder server to development machines synchronisation.

Iteration two: SynchronisationSynchronous build server with development machines in a controlled way, i.e. only when a local development tasks has completed (build and tested) and the developer do a ‘Get Latest’ to sync the source code. The contrary to this is whenever changes to build server db is made and tests has been successful, using SQL backup/ replication/publish/subscription (?) mechanism to roll out the changes immediately.

Story break down (iteration one)
1. Post Build Event.
DevEnv (Vs.net) uses post build event to trigger user database schema objects (tables, views, store procedures etc) scripting.
1) Post-build event command line that kicks off the db object scripting. This can be done in two ways:
a. Scpriting (WScript/CScript) with ActiveX object SQLDMO. This option is more fine grain control on what objects to script.
b. Command line executable using SQL Server upgrade facility. Scripts entire database in one go. (Scripting Database Objects has detail introduction.)
2) Check file size (not sum size) on the generated DBGen.sql, auto check out the source control version if there are difference, - but do not check it in yet.

(Developer checks in all changes)
2. Release decision.
Decide whether there is a need to release new db schema by checking the source safe version and time stamp. This process needs to be built into Nant build process.

3. Run DBGen.sql
(If release required) DBGen.sql is executed by Nant using WMI and blah blah blah…
DBGen.sql is run after successful build, before unit tests.