Introduction

In an earlier blog post I wrote about how to setup TeamCity with MySQL. In this article I describe how to move a TeamCity database including its data from the default database server to a Microsoft SQL Server 2008 R2 system.

Figure 1: Configuring TeamCity makes me hungry

Detailed migration procedure

The following links take you to the relevant pages of the TeamCity documentation.

Migrating TeamCity to an external database
Full migration procedure
TeamCity external database setup

The following instructions apply to a “Full Migration”. The database and all its data is being converted to a MSSQL Server database.

Step 1 – Run a custom TeamCity backup

Do a custom backup and select all options.

Figure 2: Configure TeamCity backup

`

Figure 3: TeamCity backup in progress

Step 2 - Prepare SQL Server 2008 R2

Of course you need a SQL Server 2008 R2 installed and ready to go. To make your life easier during the execution of the TeamCity migration script check the following settings:

Enable TCP/IP connection and check Windows firewall settings.

Figure 4: Check the required connectivity is enabled

Enable Mixed Authentication.

Figure 5: Check that SQL Server and Windows Authentication Mode is selected

Create a SQL Server user called SQLTCAdmin. In my example the password is “LuckyYou”.

Figure 6: Create a SQL Server user SQLTCAdmin

Step 3: Prepare your SQL database

Create new Database called teamcity.

Ensure that case insensitive collation is selected for this database, and it is the same as the collation of the tempdb database

Figure 7: Create a new database called teamcity

Make the earlier created user SQLTCAdmin the owner of the TeamCity database.

Figure 8: Make SQLTCAdmin the owner of the TeamCity database.

Figure 9: The database is ready to go

Step 4: Shutdown TeamCity Server and Agent services

Use Service Control Manager or PowerShell to shutdown the TeamCity system:

get-service Teamcity, TCBuildAgent | Stop-Service

Figure 10: Shutting down the teamcity server services with PowerShell

Step 5: Copy and rename the database properties file

Copy the file database.mssql.properties.dist from e.g. C:\Users\Administrator\.BuildServer\config 
to a temporary folder e.g. C:\TeamCityMigrationTemp\and rename it to database.properties.

Figure 11: Copy the database properties file to a temporary location

Edit the database properties file. Use your server URL, user and password. See my example below.

Figure 12: Configure the database.properties file in the temporary location using your values

Step 6: Prepare the JDBC Driver

Note: First I tried to use the native driver JDBC driver from Microsoft, but I couldn’t get it to work.

Then I tried successfully the open Source JTDS Driver. You can download it from http://jtds.sourceforge.net/
Copy the jtds-1.2.5.jar from the just downloaded package into the TeamCity Data Directory C:\Users\Administrator\.BuildServer\lib\jdbc directory.

Figure 13: Copy the jtds-1.2.5.jar into the jdts directory

Figure 14: Copy the jtds-1.2.5.jar into the jdts directory

Step 7: Create a VMWare Snapshot

This step is optional and only applicable if you are using a virtualization technology. Stop TeamCity server services again after reboot.

Step 8: Run dos commands

Open a CMD prompt as Administrator and run the following commands one at the time. Adjust the path names based on your individual environment.

cd C:\Program Files (x86)\TeamCity6.5\TeamCity\bin 
set PATH=%PATH%;C:\Program Files (x86)\TeamCity6.5\TeamCity\jre\bin 
set PATH=%PATH%;C:\Program Files (x86)\TeamCity6.5\TeamCity\bin 
set TEAMCITY_APP_DIR=C:\Program Files (x86)\TeamCity6.5\TeamCity\webapps\ROOT 
set TEAMCITY_DATA_PATH=C:\Users\Administrator\.BuildServer 

C:\Program Files (x86)\TeamCity6.5\TeamCity\bin>maintainDB.cmd migrate -T C:\TeamCityMigrationTemp\database.properties

Figure 15: Setting environment variables

Figure 16: maintainDB.cmd progress part 1

Figure 17: maintainDB.cmd progress part 2

Figure 18: maintainDB.cmd progress part 3

Figure 19: maintainDB.cmd progress part 4

Figure 20: maintainDB.cmd progress part 5

Step 9: Start Services

After the database migration succeeded, use Service Control Manager or PowerShell to start the TeamCity server services again:

get-service TeamCity, TCBuildAgent | Start-Service

Figure 21: Starting the teamcity server services

Step 10: Launch the teamcity portal

Open TeamCity Web Site and verify that the import is complete. Run a build.

Figure 22: Check the portal coming up

Figure 23: Check the history is still available

Figure 24: TeamCity is now using SQL Server

Download

The JDBC driver that I used in my example, a batch file and some notes can be downloaded here: TeamCitySQLMicration.zip

Ausblick

To get some more test result analytics out of TeamCity I was thinking about using Microsoft SQL Server Reporting Services to generate and publish additional reports. Stay tuned!