T-SQL: A Week Of Continuous Integration Day 2

This is the second post of a 5 part series outlining my journey to getting SQL Server databases under source control and into a continuous integration environment.

The other posts are:
Day 1 - Powershell & Scripting Objects


Making Use of a Build Server

So we've seen in the last post how we can start scripting databases but we could still do so much more. Like many others, our dev team run continuous integration on a build server with TeamCity at the hub of things and there was genuine excitement at the prospect at pulling our database development in line with the rest of the code base. With my database scripts in SVN, we could make regular, automated "builds" of a database which would validate our schema while also giving us a basis to add a testing framework in the future. For me, this would be another real step forward and would solve a couple of issues. For example, imagine a database with hundreds of stored procedures referencing a particular column in a table. A drop or rename of this column would render all the stored procedures invalid and having a regular build of the schema would quickly identify any issues. It would also encourage deprecated objects to be cleared up - good practice when keeping a lean database for maintenance and performance. All i needed was to make use of the existing build server and provision an instance of SQL Server to act as the target for deploying the database.

So i'm sold. TeamCity can pull out the scripts from SVN and then using software such as FinalBuilder, I was able to loop through the directories and execute the sql scripts. Fine...in theory.

However, when you come to wanting to build the database from these scripts, there is a problem in that when using a simple powershell script (with Invoke-SQLcmd) to build the db in that dependencies are not honoured. SQL requires that objects are deployed in a certain order (functions need tables to be created first, tables need to be created before its relations etc) and the only way to create a db from a scripts directory is to manually build (and maintain) a deploy script - kind of against the idea of Continuous Integration. So while the Powershell scripting works nicely for my production environment, i'd need a different approach to get continuous integration working.

But at this stage, the plan had developed further and we now had the following components:

1) Database Objects Scripted
2) Scripts stored in Source Control
3) A database server provisioned for continuous integration
4) A build server which could execute the steps.