Working with Continuous Integration in a BI Environment Using Red Gate Tools with TFS

Continuous integration is becoming increasingly popular for database development, and when we heard of an implemention of a system based on TeamCity, TFS, SQL Source Control, SQL Compare, and SQL Data Compare, we were interested to find out more. Grahaeme Ross gives a practical guide to how it was achieved.

This article describes a successful proof-of-concept trial of Continuous Integration for a Business Intelligence (BI) application. It uses TeamCity, TFS, SQL Source Control, SQL Compare, and SQL Data Compare, and I’ll be aiming to show you how you can set up something similar.

The article ‘Using SQL Test Database Unit Testing with TeamCity Continuous Integration‘ by Dave Green was the inspiration for me to give this approach to continuous integration a try. As our company uses TeamCity, it seemed to be a great fit. The article got me started, and I was then able to fill in those areas that it touched on only briefly. It is those areas that I want to talk about and pass on my experience. At the same time I’ll raise a few questions that I hope people will be able to offer opinion about, or elaborate on.

I’ve been tasked by my company to find ways of improving the development methodology of the Business Intelligence (BI) team. The Continuous Integration (CI) methods being used by our Web team seemed out of our reach until reading Dave Green’s article and Troy Hunt’s ‘Continuous Integration for SQL Server Databases‘.

Preparation work

We were very lucky at our company to have full pro licence for SQL Tools so the first thing I did was to download and install the following:

I will go into more detail about those parts of my build that Dave Green mentioned only briefly, especially relating to Team Foundation Server (TFS).

We already have TFS installed and I have created a Proof of Concept folder for my work.

Setting up SQL Source Control

SQL tools first. Installing SQL Source Control 3 was straightforward and I was soon staring at the screen below:


A word of warning: We were using TFS 2005 and, when I attempting to link the database, I found some issues. Although it was nothing serious, it is worth detailing here.


Server URL:

I had problems connecting to TFS using the server or instance name. Using the IP address and port solved this issue. This may just be peculiar to our set up, but we have found that TFS 2005 has less connection issues when using IP. Someone may like to explain that to me (DNS Server issues perhaps?)

Database folder:

I chose the browse button thinking to head to my POC folder and then create the database folder there using the SQL Source Control 3 as shown below:


For some reason I was getting an error when I tried to do this and, through being both impatient and lacking admin access to TFS, I found a workaround. I went into TFS and created the database folder and then went back to SQL Source Control and pointed to it. Hey presto it worked and my database was created in TFS. SQL Source created a whole load of folders when doing this, so I am not sure why it could do it earlier. I recommend clicking on the send error report to Red Gate when you get something like this, it will help them a lot. I did, and I felt good inside. I hope they did!

Finally I chose ‘dedicated database’ because I did not want the developers making changes to this copy of the database in TFS.

So now I have my database in TFS:


You might notice the Scripts folder at the bottom. I did this after having an accident with my Database Folder. I originally stored these scripts in folders within the SQL Source Control main folder highlighted.

Well I don’t know for sure if that was the reason why I lost my link and it became corrupted, but I have had no problems since I moved those scripts in to a separate folder. I haven’t checked this with Red Gate but there is a fair chance I will find out that you mustn’t mess with the database folder. It is probable that it could have been fixed quite easily, but as I am impatient and it was quicker just to recreate it. In a live environment I would not want to do this, so it was a good thing to learn the lesson now. (Yeah, I did send the error report; still feeling good…)

Setting Up TeamCity

Now I have my database safe in TFS. Time to set up TeamCity, with a new project connected to TFS. Now I found this to be fairly straightforward, but I had to make sure that a few things were set correctly. I have only recently started using TeamCity and this should give many of you hope, because I was able to set this up without too much trouble.


Firstly, create a project in TeamCity, and then you will be presented with the above page. Now we want to configure this project to TFS and, in particular, to be able to access our newly-scripted database and the script folder.

