Setting up an External Database

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

Searching TeamCity 6.5.x Documentation

Table of Contents

By default, TeamCity runs using an internal database based on 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.
External database is usually more reliable and provides better performance.

Internal database may crash and lose all your data (e.g. on out of disk space condition). Also, internal database can become extremely slow on large data sets (say, database storage files over 200Mb). Please also note that our support does not cover any performance or database data loss issues if you are using internal database.

In short, do not EVER use internal HSQLDB database for production TeamCity instances.

The database connection settings are configured in <TeamCity Data Directory>\config\database.properties file. If the file is not present, TeamCity automatically uses internal database.

This page covers external database setup for the first use with TeamCity. If you evaluated TeamCity with internal database and want to preserve the data while switching to an external database, please refer to Migrating to an External Database guide.

This page covers:

Selecting External Database Engine

TeamCity supports MySQL, PostgreSQL, Oracle, MS SQL and Sybase databases.
As a general rule you should use the database that better suits your environment and that you can maintain/configure better in your organization.

While we strive to make sure TeamCity functions equally well under all of the supported databases, issues can surface in some of them under high TeamCity-generated load.
Our order of preference for the databases would be: MySQL, Oracle, PostgreSQL, MS SQL, Sybase.

We recommend using MySQL. TeamCity is tested most extensively and as a result might be a bit more stable with MySQL (see also the recommended settings).

General Steps

If you already ran TeamCity but do not want to preserve existing build history and users, please refer to Migrating to an External Database#Switching to Another Database
Here are the steps to connect TeamCity to external database:

  1. Ensure you have TeamCity Data Directory. If it is a fresh TeamCity installation, run TeamCity with the default settings to initialize the directory.
  2. Ensure you have a backup of existing data if you already have functional TeamCity installation.
  3. Setup external database as described below for each database type.
  4. Make sure TeamCity server is not running.
  5. Install Database driver.
  6. Configure connection to the database in <TeamCity Data Directory>\config\database.properties file.
  7. Start the server.

Database Configuration Properties

TeamCity uses Apache DBCP for database connection pooling. Please refer to http://commons.apache.org/dbcp/configuration.html for detailed description of configuration properties. Example configurations for each of supported databases are provided in the sections below.

For all supported databases there are template files with database-specific properties, which you can use. These templates are located in the <TeamCity Data Directory>/config directory and have the following name format: database.<database_type>.properties.dist.
In order to use a template, copy it to database.properties and then modify it to specify correct properties for your database connections.

Database Driver Installation

Due to licensing terms, TeamCity does not bundle driver jars for external databases. You will need to download the Java driver and put the appropriate jars from it (see specific databases instructions below) into <TeamCity Data Directory>/lib/jdbc directory (create it if necessary).

Configuring Database Server

Please note that TeamCity modifies its own database schema during upgrade. In addition to usual read/write permissions on all tables, the user account used by TeamCity should have permissions to create new, modify and delete existing tables in its schema as well as create temporary tables.

MySQL

Supported versions
Recommended database server settings:

Installation:

  1. Download the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/.
  2. Install MySQL connector driver jar (mysql-connector-java-*-bin.jar from the downloaded archive).
  3. Create an empty database for TeamCity in MySQL and grant permissions to modify this database to a user from which TeamCity will work with this database.
  4. In the <TeamCity data directory>/config folder rename database.mysql.properties file to database.properties and specify the required settings in this file:

PostgreSQL

Supported versions

  1. Download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/download.html and place it into the <TeamCity data directory>/lib/jdbc.
  2. Create an empty database for TeamCity in PostgreSQL and grant permissions to modify this database to a user from which TeamCity will work with this database. Be sure to set up it to use UTF8.
  3. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file:
