16 September 2005

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.


Anonymous said...

This information is very helpful to a project i'm currently working on. I just have one question for you.

We're using source control (Surround SCM) to check-in stored procedures from dev databases (SQL Server Enterprise Manager). Then during the build process, doing a GET of these sprocs and copying them to the corresponding production databases.

Is there a way to ONLY check-in sprocs that have been created/updated since the last check-in? If there is, is it possible to do a GET of only these created/updated sprocs and copy those to prod database, thus not effecting the sprocs that were not changed and saving vast amounts of time during the build process?


Jingye said...

Sorry being hugely late...
I think it will only be possible if SQL Server Enterprise Manager keeps a version information of sprocs.