Set up External Database

Recommended database server settings:

• Use InnoDB storage engine.

• Use utf8mb4 character set (or utf8 if MySQL version is 5.5.2 or earlier).

• Use case-sensitive collation.

• Make sure that the time zone of the JVM running TeamCity and that of MySQL instance are the same using the my.cnf file or by configuring time zones at the OS level.

• The server's sql_require_primary_key system variable must be set to OFF since TeamCity manages tables without primary keys.

• See also recommendations for MySQL server settings.

The MySQL user account that will be used by TeamCity must be granted all permissions on the TeamCity database. This can be done by executing the following SQL commands from the MySQL console:

create database <database-name> collate utf8mb4_bin; -- or utf8_bin on MySQL 5.5.2 or earlier
create user <user-name> identified by '<password>';
grant all privileges on <database-name>.* to <user-name>;
grant process on *.* to <user-name>;

JDBC driver installation:

1. Download the MySQL JDBC driver. Make sure to use a version compatible with your server.

2. For Windows, run the installer in "Custom" mode and select "MySQL Connectors | Connector/J" to install a standalone connector.

3. Copy mysql-connector-j-<version>.jar from the downloaded archive (Linux) or the installation folder (Windows) into the <TeamCity Data Directory>/lib/jdbc directory (remove the existing files there, if any). Proceed with the TeamCity setup.

1. Create an empty database for TeamCity in PostgreSQL.

   • Make sure to set up the database to use UTF8.

   • Grant permissions to modify this database to the user account used by TeamCity to work with the database.

2. See also recommendations for PostgreSQL server settings.

TeamCity does not specify which schema will be used for its tables. By default, PostgreSQL creates tables in the public schema. TeamCity can also work with other PostgreSQL schemas. To switch to a different schema, create a schema named exactly as the username. This can be done using the pgAdmin tool or with the following SQL:

create schema teamcity authorization teamcity;

The schema has to be empty (it must not contain any tables).

Download the required PostgreSQL JDBC42 driver and place it into the <TeamCity Data Directory>/lib/jdbc directory (remove the existing files there, if any). Proceed with the TeamCity setup.

Create an Oracle user account/schema for TeamCity.

tip

TeamCity uses the primary character set (char, varchar, clob) for storing internal text data and the national character set (nchar2, nvarchar, nclob) for storing the user input and data from external systems, like VCS and NTLM.

• Make sure that the national character set of the database instance is UTF or Unicode.

• Grant the CREATE SESSION and CREATE TABLE permissions to a user whose account will be used by TeamCity to work with this database.

On the first connect, TeamCity creates all the necessary tables and indices in the user's schema. (Note: TeamCity never attempts to access other schemas even if they are accessible.)

Make sure the TeamCity user has quota for accessing the table space.

1. Get the Oracle JDBC driver. Supported driver versions are 11.1 and later. The Oracle JDBC driver must be compatible with your Oracle server.
   Place the following files:

   • ojdbc8.jar (or ojdbc6.jar, ojdbc7.jar depending on your database version)

   • orai18n.jar (can be omitted if missing in the driver version)
     into the <TeamCity Data Directory>/lib/jdbc directory (remove the existing files there, if any).
     It is strongly recommended locating the driver in your Oracle server installation. Contact your DBA for the files if required. Alternatively, download the Oracle JDBC driver from the Oracle website.

2. Proceed with the TeamCity setup.

tip

TeamCity uses the primary character set (char, varchar, text) for storing internal text data and the national character set (nchar, nvarchar, ntext) for storing the user input and data from external systems, like VCS and NTLM.

1. Create a new database. As the primary collation, use the case-sensitive collation (collation name ending with _CS_AS) corresponding to your locale.

2. Create a TeamCity user and ensure that this user is the owner of the database (grant the user dbo rights), which will give the user the ability to modify the database schema. For SSL connections, ensure that the version of MS SQL server and the TeamCity version of Java are compatible. We recommend using the latest version of the SQL server.

3. Allocate sufficient transaction log space depending on how intensively the server will be used. The recommended setup is not less than 1 GB.

4. Make sure the SQL Server browser is running.

5. Make sure the TCP/IP protocol is enabled for the SQL Server instance.

1. Download the Microsoft JDBC driver v12.6+ (sqljdbc_12.6.x package) from the Microsoft Download Center.

2. Unpack the downloaded package into a temporary directory. Copy the mssql-jdbc-<version>.jre8.jar from the just downloaded package into the <TeamCity Data Directory>/lib/jdbc directory (remove the existing files there, if any). MS SQL integrated security (Windows authentication) requires installing sqljdbc_auth.dll from the driver package as per instructions.

3. Proceed with the TeamCity setup.

It is not recommended using the jTDS JDBC driver, as it has known issues with using Unicode characters.

If you use the driver (jtds text appears in the connectionUrl of database.properties), it is highly recommended switching the native driver:

1. Create the server backup including the database.

2. Stop the server and configure the server to use the native Microsoft JDBC driver as noted in the section above.

3. Restore the database from the backup into the new MS SQL database.

4. Run the server.