Continuous Integration and the Automated Database Update Process

[article]


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 name="syncdbs" depends = "runpresql, movePrePostDeployFoldersOut, comparesyncdbs, movePrePostDeployFoldersBack, runpostsql" >
 <call target="updateversion" />

</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" >

              <!-- run pre-deploys -->

              <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>

AgileConnection is one of the growing communities of the TechWell network.

Featuring fresh, insightful stories, TechWell.com is the place to go for what is happening in software development and delivery.  Join the conversation now!

Upcoming Events

Oct 12
Oct 15
Nov 09
Nov 09