Continuous Integration and the Automated Database Update Process

Summary:

Developers must have good feedback to ensure productivity. Most shops have a continuous integration (CI) build process that allows the developer to quickly know if a build failed, and some shops have an automatic deploy of the CI build to provide website feedback. Lance Lyons writes about an approach to automating the update of databases in a CI environment.

Developers must have good feedback to ensure productivity. Most shops have a continuous integration (CI_ build process that allows the developer to quickly know if a build failed, and some shops have an automatic deploy of the CI build to provide website feedback. However, most shops don’t have an automated update for databases in the CI environment. Without this database automation, the website CI is incomplete or requires a database administrator (DBA) to constantly update the database as developers hand over new changes. In this article, I write about an approach to automating the update of databases in a CI environment that we currently use at Onlife Health Inc., a Brentwood, TN-based subsidiary of Blue Cross Blue Shield of Tennessee that specializes in personal health coaching.

Database Under Version Control
With any database update process—particularly an automated one—strong version control of the database objects is needed. To achieve this, you need to script out the individual databases into their separate parts like tables, views, stored procedures, functions, etc. There are a number of tools that can do this. The tool that we use at Onlife Health for generating these scripts is Red Gate SQL Compare, as it has the ability to script out an entire database and a command-prompt version of its SQL Compare tool. This makes it an excellent tool for performing comparisons and database syncs in an automated environment.

Once you have scripted out the database, the individual parts will be version controlled in the subversion repository as a complete database set. In order to effect any change to a database’s set of objects, the developer or DBA will modify some part of the set of database’s objects using a traditional edit-and-commit development process.

Database Object Structure in Subversion (or Any Source-code Provider)
The typical structure for databases that have been script out is shown below.

\database_name\
\Functions
\Post-deploy
\Pre-deploy
\Stored Procedures
\Synonyms
\Tables
\Views

The \Post-deploy and \Pre-deploy folders are for custom scripts that alter or add data to the database. Pre-deploy scripts are those changes that need to happen before the schema is changed. This might be to format data before its type is changed or to create a temporary table to store data before a table is changed or dropped. The Post-deploy folder is for all other data changes. Ninety-nine percent of all data changes will consist of scripts in the post-deploy folder.

Database Version
A table to track version updates is used to monitor the version number of database objects. This version number information will be stored in a _dbversion table that will contain the build version identifier as well as an AppliedDate field to give some historical indication of when the database was updated and to what levels. This _dbversion table becomes one of the tables of the database and an example of this table is provided below.

CREATE TABLE [dbo].[_DBVersion](
[BuildVersion] [varchar](50) NOT NULL,
[AppliedDate] [datetime] NOT NULL,
[Application] [varchar](50) NOT NULL,
[DBName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[_DBVersion] ADD CONSTRAINT [DF__DBVersion_AppliedDate] DEFAULT (getdate()) FOR [AppliedDate]
GO

CI Update of Databases
Now that I have explained the basics, let’s discuss how we use the checked-in database schema to keep databases updated in a continuously integrated environment. Let us also assume that the environment name is INT (short for “integration”) In this INT environment, we may have a number of website applications we are building and deploying in a CI fashion. In the background are databases connected to many of these applications to form a complete solution. Since the website applications are already automatically updated, we also need to update the databases, including schema and data.

In our case, we are going to assume that an initial database already is set up for the website application. A DBA or developer may have created this, and it might be complete with data and schema for that moment in time. In any case, the level of the first database deployed in INT should be reflected in the scripted source that is checked in. In other words, a comparison of the database that is deployed and the checked-in version should be the same.

Now, similar to the way we build websites continuously using tools like TeamCity, we also can set up database CI projects to call NAnt scripts that will compare an existing database to a checked-in version of database scripts. If the scripts detect differences, then the process will update the target database to a version that reflects what is checked in. We implement data changes by grouping them into two groups, those that need to happen before the schema update and those that happen after.

The general step-by-step process for the CI database update would then be:

TeamCity detects any database source code changes.

Check out the database source for the database we are updating.

Remove the prior build’s pre- and post-build data scripts from the execution path.

Run pre-deploy scripts for pre-schema update data changes.

Run SQLCompare.exe to perform a comparison of the script source to the existing database, and sync the existing database.

Run post-deploy data scripts in alphabetical order.

On success, write the version number to the _DBVersion table.

Syncdb.build script
At the core of the database CI is the syncdb.build script. This is an NAnt script called by the TeamCity build system. We will discuss the different targets in this script and how it achieves the general steps to update the CI Database. (The full script is available at the end of this article.)

An example of the NAnt parameters used to call Syncdb.build from TeamCity is:

"-D:deploy.environment=INT"
"-D:build.dir=%teamcity.build.checkoutDir%"
"-D:sql.scriptsourcepath=%teamcity.build.checkoutDir%\dbname"
"-D:runsql=true"
"-D:sql.database=dbname"
"-D:sql.password=keepItOutOfTheNantScript"
"-D:sql.scriptfilepath=e:\syncDbfolder"
"-D:build.version=%env.BUILD_NUMBER%"

When TeamCity makes the call to the syncdb.build script, the target it calls is syncdbs. This target essentially executes the other necessary targets in the proper sequence.

</target>

In this scenario, updateversion is called after successful execution of runpresql, movePrePostDeployFoldersOut, comparesyncdbs, movePrePostDeployFoldersBack, and runpostsql in that order.

The targets of importance in syncdb.build are runpresql, comparesyncdbs, runpostsql, and updateversion.

Runpresql
Runpresqlwill apply all scripts that are checked into the \Pre-deploy folder to the database being updated.

<target name="runpresql" >

<foreach item="File" property="sqlscriptname" >

<in>

<items basedir="${sql.scriptsourcepath}">

<include name="**\Pre**.sql" />

<exclude name="**\Post-**" />

<exclude name="**\archived\**" />

</items>

</in>

<do failonerror="true">

<if test="${runsql=='true'}" >

<echo message="Running Script ${sqlscriptname}" />

<echo message="sqlcmd -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i ${sqlscriptname} -I" />

<exec failonerror="true" program="${sqlcmd.exe}" commandline='-b -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i "${sqlscriptname}" -I' />

<property name="HasSQLScriptRun" value="true" />

</if>

</do>

</foreach>

</target>

Comparesyncdbs
This is the meat of the entire process. In this target, we compare the existing database to the checked-out version of the database source using Red Gates’s sqlcompare.exe command line. If there are any differences in schema, then the existing target database is updated. In addition, a report of the changes is generated along with a script that applies the same changes.

<target name="comparesyncdbs">

<echo message="Start the update db process" />

<delete file="${sql.syncfilename}" if="${file::exists(sql.syncfilename)}" />

<delete file="${sql.reportfilename}" if="${file::exists(sql.reportfilename)}" />

<echo message="Calling sqlcompare.exe to sync db's " />

<choose>

<when test="${runsql=='true'}" >

<echo message="comparing and syncing schema" />

<exec program="${sqlcompare.path}\sqlcompare.exe" failonerror="false" resultproperty="returncode" verbose="true" >

<arg value="/scripts1:${sql.scriptsourcepath}" />

<arg value="/database2:${sql.database}" />

<arg value="/server2:${sql.server}" />

<arg value="/username2:${sql.user}" />

<arg value="/password2:${sql.password}" />

<arg value="/sync" />

<arg value="/ScriptFile:${sql.syncfilename}" />

<arg value="/report:${sql.reportfilename}" />

<arg value="/ReportType:Simple" />

<arg value="/options:ForceColumnOrder,IgnoreUsers,IgnorePermissions,IgnoreWhiteSpace,IgnoreUserProperties,IgnoreCollations" />

<arg value="/exclude:User" />

<arg value="/exclude:Role" />

<arg value="/exclude:Schema" />

<arg value="/exclude:Synonym" />

</exec>

<echo message="error return code = ${returncode}" />

<fail if="${returncode !='63' and returncode !='0'}">Sync failed </fail>

</when>

<otherwise>

(Do not sync)

</otherwise>

</choose>

<if test="${file::exists(sql.syncfilename)}" >

<property name="HasSQLScriptRun" value="true" />

</if>

</target>

Runpostsql
Data changes that are applied after schema updates—which are approximately 99 percent of all data updates—are applied to the target database in this area. These data changes are checked into the \Post-deploy folder of the database SVNrepository. The scripts are run in alphabetical order so sequencing can be done.

<target name="runpostsql" >

<foreach item="File" property="sqlscriptname" >

<in>

<items basedir="${sql.scriptsourcepath}">

<include name="**\Post**.sql" />

<exclude name="**\Pre-**" />

</items>

</in>

<do failonerror="true">

<if test="${runsql=='true'}" >

<echo message="Running Script ${sqlscriptname}" />

<echo message="sqlcmd -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i ${sqlscriptname} -I" />

<exec failonerror="true" program="${sqlcmd.exe}" commandline='-b -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i "${sqlscriptname}" -I' />

<property name="HasSQLScriptRun" value="true" />

</if>

</do>

</foreach>

</target>

Update version
This target is executed at the end of the update process to add the version number to the _dbversion table. This version is essentially the SVN revision of the database source and provides some history of when a database was updated with a cross reference into the SVN repository—what the updates were.

<target name="updateversion" >

<if test="${runsql=='true' and HasSQLScriptRun=='true' }" >

<sql

connstring="Provider=SQLOLEDB;Data Source=${sql.server}; Initial Catalog=${sql.database};User Id=${sql.user}; Password=${sql.password};"

transaction="true"

delimiter=";"

delimstyle="Normal"

print="true"

output="${sql.scriptfilepath}\results.txt"

insert into _dbversion values ('${build.version}', getdate(), 'CIUPDATE','${sql.database}');

</sql>

</if>

</target>

Final Word
In a truly integrated CI environment like the INT environment we use today at Onlife Health, not only are the website applications automatically built in a CI fashion and then subsequently deployed as live websites, but also the live connected databases are kept up to date in an automated fashion. This allows development teams to get the ultimate feedback by seeing their changes in live, updated application websites and databases. This database update approach can also be extended to include build-to-build migration scripts for deploying applications and their related database updates to any environment. I discuss this concept and approach in an upcoming article titled “Database Versioning and Update Automation.”When using both in tandem, the earlier CI database update also provides feedback on how the set of database changes will integrate, before a full release build and subsequent deploy have taken place.

Complete NAnt Syncdb.build Script

<?xml version="1.0"?>

<!-- =================================

Project: syncdb.build

Author : Lance Lyons

Onlife Health Solutions

This script will create the blank model database on the blank model database server as well as create the migration script from the prior build to the current build.

Required inputs:

sql.database

sql.user

sql.password

sql.scriptsourcepath

sql.scriptfilepathname

sql.reportfilepathname

================================= -->

<project name="syncdb" default="donothing">

<target name="donothing">

</target>

<include buildfile="publishing.include.build" />

<loadtasks assembly="C:\nantcontrib-0.85\bin\NAnt.Contrib.Tasks.dll" />

<property name="sqlcmd.exe" value="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" />

<property name="sql.database" value="" readonly="false" />

<property name="sql.user" value="app_AutomatedBuild" readonly="false" />

<property name="sql.password" value="" readonly="false" />

<property name="sql.server" value="int-sql01" readonly="false" />

<if test="${deploy.environment == 'DEV'}" >

<property name="sql.server" value="dev-sql02,54552" readonly="false" />

</if>

<if test="${deploy.environment == 'DEV2'}" >

<property name="sql.server" value="dev-sql02,54552" readonly="false" />

</if>

<property name="HasSQLScriptRun" value="false" />

<property name="sqlcompare.path" value="C:\Program Files\Red Gate\SQL Compare 8" readonly="false" />

<property name="sql.scriptsourcepath" value="" readonly="false" />

<property name="sql.scriptfilepath" value="" readonly="false" />

<property name="sql.reportfilepath" value="" readonly="false" />

<property name="build.version" value="" readonly="false" />

<property name="sql.scriptsourcepath.postdeploy" value="${sql.scriptsourcepath}\Post-Deploy" readonly="false" />

<property name="sql.scriptsourcepath.predeploy" value="${sql.scriptsourcepath}\Pre-Deploy" readonly="false" />

<property name="sql.syncfilename" value="${sql.scriptfilepath}\${sql.database}\sync${sql.database}${build.version}.sql" />

<property name="sql.reportfilename" value="${sql.scriptfilepath}\${sql.database}\report${sql.database}${build.version}.html" />

<property name="sql.tempdir.postdeploy" value="e:\tempdir\${sql.database}\Post-Deploy" readonly="false" />

<property name="sql.tempdir.predeploy" value="e:\tempdir\${sql.database}\Pre-Deploy" readonly="false" />

<target name="updateversion" >

<if test="${runsql=='true' and HasSQLScriptRun=='true' }" >

<sql

connstring="Provider=SQLOLEDB;Data Source=${sql.server}; Initial Catalog=${sql.database};User Id=${sql.user}; Password=${sql.password};"

transaction="true"

delimiter=";"

delimstyle="Normal"

print="true"

output="${sql.scriptfilepath}\results.txt"

insert into _dbversion values ('${build.version}',getdate(),'CIUPDATE','${sql.database}');

</sql>

</if>

</target>

<target name="movePrePostDeployFoldersOut">

<echo message="deleting temp directory : ${sql.tempdir.postdeploy}" />

<echo message="deleting temp directory : ${sql.tempdir.predeploy}" />

<delete>

<fileset basedir="${sql.tempdir.postdeploy}">

<include name="**"/>

</fileset>

</delete>

<delete>

<fileset basedir="${sql.tempdir.predeploy}">

<include name="**"/>

</fileset>

</delete>

<echo message="deleting temp directory : ${sql.tempdir.predeploy}" />

<move todir="e:\tempdir\${sql.database}\Post-Deploy">

<fileset basedir="${sql.scriptsourcepath.postdeploy}">

<include name="**" />

<exclude name="archived/**" />

</fileset>

</move>

<move todir="e:\tempdir\${sql.database}\Pre-Deploy">

<fileset basedir="${sql.scriptsourcepath.predeploy}">

<include name="**" />

<exclude name="archived/**" />

</fileset>

</move>

<delete>

<fileset basedir="${sql.scriptsourcepath.postdeploy}">

<include name="**"/>

</fileset>

</delete>

<delete>

<fileset basedir="${sql.scriptsourcepath.predeploy}">

<include name="**"/>

</fileset>

</delete>

</target>

<target name="movePrePostDeployFoldersBack">

<move todir="${sql.scriptsourcepath.postdeploy}">

<fileset basedir="e:\tempdir\${sql.database}\Post-Deploy">

<include name="**" />

<exclude name="archived/**" />

</fileset>

</move>

<move todir="${sql.scriptsourcepath.predeploy}">

<fileset basedir="e:\tempdir\${sql.database}\Pre-Deploy">

<include name="**" />

<exclude name="archived/**" />

</fileset>

</move>

<delete dir="e:\tempdir\${sql.database}\Post-Deploy" />

<delete dir="e:\tempdir\${sql.database}\Pre-Deploy" />

</target>

<target name="comparesyncdbs">

<echo message="Start the update db process" />

<delete file="${sql.syncfilename}" if="${file::exists(sql.syncfilename)}" />

<delete file="${sql.reportfilename}" if="${file::exists(sql.reportfilename)}" />

<echo message="Calling sqlcompare.exe to sync db's " />

<choose>

<when test="${runsql=='true'}" >

<echo message="comparing and syncing schema" />

<exec program="${sqlcompare.path}\sqlcompare.exe" failonerror="false" resultproperty="returncode" verbose="true" >

<arg value="/scripts1:${sql.scriptsourcepath}" />

<arg value="/database2:${sql.database}" />

<arg value="/server2:${sql.server}" />

<arg value="/username2:${sql.user}" />

<arg value="/password2:${sql.password}" />

<arg value="/sync" />

<arg value="/ScriptFile:${sql.syncfilename}" />

<arg value="/report:${sql.reportfilename}" />

<arg value="/ReportType:Simple" />

<arg value="/options:ForceColumnOrder,IgnoreUsers,IgnorePermissions,IgnoreWhiteSpace,IgnoreUserProperties,IgnoreCollations" />

<arg value="/exclude:User" />

<arg value="/exclude:Role" />

<arg value="/exclude:Schema" />

<arg value="/exclude:Synonym" />

</exec>

<echo message="error return code = ${returncode}" />

<fail if="${returncode !='63' and returncode !='0'}">Sync failed </fail>

</when>

<otherwise>

<echo message="comparing schema no sync" />

<exec program="${sqlcompare.path}\sqlcompare.exe" failonerror="false" resultproperty="returncode" verbose="true" >

<arg value="/scripts1:${sql.scriptsourcepath}" />

<arg value="/database2:${sql.database}" />

<arg value="/server2:${sql.server}" />

<arg value="/username2:${sql.user}" />

<arg value="/password2:${sql.password}" />

<!--

<arg value="/sync" />

-->

<arg value="/ScriptFile:${sql.syncfilename}" />

<arg value="/report:${sql.reportfilename}" />

<arg value="/ReportType:Simple" />

<arg value="/options:ForceColumnOrder,IgnoreUsers,IgnorePermissions,IgnoreWhiteSpace,IgnoreUserProperties,IgnoreCollations" />

<arg value="/exclude:User" />

<arg value="/exclude:Role" />

<arg value="/exclude:Schema" />

<arg value="/exclude:Synonym" />

</exec>

<echo message="error return code = ${returncode}" />

<fail if="${returncode !='63' and returncode !='0'}">Compare failed </fail>

</otherwise>

</choose>

<if test="${file::exists(sql.syncfilename)}" >

<property name="HasSQLScriptRun" value="true" />

</if>

</target>

<target name="runpresql" >

<foreach item="File" property="sqlscriptname" >

<in>

<items basedir="${sql.scriptsourcepath}">

<include name="**\Pre**.sql" />

<exclude name="**\Post-**" />

<exclude name="**\archived\**" />

</items>

</in>

<do failonerror="true">

<if test="${runsql=='true'}" >

<echo message="Running Script ${sqlscriptname}" />

<echo message="sqlcmd -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i ${sqlscriptname} -I" />

<exec failonerror="true" program="${sqlcmd.exe}" commandline='-b -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i "${sqlscriptname}" -I' />

<property name="HasSQLScriptRun" value="true" />

</if>

</do>

</foreach>

</target>

<target name="runpostsql" >

<foreach item="File" property="sqlscriptname" >

<in>

<items basedir="${sql.scriptsourcepath}">

<include name="**\Post**.sql" />

<exclude name="**\Pre-**" />

</items>

</in>

<do failonerror="true">

<if test="${runsql=='true'}" >

<echo message="Running Script ${sqlscriptname}" />

<echo message="sqlcmd -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i ${sqlscriptname} -I" />

<exec failonerror="true" program="${sqlcmd.exe}" commandline='-b -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i "${sqlscriptname}" -I' />

<property name="HasSQLScriptRun" value="true" />

</if>

</do>

</foreach>

</target>

<target name="syncdbs" depends="runpresql,movePrePostDeployFoldersOut,comparesyncdbs,movePrePostDeployFoldersBack,runpostsql" >

<call target="updateversion" />

</target>

</project>

About the author

Lance Lyons