Configure an external database

During the CloudBees CD/RO server installation, if you elected not to install the built-in (default) database, you need to configure an alternate database. A CloudBees CD/RO enterprise license is required to configure an alternate database. For more information about supported databases, see Database requirements. For installation instructions, see Configuring CloudBees CD/RO to Use an Alternate Database.

Database interactions

Your database administrator (DBA) must create a database for use specifically by CloudBees CD/RO. The CloudBees CD/RO server interacts with the database using a JDBC driver for each of the databases that CloudBees CD/RO supports.

The first step in any interaction is to present user credentials to the database. This information is stored in the CloudBees CD/RO database.properties file as a user name plus a password. The password is stored as an encrypted string, using the passkey generated by the server.

Database user

For MS SQL Server and MySQL, the DBA creates a database user for use specifically by CloudBees CD/RO. For Oracle, the DBA must create a database user for use specifically by CloudBees CD/RO.

The CloudBees CD/RO database user must have permissions to add or delete rows from the database at all times. The database user must also have rights to create or delete tables, and add or remove a columns, indices, and constraints to a table at certain defined times.

When the CloudBees CD/RO server first starts up, CloudBees CD/RO creates a schema in the specified database, so the database user should be the owner of the CloudBees CD/RO database. This allows CloudBees CD/RO to make the required schema changes.

If the CloudBees CD/RO server cannot connect to the database, it continues to wait for a valid database configuration. Check the log files for a successful database connection.

Default database ports

The supported external databases use the following ports:

Port	Used by
1521	Oracle
1433	Microsoft SQL Server
3306	MySQL
5432	PostgreSQL

MySQL prerequisites

For non-production environments, only, if you intend to run MySQL on the same server as your CloudBees CD/RO server, then install with the advanced mode option that avoids installing the MariaDB built-in database. In production environments, you must install and run MySQL on a separate server.

Database user permissions

CloudBees supports installations only where the database user has rights to create and delete tables at all times. To upgrade the MySQL databases, the database user must also have permissions to create and delete schema (databases) for the duration of the upgrade. For security reasons, permissions granted to database users to create and delete schema in multi-tenant MySQL databases may be revoked after the upgrade process is complete.

Installing the JDBC driver

For MySQL, the JDBC driver is not installed by the CloudBees CD/RO installer (for licensing reasons). It must be downloaded and installed separately from the MySQL website. For more information, go to Installing the MySQL JDBC Driver .

Setting the default MySQL server timezone

By default, a MySQL instance uses the timezone from the operating system, which might cause the following bootstrap failure if the instance is using the MySQL JDBC Connector/J driver version 8.0 or later:

{noformat}
2018-07-27T03:47:35.070 | ERROR | bootstrap | HikariPool
| {PRODUCT} - Exception during pool initialization.
java.sql.SQLException: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
…▼

If this failure occurs, ensure that the database timezone is set to a supported timezone and that it matches the CloudBees CD/RO server timezone. Apply the fix to the MySQL instance (not to the CloudBees CD/RO server) as described in https://bugs.mysql.com/bug.php?id=86425. For more information about this issue and for a list of supported timezones, see https://bugs.mysql.com/bug.php?id=85816 and https://stackoverflow.com/a/47108511.

If it is not possible to set the MySQL database timezone then set it on the CloudBees CD/RO server: configure the JDBC URL in <DATA_DIR>/conf/database.properties to include the serverTimezone parameter set to the database timezone. For example, serverTimezone=America/Los_Angeles.

In CloudBees CD/RO v10.0 and later, the CloudBees CD/RO server sets this in the generated JDBC URL in <DATA_DIR>/conf/database.properties at install time.

Configuring database settings

You must configure UTF-8 character encoding and case-insensitive collation with utf8 and utf8_general_ci respectively. Of note:

The utf8mb4 character set is not supported because it requires four bytes per character, which puts this width beyond the key limits.
The database must use the InnoDB MySQL DB storage engine with the innodb_large_prefix option enabled because of maximum 3072 bytes key limits.
- MySQL versions previous to 5.7 does not enable innodb_large_prefix by default. It must be explicitly enabled.
- MySQL 5.7 and later versions enables innodb_large_prefix by default.
- MySQL 8.0 and later versions eliminates the innodb_large_prefix setting and defaults the behavior to true.
MySQL 8.0 and later versions default the character set and collation to utf8mb4 and utf8mb4_0900_ai_ci respectively. You must explicitly set these to utf8 and utf8_general_ci.

Tuning memory allocation

To tune memory allocation, see the KBEC-00038 - Improving CloudBees CD/RO server performance by tuning memory allocation knowledge base article.

Oracle RAC

You can configure CloudBees CD/RO to work with Oracle Real Application Clusters (RAC), which provides software for clustering and high availability in Oracle database environments. For instructions, see the KBEC-00064 - Using Oracle RAC Server with CloudBees CD/RO knowledge base article.

Configure the Oracle database

The following permissions and settings are required for installation and runtime operations for the CloudBees CD/RO Oracle database user.

The required permissions are needed for:

Create session
Resource
Connect

The following SQL command grants these permissions to an example user named flowbuster:

grant create session, resource, connect to flowbuser;▼

Additionally, the open_cursors parameter determines the maximum number of cursors (SQL statements) that can be open simultaneously in a session with CloudBees CD/RO.

CloudBees recommends issuing the following SQL command to set the open_cursors parameter to 1000:

alter system set open_cursors = 1000 scope = both;▼

PostgreSQL prerequisites

CloudBees CD/RO supports PostgreSQL databases, which include a large set of the SQL standard and features, robust support for complex queries, and multiversion concurrency. PostgreSQL also offers extensibility, and you can add new data types, functions, operators, and more. For information on which PostgreSQL database versions are supported by specific CloudBees CD/RO releases, refer to supported databases for traditional installations.

To connect PostgreSQL databases to CloudBees CD/RO, administrators must enable International Components for Unicode (ICU) collation support. Refer to the Collation section of the PostgreSQL documentation for more information.