Configuring TeamCity with SQL Server

1. April 2009

This is the second post in my two post mini series where I try to describe my development environment. I have already mentioned that I use TeamCity as my continuous integration and build system. In this post I will describe how to configure TeamCity to use SQL Server as a main configuration repository.

Installation of TeamCity

Run TeamCity installation to install the build server and build agent on your server. This will give you fully functional build system that is integrated with your development components. It will automatically (when configured appropriately) trigger build when a change is submitted into your VisualSVN Server repository. The build will use MSBuild to build all the artifacts of your project including running unit tests, generating source code documentation, etc. When finished the system will automatically label the source code after a successful build.

Currently all configuration is written into HSQLDB database.

 

Microsoft SQL Server as a configuration repository

Assumtions: You have 64-bit operating system and you have installed TeamCity into C:\Program Files (x86)\JetBrains\TeamCity and your build server configuration is at C:\BuildServer folder.

 

JDBC driver

TeamCity is written in Java and therefore we need Java drivers for Microsoft SQL Server. Download and install JDBC driver from Microsoft SQL Server JDBC Driver page. Copy sqljdbc.jar and auth/x64/sqljdbc_auth.dll into C:\Program Files (x86)\JetBrains\TeamCity\webapps\ROOT\WEB-INF\lib.

 

Configure SQL Server for JDBC driver

Open SQL Server Configuration Manager and make sure that SQL Server (MSSQLSERVER) is up and running and that TCP/IP protocol is enabled (using port 1433).

 

TeamCity_SqlProtocol

Create SQL Server database

Open SQL Server Management Studio and add a new Login named teamcity. In this example I will use SQL Server authentication with password teamcity (because I chose such a simple password I need to uncheck Enforce password policy).

Create a new database named TeamCity and add teamcity user to the newly created database as db_owner.

 

Configure TeamCity migration tool

Edit C:\Program Files (x86)\JetBrains\TeamCity\bin\dbMigration.properties file and make sure that sourceURL is set correctly:

sourceURL=jdbc:hsqldb:file:C:/BuildServer/system/buildserver

Now we need to add target configuration for the migration. This is where we add information about our newly created SQL Server database:

# MSSQL 2008 via JDBC 
targetDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver targetURL=jdbc:sqlserver://dpokluda:1433;database=TeamCity; 
targetUser=teamcity 
targetPassword=teamcity

 

Configuration migration

To execute the migration follow these steps. Start PowerShell (or any other shell but you will need to change the bellow mentioned steps).

Add Java to your Path variable so that migration tool can run it. This step is only necessary when you don’t have Java installed on your system and you want to use the one that comes with TeamCity.

$env:Path += ";C:\Program Files (x86)\JetBrains\TeamCity\jre\bin"

Stop all TeamCity services.

stop-service TCBuildAgent 
stop-service TeamCity

Navigate to TeamCity bin folder and start the migration.

cd "C:\Program Files\JetBrains\TeamCity\bin"
migrateDB.bat migrate

(It is fine to press just [Enter] key here since we have entered the full path into the dbMigration.properties file - otherwise enter C:/BuildServer path.)

Start TeamCity services.

start-service TCBuildAgent 
start-service TeamCity

 

Verify functionality of the new repository

Navigate to your TeamCity web page and add a new user in TeamCity administration named Test.

Open TeamCity database in SQL Server Management Studio and you should see that there is a new user named Test in users table:

SELECT TOP 10 * FROM [TeamCity].[dbo].[users]

 

TeamCity_VerifyUsers

For more information see the following resources:

Coding , , ,

Configuring Development Environment

31. March 2009

As a .NET developer I have a set of development tools I frequently use. As a test-driven developer I prefer to have some kind of a continuous integration environment available. This is the first part of the two post series. This first post will list components I use. The second post will describe how to configure TeamCity to use SQL Server as a main configuration repository.

Source control system

I currently use Subversion as my source control system. I like the fact that I can work on my files (see diffs for example) even when I’m not connected to my source control server. This is a huge advantage for notebook users in my opinion.

I currently use VisualSVN Server. It comes with a VisualSVN Server Manager (snap-in to the Microsoft Management Console) where you can very easily configure your source server. You don’t need to tweak authz file any more.

 

Development environment

I don’t want to spend too much time in this section because it would make this post way too long. Let me just note that my primary IDE is Microsoft Visual Studio with JetBrains ReSharper plugin.

As a unit test framework I use xUnit.net with xUnit.net Contrib integration to ReSharper. Why do I use xUnit.net and not NUnit or MSTest? The answer is best described by Jim Newkirk at Why did we build xUnit. I like the fact that the framework is very simple. It has a limited number of custom attributes and it’s reasonably fast. Assert methods are generic based which makes the code more precise. I also like the fact that each test is run in it’s own test class instance and therefore it improves individual test isolation.

Note: My biggest complaint about MSTest was the fact that it was only available in a special suite of Visual Studio. With Visual Studio 2008, MSTest is now part of Professional version of Visual Studio. I think that it is still not enough. Unit testing is such a basic requirement that it should be part of the platform (.NET Framework) or it’s SDK. I should be forced to buy Visual Studio to get MSTest binaries!

 

Build scripts

I am a long time NAnt user. I have a library of snippets I use for my build scripts. I have even created a set of CodeSmith templates that help me generate the basic NAnt scripts.

Even though I like NAnt a lot, I am slowly moving away toward MSBuild based scripts. Most of my new development is based on MSBuild scripts. Why? I like the fact that Visual Studio shares the same project configuration as my build system.

 

Continuous integration & Build system

I prefer spending my time coding rather than tweaking XML files to configure my environment. That’s why I decided to use JetBrains TeamCity. TeamCity is continuous integration and build management system that is available for free for small teams (details are available at JetBrains web).

 

Configuration repository

As a configuration repository I prefer Microsoft SQL Server database. For very small systems like stand-alone development on a notebook I would stay with HSQLDB which is the default configuration repository in TeamCity.

 

Installation and Configuration

First I install Microsoft .NET Framework, Microsoft SQL Server 2008 and Microsoft Visual Studio 2008. After that I install JetBrains ReSharper and xUnit.net. The last component to install is JetBrains TeamCity. Read my next blog post about configuring TeamCity with SQL Server.

Coding , ,