Migrating to an External Database

By default, TeamCity runs using an internal database that uses the HSQLDB database engine. The internal database suits evaluation purposes since it works out of the box and requires no additional setup. However, we strongly recommend using an external database as a back-end TeamCity database in a production environment.

TeamCity 3.1 supports MySQL, Oracle, and PostgreSQL external databases.

Please refer to Setting up an External Database page for the database-specific configuration steps. If you want to start using external database from the first TeamCity start, these are the only steps you need.

If you were using TeamCity with the internal storage engine, there are two ways to switch to an external database:

• Switch with no data migration: build configurations settings will be preserved, but not the historical builds data or users.
• Full Migration: all the data is preserved except Inspections and Duplicate build results as well as statistics data (and any database-stored data provided by the third-party plugins).
  

  Database migration cannot be combined with server upgrade. If you want to upgrade at the same time, you should first upgrade (run the new version of TeamCity) and then migrate to another database.

Switching to another database

To switch to another database without saving the build history or user data:

1. Install and set up an external database.
2. Shut down the TeamCity server.
3. Create a backup copy of the <TeamCity data directory> used by the server
4. Clean up the system folder: you must remove messages and artifacts folders from /system folder of your <TeamCity data directory>; you may delete old HSQLDB files: buildserver.* to remove the no longer needed internal storage data.
5. Start the TeamCity server.

Full Migration

Please note that significant migration improvements were introduced between versions 3.0 in 3.1. Refer to the applicable description: 3.0 or 3.1.

This section provides descriptions of TeamCity migration to different types of external databases:

• Full Migration in TeamCity 3.0
• Full Migration in TeamCity 3.1

Full Migration in TeamCity 3.0

TeamCity 3.0.x supports migration only to MySQL database.

The target database must be empty before the migration process (it should NOT contain any tables).

To migrate all your existing data to an external database:

1. Shut the TeamCity server down.
2. Create a backup copy of the <TeamCity data directory> used by the server.
3. Download the MySQL JDBC driver from: http://dev.mysql.com/downloads/connector/j/.
4. Place the MySQL driver into the TeamCity WEB-INF/lib folder. Further instructions assume the driver is named mysql-connector-java-5.0.5-bin.jar.
5. Run the export/import tool.
6. Move database.properties file to the config folder of your <TeamCity data directory>. This file is created when the database migration is successfully finished.
7. Copy a proper version.dat file to the system folder of your <TeamCity data directory>, overwriting the existing file. This file is also created when the migration is successfully finished.
8. Start the TeamCity server.

Running the Export/Import Tool

To start the export/import tool use the following commands from the TeamCity WEB-INF/lib folder:

• For Windows:
  You may use a command-line script that will perform a number of steps for you. To use it, navigate to the <TeamCity home>\bin directory, execute the migrateToMySQL.bat file, and follow the prompts.
  
  Alternatively, you can run the export/import tool manually:

  SET CP=server.jar;openapi.jar;server-openapi.jar;mysql-connector-java-5.0.5-bin.jar;hsqldb.jar;dbunit-2.2.jar;common.jar;server-model.jar;log4j-1.2.12.jar;jdom.jar
  
  java -Dfile.encoding=utf-8 -Xmx512m -cp %CP% jetbrains.buildServer.serverSide.HSQL2MySQL <arguments>

• For Unix:

  export CP=server.jar:openapi.jar:server-openapi.jar:mysql-connector-java-5.0.5-bin.jar:hsqldb.jar:dbunit-2.2.jar:common.jar:server-model.jar:log4j-1.2.12.jar:jdom.jar
  
  java -Dfile.encoding=utf-8 -Xmx512m -cp $CP jetbrains.buildServer.serverSide.HSQL2MySQL <arguments>

  
  Depending on the location of your TeamCity data path there are three different sets of arguments to use with the export/import tool:

• For the default location of the <TeamCity data directory> (i.e. <user home>/.BuildServer) use:

  <target database URL> <target database user> <user password>

• For a non-default location of the <TeamCity data directory> use:

  <path to TeamCity data directory> <target database URL> <target database user> <user password>

• For the standalone HSQLDB database use:

  <HSQL database URL> <HSQLDB user> <HSQLDB password> <target database URL> <target database user> <user password>

