Setting up an External Database

Added by Christopher Hamilton, last edited by Yegor Yarko on Jan 19, 2010  (view change)
Search
Searching TeamCity 5.x Documentation
Table of Contents

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.

External database is usually more reliable and provides better performance.

TeamCity supports MySQL, PostgreSQL, Oracle, MS SQL and Sybase databases.

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:

General Steps

  1. If you already ran TeamCity but do not want to preserve any data, 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

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.

You can use templates of database-specific properties file which are available for all supported databases. These templates are located in the <TeamCity Data Directory>/config directory and have the following name format: database.<database_type>.properties.dist.

MySQL

Supported versions: (5.0.40+)

  1. Download the MySQL JDBC driver from: http://dev.mysql.com/downloads/connector/j/
  2. Put MySQL connector driver jar (mysql-connector-java-*-bin.jar from the downloaded archive) to the WEB-INF/lib directory of TeamCity web application
  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.
    We recommend using UTF-8 character set for the database. Please consult this document for more details on how to create database with Unicode support.
  4. In the <TeamCity data directory>/config folder rename database.mysql.properties file to database.properties and specify the required settings in this file: 
    connectionUrl=jdbc:mysql://<host>/<database name>
connectionProperties.user=<user>
connectionProperties.password=<password>
maxConnections=50

PostgreSQL

Supported versions: 8.3+

  1. 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.
  2. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file: 
    driverName=org.postgresql.Driver
connectionUrl=jdbc:postgresql://<host>/<database name>
connectionProperties.user=<user>
connectionProperties.password=<password>
maxConnections=50

Oracle

Supported versions: 10g, 11g

  1. Create an Oracle user account for TeamCity (with CREATE SESSION, CREATE TABLE, EXECUTE ON SYS.DBMS_LOCK permissions).
  2. Download the Oracle JDBC Thin driver and put the driver jars (ojdbc14.jar and orai18n.jar) into the WEB-INF/lib directory of TeamCity web application. Please make sure use the specified driver (version 10.2.0.1.0 or higher), earlier driver versions may not function properly with TeamCity.
  3. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file: 
    driverName=oracle.jdbc.driver.OracleDriver
connectionUrl=jdbc:oracle:thin:@<host>:1521:<service>
connectionProperties.user=<user>
connectionProperties.password=<password>
maxConnections=50

Microsoft SQL Server

Supported versions: 2005, 2008

  1. Create new database, ensure that _CI_AI collation is selected for this database.
  2. Create TeamCity user and ensure the 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.
  3. Download latest jTDS driver and put jtds-*.jar driver jar from the archive into WEB-INF/lib directory of TeamCity web application.
  4. In the <TeamCity data directory>/config folder create file database.properties and specify the required settings in this file: 
    driverName=net.sourceforge.jtds.jdbc.Driver
connectionUrl=jdbc:jtds:sqlserver://<host>:1433/<database name>
connectionProperties.user=<user>
connectionProperties.password=<password>
maxConnections=50

To use Windows authentication, just exclude connectionProperties.user and connectionProperties.password properties.

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

Sybase

Supported version: ASE 15.0.3

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. 
    use master
go
create database DB_NAME on general = 4096
                    log on general = 1024 with override
go
exec master..sp_dboption DB_NAME, "allow nulls by default", true
go
exec master..sp_dboption DB_NAME, "select into", true
go
exec master..sp_dboption DB_NAME , "trunc log on chkpt" , true
go
exec master..sp_dboption DB_NAME, "ddl in tran", true
go
exec master..sp_dboption tempdb, "ddl in tran", true
go
checkpoint
go
commit
go
  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. 
    use DB_NAME
go
exec sp_adduser 'TC_USER', 'TC_USER_PASSWORD', null
go
exec sp_modifylogin TC_USER, defdb, 'DB_NAME'
go
grant create default to TC_USER
go
grant create table to TC_USER
go
grant create view to TC_USER
go
grant create rule to TC_USER
go
grant create procedure to TC_USER
go
commit
go
  4. Place JDBC driver (file jconn3.jar that can be found in <Sybase home directory>/jConnect-6_0/classes) into the WEB-INF/lib directory of TeamCity web application.
  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: 
    driverName=com.sybase.jdbc3.jdbc.SybDriver
connectionUrl=jdbc:sybase:Tds:<HOST_NAME>:5000
connectionProperties.user=<TC_USER>
connectionProperties.password=<TC_USER_PASSWORD>
connectingTimeout=17000
connectingAttemptsInterval=3000
validationQuery=select 1
testOnBorrow=false
maxConnections=50



See Also:

Labels