TeamCity doesn't specify which schema should be used for its tables. By default, PostgreSQL creates tables in the 'public' schema (the 'public' is the name of the schema). TeamCity can also work with other PostgreSQL schemas.
To switch to another schema do the following:
  1. Create a schema which name is exactly like the user name; it can be done using the pgAdmin tool or with the following SQL:

    The username should be specified in the 'database.properties' in TeamCity, and has to be in lower case.
    Schema has to be empty (don't contain tables).

  2. Start TeamCity.

Oracle

Supported versions

  1. Create an Oracle user account for TeamCity (with CREATE SESSION, CREATE TABLE, EXECUTE ON SYS.DBMS_LOCK permissions).
  2. Get the Oracle JDBC driver from your Oracle server installation or download it from Oracle web site.
    Supported driver versions are 10.2.0.1.0 and higher.
    It should be two files:
    • ojdbc14.jar
    • orai18n.jar (can be omitted if missing in the driver verison)
      Place them into <TeamCity data directory>/lib/jdbc directory.
  3. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file:
    Make sure TeamCity user have quota for accessing table space

Microsoft SQL Server

Supported versions

On MS SQL server side
  1. Create new database. Ensure that case insensitive collation is selected for this database, and it is the same as the collation of the tempdb database.
  2. Create TeamCity user and ensure that this user is the owner of the database (grant the user dbo rights). This requirement is necessary because the user needs to have ability to modify database schema.
On TeamCity server side

You can use either JTDS JDBC driver (open source) or MS native JDBC driver (free for downloading).

JTDS driver
  1. Download the latest jTDS driver ditributive file (zip file), unpack the jtds-*.jar driver jar and place it to <TeamCity data directory>/lib/jdbc.
  2. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file:

To use Windows authentication (SSPI) to connect to your SQL Server database, make sure there are no connectionProperties.user and connectionProperties.password properties specified in the database.properties file and also copy jtds-XXX-dist\x86\SSO\ntlmauth.dll file from the JTDS driver package to <TeamCity Home>\bin. Also setup TeamCity server (service or process) to be run under user account that has access to the database.

The jtds driver doesn't know a "default" port value, so the port number in the connectionUrl is a mandatory parameter.

Please make sure SQL Server is configured to enable TCP connections on the port used in the connectionUrl.
If you use named instance you can specify the instance name by following means:

  • Add the "instance" property into the connection URL, like the following:
    connectionUrl=jdbc:jtds:sqlserver://<host>:1433/<database name>;instance=sqlexpress
  • Or, specify corresponding property in the database.properties file:
    connectionProperties.instance=<instance_name>

Native driver
  1. Download the MS sqljdbc package from [http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707] and unpack it. Let us assume the directory where you've unpacked the package into is called sqljdbc_home.
  2. Copy the sqljdbc4.jar from the just downloaded package into the TeamCity Data Directory/lib/jdbc directory.
  3. In the <TeamCity data directory>/config folder create database.properties file and specify the following required settings in this file:

If you use named instance you can specify the instance name in the connection URL, like the following:

If you prefer to use Windows authentication, follow the additional steps:

  1. Ensure that your Java bitness is the same as Windows bitness (in other words, use 64-bit Java with 64-bit Windows and 32-bit Java with 32-bit Windows).
  2. Copy the sqljdbc_home /enu/auth/x86/sqljdbc_auth.dll (in case of 32-bit system) or sqljdbc_home /enu/auth/x64/sqljdbc_auth.dll (in case of 64-bit system) into your Windows/system32 directory (or another directory denoted in %PATH%). Ensure that there are no other sqljdbc_auth.dll files in your system).
  3. In the <TeamCity data directory>/config folder create file database.properties and specify the connection URL (with no user names or passwords) in this file:

Sybase

Supported versions

TeamCity needs special configuration of the database to work appropriately.
Sybase should be configured to work with Case-insensitive table names.

  1. Ensure that Sybase server is set up to use case-insensitive table names.
    This can be done in Sybase server config utility:
    1. Click on the "Configure Adaptive Server" button and select the server from the list
    2. Click on the "Language" button
    3. Click the button under the "Sort Order" label, and select the case-insensitive option (for example, "Dictionary order, case insensitive, accent insensitive").
  2. Create a new empty database and apply the following options:
    Use the actual database name instead of DB_NAME.
  3. Ensure the user that will be used by TeamCity to access the database has necessary permissions.
    Use the actual database name instead of DB_NAME, user name instead of TC_USER and password instead of TC_USER_PASSWORD.
  4. Get the driver (file jconn3.jar that can be found in <Sybase home directory>/jConnect-6_0/classes) and place it to <TeamCity data directory>/lib/jdbc.
  5. In the <TeamCity data directory>/config folder create or modify the database.properties file and specify the following settings in this file, replacing DB_NAME, TC_USER and TC_USER_PASSWORD with the values used during database configuration:



See also:

Installation and Upgrade: Migrating to an External Database

 

Labels:

sybase sybase Delete
database database Delete
migration migration Delete
mssql mssql Delete
oracle oracle Delete
postgresql postgresql Delete
mysql mysql Delete
Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.