>cd TC\WEB-INF\lib
>SET CP=server.jar;openapi.jar;mysql-connector-java-5.0.6-bin.jar;hsqldb.jar;dbunit-2.2.jar;common.jar;server-model.jar;log4j-1.2.12.jar;jdom.jar
>java -Xmx512m -cp %CP% jetbrains.buildServer.serverSide.HSQL2MySQL \
jdbc:mysql://localhost/buildserver root root
Thu Apr 05 12:45:58 MSD 2007 .BuildServer path: C:\Documents and Settings\user\.BuildServer
Thu Apr 05 12:45:58 MSD 2007 Connecting to source database \
 jdbc:hsqldb:file:C:\Documents and Settings\user\.BuildServer\system\buildserver
Thu Apr 05 12:46:03 MSD 2007 Connecting to target database jdbc:mysql://localhost/buildserver
Thu Apr 05 12:46:03 MSD 2007 Initializing schema...
TABLES to be converted: (29) [agent, agent_sources_version, ....]
Thu Apr 05 12:46:09 MSD 2007 Exporting data to C:\TEMP\TC-export56020
Thu Apr 05 12:46:10 MSD 2007 Importing data from C:\TEMP\TC-export56019
Thu Apr 05 12:46:13 MSD 2007 Exporting database.properties
Thu Apr 05 12:46:13 MSD 2007 Exporting version.dat
Thu Apr 05 12:46:13 MSD 2007 Done.

Additional information

• MySQL JDBC URL format:
  

  jdbc:mysql://<host>[:<port>]/<database name>

  Refer to this page for more information.
• If you encounter an "out of memory" error, try increasing the number in the -Xmx512m parameter. On a 32-bit platform the maximum is about 1300 megabytes

Full Migration in TeamCity 3.1

TeamCity 3.1 supports HSQLDB, MySQL, Oracle, and PostgreSQL; the migration tool can move data between any of these databases.

Support of Microsoft SQL Server 2005 is also included in TeamCity 3.1, but it has "experimental" status.

The target database must be empty before the migration process (it should NOT contain any tables).

To migrate all your existing data to a new external database:

1. Set up an external database to be used by TeamCity. You can refer to Setting up an External Database for information on how to set up a TeamCity-specific. (At this point you should only create and configure the database, do not modify any TeamCity settings at this time).
2. Shut the TeamCity server down.
3. Create a backup copy of the <TeamCity data directory> used by the server
4. If the database driver is not bundled with TeamCity (refer the database properties table below, and place the appropriate driver into the TeamCity WEB-INF/lib directory. The following instructions refer to the driver as <DRIVER_JAR_NAME>.
5. Edit the <TeamCity home>/bin/dbMigration.properties file, entering correct values for the source and target database. Ensure all the properties are supplied and only the necessary lines are uncommented (single-line comments are designated by # character)
6. Run the migration tool (see below)
7. Upon the successful completion of the database migration, a database.properties file will be created. Move this file into the config folder of your <TeamCity data directory> (this step is performed automatically by the migrateDB script)
8. Additionally a version.dat file will be created. Copy it to system folder of your <TeamCity data directory>, overwriting the existing file (this step is performed automatically by the migrateDB script)
9. Start TeamCity server. This should be the same TeamCity version that was run last time (TeamCity upgrade should be performed as a separate procedure).

Databases Properties Table

Sample dbMigration.properties file residing in the bin directory contains sections for several databases.

Running the Migration Tool

You can use the migrateDB.bat/migrateDB.sh script located in the <TeamCity home>\bin directory. Just run the script and follow the instructions.

Generally, you would need to run the script with single migrate parameter to perform the migration and move the generated configuration files into <TeamCity data directory>.

Additional information

• If you encounter an "out of memory" error, try increasing the number in the -Xmx512m parameter in the migrateDB script. On a 32-bit platform the maximum is about 1300 megabytes.

Alternative approach is to run HSQLDB in standalone mode via

java -Xmx256M -cp ..\webapps\ROOT\WEB-INF\lib\hsqldb.jar org.hsqldb.Server -database.0 <TeamCity data directory>\system\buildserver -dbname.0 buildserver

and then running the Migration tool pointing to the database as the source: jdbc:hsqldb:hsql://localhost/buildserver sa ''

See also:

• Concepts: TeamCity Data Directory
• Installation and Upgrade: TeamCity Data Backup | Setting up an External Database