Red Gate recently released a new set of build scripts, specifically designed for TFS Build that allows users to call the Red Gate SQL Automation Pack to build, test and deploy their database. It also makes it easy to publish a NuGet package containing the database scripts to a NuGet repository ready to be picked up by a release management tool. It’s documented here.

This is great for automated testing and deployments that you want to trigger every time you commit new code to source control. It provides feedback to developers regarding the correctness of their code and it can be used to keep a testing database up to date. Finally it allows you to publish a release artifact, in the form of a NuGet package, in a fully automated way.

This NuGet package can then be used by Red Gate Deployment Manager (DM) or Octopus Deploy (as I documented on this blog last month) for ‘one-click’ deployments to your more risk-averse environments on a more selective basis, where DBAs have the ability to review the deployments before triggering them.

However, there is a step in between publishing the NuGet package and deploying it to the senior environments. You need to create a release. This is important because a release may consist of multiple NuGet packages. For example, let’s say your project consists of an ASP.NET web app with a SQL Server back-end. It would be nice to deploy all your packages together as a single entity so that dependencies are deployed together.

Creating the release in either UI is a trivial enough task, but it would be better if it was automated. Creating a new release every time you want to deploy a new version is, frankly, a bit of a faff.

The good news is that creating your release with either DM or Octopus (and deploying it if you wish) is relatively easy. All you need to do is call the command line tools that are provided (octo.exe or deploymentmanager.exe). Other CI servers, such as Jenkins, Bamboo and TeamCity, allow you to make a command line call as a standard type of build step. Unfortunately, TFS Build does not. There simply is no feature to make a custom call to cmd to reference some other executable. You need to wrap up your command into some other build process.

There are a few ways to do this. @PaulStovell has referenced a few different approaches here. I’m going to take an approach similar to that of @dkarzon by wrapping my command into a build script. However, instead of creating my build script from scratch, I’m just going to tweak to the existing TFS Build template that comes pre-packaged with the Red Gate SQL Automation Pack.

We don’t need to worry about creating the package or publishing it to our NuGet feed because the Build and Publish steps in the SQL Automation Pack have already taken care of that for us. Find the SQLCI.proj file and add the following block of code, where ‘foo’ is a sensible description of the task you are carrying out. (Each line within a seperate XML <> tag):

Target Name="foo"
Exec Command="YourCommand"/
/Target

We also need to add ‘foo’ to the list of DefaultTargets at the top of the script, to ensure that the code will run.

Project ToolsVersion="4.0" DefaultTargets="build;test;sync;publish;foo" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"

Once we’ve done this we can type in whatever command we like. You could also add multiple steps to carry out multiple actions. Do you already have some testing process for example that you would like to run post deployment?

We are now going to add the command to our release management tool. If we have already followed the SQL Automation Pack documentation we will already have already passed the TFS build number through to MSbuild. (Yes, this is something that everyone forgets the first time). This allows us to create releases with the same version number as our build. This is how to do it with Deployment Manager:

"C:\Program Files (x86)\Red Gate\Deployment Manager\Tools/DeploymentMansager.exe" create-release --project "MyProject" --version $(tfs_buildnumber) --deployto MyEnvironment --server MyDeploymentManagerURL --apikey MyApiKey

And this is how to do it with Octopus Deploy:

"C:\Program Files\Octopus Deploy\Octo.exe/octo" create-release --project "MyProject" --version $(tfs_buildnumber) --deployto MyEnvironment --server MyOctopusURL --apikey MyApiKey

Once done it should look like this:

In summary

By copying and pasting a little bit of text, and adding a single command, we can extend the default SQL Automation Pack TFS Build template to do whatever we like from the command line. An obvious and natural use case for this is to create a release in our release management tool of choice.

Once we have created a release, it often makes sense to stop using the sync command in the TFS Build template and trigger your release management tool to do any automated post commit deploys instead. This way you are testing precisely the same deployment method that you’ll be using for the more important deployments to your downstream environments every time you commit a new line of code to source control.

Automating your release management tool to keep a test environment up to date within your TFS Build also reduces the number of steps required to promote the latest version of code to your testing, staging or (if you are brave enough) your production environments.