See the tab saying VCS Roots? Click on that tab now. Once the page loads, click on the button “Create VCS Root”. When the next page loads, you only have one option which is to choose from a drop down combo list box. Choose “Team Foundation Server”. Now we get to the interesting bit:


VCS Root Name: Give it a name, whatever floats your boat.

URL: Couple of things here. As the picture shows, enter the server name or the ip address and then follow it by the port number, yours should be 8080, that’s the port that TFS is listening on. If you got this far then this should look the same as the entry you used for Red Gate SQL Source Control. If you are having trouble, you need to check with TFS owner to find the port number to use.

There is another thing to check, if you are having trouble. This is whether you need to use HTTPS rather than HTTP. In the picture above, it’s plain old HTTP.

User name: The account that you use to access TFS is a good start here. I used my own domain user account for the sake of simplicity during the exercise ‘proof of concept’ (POC). When this moves to ‘live’, I can see that we will need to set up a specific Teamcity TFS account.

Root: This refers to the root directory that you want Teamcity to start from. Check that the account you are using has access to this area of TFS, and make sure that you set this to the parent directory that contains all the folders that you will need for your build.

I have two folders that I need to access, the Red Gate SQL Source database scripts, and the custom scripts that I have developed as part of my build: So, in order for me to have access to both these folders, my route would be at the level above.


Force Overwrite of all files: I bet you thought I was going to ignore this one. Jetbrains certainly have done so in their documentation but I am having a good day, so here goes. Some people had been finding that files were not always being checked out by TeamCity and so the build was going ahead with older files. This option will force all the files in TFS to be checked out and will overwrite the previously checked-out files. I have it turned on here to test it for performance hit. It is slower so I will not use it unless I encounter this issue with a build. The manual for Teamcity can be found here (version 7):

Checking Interval: Sets the time interval that TeamCity polls TFS for any file changes. You may want to change this if your set-up requires it.

VCS Sharing: Allows the configured TFS connection to be visible across all projects. I am not using this option at the moment but you may find it useful if you are using the same root for different projects. Naturally, if you change it, you will change the setting for all projects so be careful.

I missed the “Save”, “Cancel” and “Test Connection” buttons off the picture. They are at the bottom of the page and the “Test Connection” I recommend you use before saving. You will get a nice feeling knowing that your settings actually connect to TFS (let’s hope it is to the right root folder as the option will not tell you that…) Save the changes once you get that nice “Connection Successful” message. And you come back to here:


Click back on the General Tab and you will see a page similar to this:


That’s the connection done. There is one last page that will pop up concerning TFS, but you will only see this when you start creating a build. So let’s create a build to show you this screen and explain the settings.

So, once you click on the Create Build Configuration button, you will get this screen:


Let’s get past this screen as it is the TFS screen I want to discuss. The manual gives a good explanation of the other entries. Enter a name and then press the VCS Settings button and you get this:


Select the VCS root that you created from this dropdown and the screen will update and look like this:


Here we are and here we go.

VCS Checkout Mode: I am using the default setting which is ‘Automatically on Server’. This means that TeamCity will carry out the checkout and will also be using its resources to carry this out. This does not allow any version-control activities such as check-ins, label or updates to TFS. You can turn this off by selecting ‘Do Not Checkout Files Automatically’, in which case you will then need to script the checkout as part of the build. The third option, ‘Automatically on agent’, moves the workload over to the TFS server, so you use TFS resources, thereby freeing up resources on the TeamCity server, and it will also allow such thing as check-ins, label and updates.

Checkout Directory: If you are using a shared VCS then you may want to enter a custom path here to separate it from the other project roots. Auto is recommended for good reason; there can be issues with using custom path, with the possibility of inconsistent source updates.

Clean all files before build: Teamcity doesn’t automatically remove those files that are created by a previous build. This can cause problems with old files in the build directory. Checking this will delete those directories and recreate them before each build. I have this option switched on at the moment so as to test performance. Unless there is an issue with old files then I will turn it off because it can be very slow depending on the size of your build.

