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
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
The JDBC driver that I used in my example, a batch file and some notes can be downloaded here: TeamCitySQLMicration.zip
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!