Migrating TeamCity database to Microsoft SQL Server 2008 R2

by Klaus Graefensteiner 9. August 2011 09:02

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.

photo

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.


001 - Database Backup

Figure 2: Configure TeamCity backup

`002 - Database Backup in Progress

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.


007 - Enable TCP in SQL Server Configuration Manager

Figure 4: Check the required connectivity is enabled

Enable Mixed Authentication.

003 - Mixed Security in Server Properties

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”.

004 - New Database User

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

005 - New database

Figure 7: Create a new database called teamcity

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

006 - Make User DBOwner

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

008 - Test the login to teamcity using sqltcadmin

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

009 - Shutdown TeamCity Server and Agent Services

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.

010 - Copy mssql database configuration file to a temporary folder

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.

011 - Edit database properties file

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.

012a - Extract the content of the Open Source JDBC driver

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

013a - Copy the JDBC jar file

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

014 - Set evironment for migration command

Figure 15: Setting environment variables

015 - Environment

Figure 16: maintainDB.cmd progress part 1

016 - Problems with Native MS Driver

Figure 17: maintainDB.cmd progress part 2

017 - Processing 1

Figure 18: maintainDB.cmd progress part 3

018 - Processing 2

Figure 19: maintainDB.cmd progress part 4

019 -Migration command success

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

020 - Start TeamCity Services

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.

021 - TeamCity is up

Figure 22: Check the portal coming up

022 - TeamCity Build Statistics

Figure 23: Check the history is still available

023 - TeamCity running on SQL Server

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!

Tags: , , ,

SQL | Continuous Integration | How To

About Klaus Graefensteiner

I like the programming of machines.

Add to Google Reader or Homepage

LinkedIn FacebookTwitter View Klaus Graefensteiner's profile on Technorati
Klaus Graefensteiner

Klaus Graefensteiner
works as Developer In Test and is founder of the PowerShell Unit Testing Framework PSUnit. More...

Open Source Projects

PSUnit is a Unit Testing framwork for PowerShell. It is designed for simplicity and hosted by Codeplex.
BlogShell is The tool for lazy developers who like to automate the composition of blog content during the writing of a blog post. It is hosted by CodePlex.

Administration

About

Powered by:
BlogEngine.Net
Version: 1.6.1.0

License:
Creative Commons License

Copyright:
© Copyright 2014, Klaus Graefensteiner.

Disclaimer:
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Theme design:
This blog theme was designed and is copyrighted 2014 by Klaus Graefensteiner

Rendertime:
Page rendered at 8/28/2014 8:12:41 PM (PST Pacific Standard Time UTC DST -7)