Setting up automated SQL Server builds in TeamCity… in 10 minutes flat

We all know we should be building our code upon every commit to our version control system (VCS). That way we catch bugs quicker. There are no downsides. This is just as true for our database as it is for our code. If you need persuading of the benefits read this.

But most of us don’t do it. I think they think it is hard?

Red Gate just released an updated version of its TeamCity plug-in. If you already use TeamCity and either you already source control your Visual Studio DB proj or you use SQL Source Control, setting up automated database builds now takes under 10 minutes. (If you are using any other CI server you should read this post instead since you’ll want to follow the approach I recommended for Jenkins.)

So you don’t believe it can be done in 10 minutes? These people do:

So you still don’t believe me? Prove me wrong: Go to OnlineStopwatch.com, select ‘stopwatch’ and ‘start’ and then continue reading. Oh – and leave a comment to let me know your time. 🙂

Step 1: Download and install the SQL Automation Pack

You can get it here.

Step 2: Copy the TeamCity plug-in into the TeamCity data directory

1. Fire up “Intro to SQL Automation Pack”.

2. Select ‘Open Folder’ under the TeamCity section in ‘Automation tools’.

3. Copy sqlci-teamcity.zip into the plugins folder of the TeamCity data directory. By default it will be located at either:

%ALLUSERSPROFILE%\JetBrains\TeamCity\plugins

or:

C:\ProgramData\JetBrains\TeamCity\plugins

SQL Auto Pack and TeamCity plug-in folder

4. Re-start the “TeamCity Server” service.

Step 3: Add a project in TeamCity and configure a build step

1. Add a new project ensuring that you add the VCS root where you are saving your database scripts.

2. Add a new build step to your project and select the Red Gate SQL CI Build option.TeamCityPlug-in

3. Give the build step a Package ID. (This is a NuGet package that can be re-used by following build steps to further test your code.  If the tests are successful you can deploy the package with your deployment tool).

4. Give the build step access to a SQL Server instance with enough creds to build a database. (If you use encrypted objects you need sa. If you don’t you can get away with less.)

5. Go to Build Triggers and add a VCS commit trigger.

Done. Finis. End.

Stop the stopwatch and comment with your time!

Also, you might want to test it by running the build manually or committing something to your VCS. If you are struggling in any area there is a more detailed worked example available on the official documentation site. If you are still struggling please contact me and I can help.

Possible extensions

You will notice that there are two other types of build step that come with the Red Gate TeamCity integration: ‘Red Gate SQL CI Test’ and ‘Red Gate SQL CI Sync’. The former can be used to automate any tSQLt unit tests that you have and the latter can be used to deploy your package to another environment.

Both these steps should be run after the ‘Red Gate SQL CI Build’ step as they will use the package created in your build as a source. Following the build step you can run test and sync in any combination or order, for example running the tests on different editions of SQL Server or updating several different test databases to ensure they are all in sync with the latest (successful) version from source control.

Your NuGet package is saved in the TeamCity NuGet feed by default but you can publish it to a different feed if you like using a ‘NuGet Publish’ build step. You can also deploy the NuGet package with your favourite deployment tool such as Red Gate Deployment Manager or Octopus Deploy. This will make it very easy to deploy the same, tested packages to your other environments. (NB. For Octopus deploy you need to add some Powershell to call sqlCI.exe, which you will need to install on your target machines. I’ve even written a script that you can use.)

 

  2 comments for “Setting up automated SQL Server builds in TeamCity… in 10 minutes flat

  1. Tiger
    September 24, 2015 at 6:06 am

    Excellent guide

    Thanks alot

Leave a Reply

Your email address will not be published. Required fields are marked *