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?

SQL In The City - A first time speaker's experiences

I have (with a colleague) recently presented at SQL In The City (Red Gate’s industry conference) in London. This was the largest audience to which I have thus far presented, and I thought I’d write up my thoughts on how it came about, how we prepared, and how it went.

Disclaimer – I’ve not had the session feedback yet – so this post is from my perspective!

Firstly, I’ll outline a little of the background to my session.  I wrote an article for Red Gate’s community web site Simple Talk in February 2012. This reflected a system we had implemented at work, and was positively received.

Fast forward 4 months. I was intending to attend the SQL In The City conference on the Friday, and had booked the day off work. Imagine my surprise when I received an email from Michael Francis and David Atkinson at Red Gate, inviting me to speak on Continuous Integration at this very conference.

So, I first of all took a moment to talk to my colleagues and other members of my local User Group. This was very helpful. I was cheered that the general thought was that I could do this, but given a word of caution by the user group leader, that I should think about how much time I was to be speaking for, and how much that time might be worth.

As an example, an audience of 150 * 1 hour * £20 per hour = £3,000 – was my talk worthy of that much time?

Well, after much deliberation I decided to do it – co-presenting with a colleague, Annette Allen. Annette had been involved in the implementation from the beginning, and this gave us the chance to cover both the developer experience and the system set-up and implementation side.

This meant that we had only 6 weeks to write the presentation, practice it, get it approved by Red Gate, and generally polish it. We the had a kind offer to do a ‘preview’ of the session at the next User Group session – which we wanted to do as it would give us some (largely) unbiased feedback. So that gave us 3 weeks to write the presentation, practice it, and get comfortable with the content. Oh, and we had to keep up the day job, too!

I’d like to thank my employer for their open and supportive attitude towards our doing this presentation, which really helped.

As you will have gathered, given that I am writing this, we made it. We had useful feedback from the User Group session, and from Red Gate’s product managers, which we incorporated into the presentation.

So, what of the event itself?  SQL In the City was a 2 day event in London (It’s on tour in the US this summer), and hosted about 600 people over those two days. I turned up the evening before, to find a great atmosphere as Red Gaters and Speakers helped get the place set up, then headed out for a great meal at a local restaurant.

Come the morning of the event, we had an early start – at the venue for 0730 (and Robin Williams was right about what the ‘O’ stands for). There were many great sessions – some solo, from speakers such as Steve Jones and Grant Fritchey; and others a double act, like Sebastian Meine and Dennis Lloyd (on Unit Testing), or Annette and myself. I was pleased to get a chance to enjoy some of these sessions in the run up to my session.  

When it actually came time to speak, I have to say I was initially nervous (and perhaps relied on my notes a little much), but everyone there was very supportive, and I soon relaxed into it. We ran close to time both days – so I apologise if we didn’t get a chance to answer your questions; both Annette and I were kept busy with questions all through subsequent breaks! (If you have more – do get in touch).

Quite aside from the presentations – at which I hope everyone learns something – the breaks gave a great opportunity for asking questions, learning from the Red Gate staff members, and getting to know your peers. It was great to see people I've known for a while, and I was really pleased to meet lots of people who I had hitherto only conversed with by email or knew from blogs, forums & tweets.

The days ended well, with Beer and Competitions, and all in all the event was very well attended and supported, and most enjoyable. If you went and haven’t sent in your feedback form yet – please do so (http://www.red-gate.com/sitcfeedback). I hope to see many of you at the event next year.