Setting up an External Database

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 and MS SQL 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.

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

1. If you already ran TeamCity but do not want to preserve any data, delete TeamCity Data Directory.

   If you delete TeamCity Data Directory, all the data you entered into TeamCity will be lost. To preserve your data, please refer to the migration guide.

2. Run TeamCity with the default settings to create the <TeamCity Data Directory>.
3. Shutdown the TeamCity server.
4. Perform database-specific steps described below.
5. Start the server.

   Please note that TeamCity actively modifies its own database schema. The user account used by TeamCity should have permissions to create new, modify and delete existing tables in its schema, in addition to usual read/write permissions on all tables.

Database Configuration Properties

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.

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.

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 (see below) from it into <TeamCity Data Directory>/lib/jdbc directory (create it if necessary).
In prior TeamCity versions, put the driver jar(s) into webapps/ROOT/WEB-INF/lib directory of TeamCity installation (for .exe and .tar.gz distributions). Please note that you will need to repeat the step next time you upgrade TeamCity.

MySQL

Supported versions

On MySQL server side

Recommended database server settings:

• use InnoDB storage engine
• use UTF-8 character set
• use case-sensitive collation
• see also recommendations for MySQL server settings

The user in MySQL that will be used by TeamCity must be granted all permissions on the TeamCity database.

On TeamCity server side

Installation:

1. Download the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/.
2. Place mysql-connector-java-*-bin.jar from the downloaded archive into the <TeamCity Data Directory>/lib/jdbc.
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. For this you can execute the following SQL commands from MySQL console:
4. In the <TeamCity Data Directory>/config folder rename database.mysql.properties.dist 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 rename database.postgresql.properties.dist file to database.properties and specify the required settings in this file:

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.
   Place the following files:
   • ojdbc6.jar
   • orai18n.jar (can be omitted if missing in the driver version)
     into <TeamCity Data Directory>/lib/jdbc directory.
3. In the <TeamCity Data Directory>/config folder rename database.oracle.properties.dist file to 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 a new database. If you're planning to use unix build agents, ensure that the collation is case sensitive.
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 the database schema.

If you're going to use SSL connections, please ensure that the version of MS SQL server and the version of java (on the TeamCity side) are compatible. We recommend to use the latest update of SQL server:

• SQL Server 2012 - all versions
• SQL Server 2008R2 - Service Pack 2 or Service Pack 1 cumulative update 6
• SQL Server 2008 - Service Pack 3 cumulative update 4
• SQL Server 2005 - only with JDK 6 update 27 or lower on the TeamCity side

See this page for details.

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 1.2.x jTDS driver distribution file (zip file), unpack the jtds-*.jar driver jar and place it to <TeamCity Data Directory>/lib/jdbc. (Version 1.3+ requires that TeamCity server is run under JDK 1.7+).
2. In the <TeamCity Data Directory>/config folder rename database.mssql.properties.dist file to 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.

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/download/en/details.aspx?displaylang=en&id=11774] 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 rename database.mssql.properties.dist file to 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 MS SQL integrated security (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:

More details about setup integrated security for MS SQL native jdbc driver can be found http://msdn.microsoft.com/en-us/library/ms378428(v=sql.100).aspxhere (for MS SQL 2005) and here (for MS SQL 2008).

See also: