Migrating to an External Database

Skip to end of metadata
Go to start of metadata
You are viewing documentation of TeamCity 5.x, which is not the most recent released version of TeamCity. Please refer to the listing to choose another version.
Search

Searching TeamCity 5.x Documentation

Table of Contents

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

This section covers steps you need to perform if you evaluated TeamCity with internal database and need to switch to an external database to prepare your TeamCity installation for production use.

This section covers:

Since TeamCity 5.0, maintainDB command line utility is used to migrate data between databases. maintainDB.[cmd|sh] shall/batch script is located in the <TeamCity Home>/bin directory and is used for migrating as well as for backing up and restoring TeamCity data.

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 (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

TeamCity supports HSQLDB, MySQL, Oracle, PostgreSQL, Microsoft SQL Server 2005, 2008 and Sybase; the maintainDB tool can move data between any of these databases.

The target database must be empty before the migration process (it should NOT contain any tables).
If an error occurs during migration, do not use the new database as it can result in database data corruption various errors that can uncover later. In case of error investigate the reason that is logged into console or in the migration logs (see below), and if solution is found, clean the target database and repeat the migration process

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

  1. Set up an external database to be used by TeamCity. At this point you should only create and configure the database and also install the database driver into TeamCity. Do not modify any TeamCity settings at this time.
  2. Shut the TeamCity server down.
  3. Create new properties file with a custom name (for example, database.<database_type>.properties) for the target database according to its settings from a corresponding template (<TeamCity Data Directory>/config/database.<database_type>.properties.dist), entering actual values. Place this file into a temporary directory to your liking.
  4. Run the maintainDB tool with the migrate command and specify the absolute path to the newly created target database properties file with -T option:

Upon the successful completion of the database migration, a database.properties file will be replaced with the file specified via -T option. The old database.properties file will be automatically re-named in the following format: database.properties.before.<timestamp>.

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

DB Name Driver Class Name Driver jar Name Driver is Bundled JDBC URL format
MySQL com.mysql.jdbc.Driver mysql-connector-java-5.1.12-bin.jar no, download page jdbc:mysql://<host>[:<port>]/<database>
PostgreSQL org.postgresql.Driver postgresql-8.2-505.jdbc3.jar no, download page jdbc:postgresql://<host>[:<port>]/<database>
Oracle oracle.jdbc.driver.OracleDriver ojdbc14.jar
orai18n.jar
no, grab files ojdbc14.jar and orai18n.jar from Oracle server installation or download them form download page jdbc:oracle:thin:@<host>:<port>:<database>
HSQLDB org.hsqldb.jdbcDriver hsqldb.jar yes jdbc:hsqldb:hsql://<host>[:<port>]/<database>
MS SQL net.sourceforge.jtds.jdbc.Driver jtds-1.2.2.jar no, download page jdbc:jtds:sqlserver://<host>[:<port>]/<database name>
Sybase com.sybase.jdbc3.jdbc.SybDriver jconn3.jar no, grab it from <Sybase home directory>/jConnect-6_0/classes jdbc:sybase:Tds:<host>:<port>/<database_name>

Troubleshooting

  • Extended information during migration execution is logged into logs\teamcity-dbmt.log file. Also, logs\teamcity-dbmt-truncation.log contains extended information on possible data truncations during the migration process.
  • If you encounter an "out of memory" error, try increasing the number in the -Xmx512m parameter in the maintainDB script. On a 32-bit platform the maximum is about 1300 megabytes.

Alternative approach is to run HSQLDB in standalone mode via

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

  • If you get "The input line is too long." error while running the tool (e.g. this can happen on Windows 2000), please change the script to use alternative classpath method.
    For maintainDB.bat, remove the lines below "Add all JARs from WEB-INF\lib to classpath" comment and uncomment the lines below "Alternative classpath: Add only necessary JARs" comment.



See Also:

Labels:

admin admin Delete
mysql mysql Delete
migrate migrate Delete
external external Delete
database database Delete
oracle oracle Delete
mssql mssql Delete
2005 2005 Delete
Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.
  1. Dec 04, 2008

    A few notes on windows 2003 to perform a db migration here so to save time for windows users.
    1)You need to download Java JDK here: http://java.sun.com/javase/downloads/index.jsp
    2)This needs to be done below so you avoid the No 'Server' jvm Error:
    1. Copy 'server' folder from the JDK's JRE's bin folder (example: C:\Program Files\Java\jdk1.6.0\jre\bin\server)
    2. Paste the 'server' folder to JRE's bin folder(example: C:\Program Files\Java\jre1.6.0\bin)
    --Credited for this is here:http://thiamteck.blogspot.com/2007/10/error-no-server-jvm-at.html
    3)In database.properties file make sure you change this line to your appropriate directory if you have changed the default directory:
    sourceURL=jdbc:hsqldb:file:<PATH/TO/.BuildServer>/system/buildserver

    Step 8 above is redundant and is not needed assuming step 7 works.

    Overall steps worked well, minus java problems I had.

  2. Jul 07, 2009

    On Windows, I think the environment variables need to exist, I couldn't get it to work otherwise:

    TEAMCITY_DATA_PATH  Path to BuildServer directory
    TEAMCITY_APP_DIR    Path to TeamCity application directory (default is <TeamCity_home>\webapps\ROOT)

    No need to download Java, it's already included in the <teamcity app dir>\jre, just add it to the PATH.

    You can add forceClean=true to the end of the dbMigration.properties file to DELETE all tables in the database if you have to retry the script a few times like I did.

    1. Feb 24, 2010

      Confirmed Sire404's comment.  When I upgraded to MS SQL Server, I couldn't get the migration tool to work until I did all three of those things.  It would be nice to have that in the instructions.


    2. Oct 28, 2010

      A couple of additional things I've found when migrating the database on Windows Server 2003:

      1. MaintainDB requires that your TEAMCITY_APP_DIR setting have no spaces in it. So if you were silly like me and installed TeamCity into the Program Files directory, you will need to specify this environment variable as follows: C:\PROGRA~1\TeamCity\webapps\ROOT. It may also be true of TEAMITY_DATA_PATH but I'm not sure.

      2. If you're intending to use Windows Authentication (SSPI) to connect to your SQL Server database, you will need to copy the downloaded file 'jtds-1.2.2-dist\x86\SSO\ntlmauth.dll' from the unzipped JTDS to <JRE Home>\bin. You'll also need to set the "TeamCity Web Server" to run as an account that has access to the database.

      1. Dec 02, 2010

        Daniel,

        Thank you for the notes!

        Not supporting spaces in TEAMCITY_APP_DIR is a bug (could have been filed into our tracker), will be fixed in TeamCity 6.0.1.

        I also added your note on SSPI into TeamCity 6.x documentation (Setting up an External Database).