VCS labelling mode: This is useful if you want to label all the elements of a build in TFS once it has finished. The options are fairly obvious, but if you need more detail, it is worth taking a look here:

The only note I would add to this is that you probably need to change the VCS Checkout Mode to “Automatically on agent”, because the manual does state that you can’t label unless you are in this mode. I haven’t tried doing this and any who have feel free to comment.

Choose VCS roots to label: This is only checkable when you select a VCS labelling mode other than “Do not label”. The check box OR boxes will become enabled. ‘But there is only one check box’, I hear you say. That is because I only set up one connection to TFS. I could have more than one root if I chose to. So the option lets you select which roots you created get labelled. Check them and the labelling will happen, don’t check them and it won’t. You can leave all the boxes unchecked without a warning. To me, this means that none of the root will be labelled, the equivalent of choosing “Do not label”. Anyone can confirm this, please comment.

That concludes the TFS aspect of the TeamCity build process. The TFS-integration has been stable so far and works well for me. The ability to store everything in TFS, which gives you an additional feeling of security, and run your builds from there is very satisfying. I have only scratched the surface of the possibilities using TeamCity and TFS in the POC and it has been a worthwhile exercise. I am now looking at delivering the SSIS packages as part of the build (and any third party tools). The level you can go to with your builds is only limited by your time and knowledge.

Automated Integration Testing

This is just a brief description of what I was aiming for with regard to integration testing. It really needs a second article that would also detail the Tests Driven Development we have been looking at.

In order to make the integration process as effective as possible, I want an automated test process in order to run integration tests. I want to be able to create my database, install my test environment on that database, run the tests and report back to Teamcity. Dave Green’s article covers the last part of reporting back to Teamcity. I wanted to script my tests on to the database rather than have them already installed on a database and be part of the Red Gate SQL Source Control database scripts in TFS.

Other articles that I have in the acknowledgements deal with the build themselves better than I could, I would just like to look at SQL Test and tSQLt from my perspective and how I have set them up.

This first stage is just to get some sort of automated testing in place. With this in mind it became apparent that certain tests tend to apply to all projects. So the aim is to create a set of “gatekeeper” tests. These tests would run to determine if the code is up to the standard required to be committed for testing. I tried this out and it surprised me just how much it picked up and how much it has helped the team. We have a long way to go but I think we are on the right road now.

I had a bit of difficulty with setting this up, partly because of a misunderstanding on the whole SQL Test and tSQLt / SQL Cop components, so to clear this up, for me:

  • SQL Test is a user interface that allows you to create and run tests from within the SSMS. It is installed on the local machine where the developer is working and he can then attach the interface to any database he can access. It will not work unless tSQLt is installed on the database you want to attach to. It will work, but do nothing if you have not created any tests for tSQLt to run.
  • tSQLt is the SQL based test software that is installed in to the database that you want to test against.
  • SQL Cop is a set of stored procedure tests that can be run using tSQLt. SQL Test comes with 5 of them and will install them if you want.


That’s it for TFS and Teamcity build. I follow Dave Green’s article for the general build, with help from the excellent pdf file from Red Gate “Continuous integration for databases using Red Gate tools“. I find the working examples very useful. For me, this looks like a very workable first solution upon which I can build. It does need a lot of setting up and this can impact upon projects so I would look to split it down in to smaller chunks that can be implemented in isolation and give us some of the benefit quickly (sounds like I am applying CI to creating CI. I think it has infected me…. In a good way!)

The one concern that came out of the POC was SSIS testing. In our team, we use SSIS. The difficulty of testing SSIS, certainly in terms of automated tests, limits what we can do. This is especially in terms of build a library of automated tests, very useful for regression testing. There is a free tool called SSISUnit2008 looks very promising, but there are no planned releases since the version 1.0 release back in august 2008. I personally would love Red Gate to take up the baton on this and add it to their toolbelt. Please comment on this, if enough people want it, then you never know, it could be possible to persuade them.


Dave Green and his article

Troy Hunt