Database CI the easy way - A first look at Red Gate's new TeamCity plugin

Update (June 2014): Since this article was written, the folks at Red Gate have announced a new version of the plugin, which enhances and speeds up the setup process. You can read more about it here, and thanks to Alex for the comment below alerting me to it.

I was recently tipped the nod by David Atkinson of Red Gate that they have a new plugin for the TeamCity Continuous Integration system in Early Access Preview. The plugin allows GUI integration of SQL Compare with TeamCity build steps – a great improvement for those starting out with TeamCity who may not be familiar with how to set it up, and who might find command line steps a little daunting.

This means that this step in my earlier article at Simple Talk can now be streamlined and made much easier to use; this also applies to the steps I demonstrated at the recent SQL In The City conference in London.

Now, this new plugin doesn’t do things that you couldn’t do with the command line, but it does make the whole process easy to use, as well as easier for others to maintain. To me, this is a key part of getting people to use the system - if it is easy and well supported then you can more easily convince developers and managers that it can be adopted. It also makes your initial setup easier and avoids a lot of configuration errors! Of course, the nice interface doesn't prevent you using the command line if your needs are a little more bespoke.

So, let's have a look at the plugin then. Installation is manual, but easy – just drag and drop the plugin into the TeamCity Plugins directory. There’s some simple instructions included to help you if you’re not too sure.

One thing I should mention – you need to be on version 7.x of TeamCity. My test area wasn't, and I had to upgrade – but if you’re new to TeamCity you’ll probably be downloading and using the latest version.

As an example, let’s take an existing build configuration and update it to the new way of doing things.

So, my existing build steps (actually within a template, but the steps are the same whether you use templates or not) are: 

It's the second step, "Update database schema" which we are looking at. The actual step currently has the command line :

"C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" /scripts1:"%teamcity.build.checkoutDir%\Database" /database2:%DBName% /server2:"%SQLserver%" /ignoreparsererrors /sync

which I am sure you will agree is a little ugly.

So let's create a new step (the Add Build Step link) and select the new build runner type of Red Gate (SQL Server):

This gives us a nice screen into which we can put the parameters previously used. I've used parameters here (which are defined as TeamCity build parameters) - but if you're not using parameters, standard file paths / server & database names are fine.

Note - I've used Windows Authentication here - I'd recommend you do the same if possible. That uses the Build Agent's credentials to authenticate to SQL Server. If you choose SQL Server Authentication, your access credentials will be stored and appear in the build log in plaintext, which isn't ideal. Red Gate assure me they are aware of this issue and are looking at how to resolve it.

So, having created the new step, I then disabled the previous step (there's nothing like having a simple rollback solution!) and re-ordered the build steps so the new one would execute at the same point in the build that the now disabled version had:

It's worth noting that the plugin helpfully makes the parameters used visible at this summary screen, which certainly helps to aid visibility of the process.

At this point, I ran a build, so that I can check the process is working.  The good news, of course, is that it did work.

Now, if we go to the build log of the process, we can see a little more detail:

A few things to note from this build log extract:

1. The command line used is output as part of the build (highlighted above). This is great for debugging if things aren't quite as you expect, and gives a great starting point for your own tweaks to the process.
2. The actual copy of SQL Compare used is unique to the plugin - it's not using the same version as if I'd launched it from the command line. This would be worth exploring if you're planning a version upgrade, and worth being aware of if troubleshooting. In this case the plugin is actually using a more up to date version than I have installed on my test system.
3. Red Gate's Automation License is being used for this tool. As you will see from the screenshot above though, the tool comes with a free 14 day trial period.

As you can see this is a simple process to source control a database, and one that I can see being a great help in quickly getting up and running with continuous integration for databases, whether in a database-only build (as this simple example), or part of a bigger application test process.

I hope that Red Gate can expand this plug in to cover steps like clearing out the existing database (perhaps a tick box in the current implementation?) - this would cover my first step, which I think most implementations will want in order to give a predictable build. Another useful step would be to integrating with SQL Data Compare to load source controlled data into the database, which a number of implementations use. Ideally, a mature plugin would also run any SQL Test (i.e. tSQLt) tests defined in the database schema, and load the test results back in to TeamCity. This would then form a complete Continuous Integration solution for databases, without any need for the command line steps, and so allow easy adoption and maintenance. 

All in all I'm very pleased with the work that Red Gate have put in to producing an easy to use tool here; it's something that I can see fulfilling the majority of needs, and expanding over time to do more common Database CI tasks.

Why not drop Red Gate a line to give it a go yourself?