Version: Deploy 22.3

Configure the Database and Artifacts Repository

This topic covers the storage options in Deploy, which uses a database to store all data, including configuration items (CIs), deployment packages, logging, and more. Deploy can use either the filesystem or a database for storing binary artifacts (deployment packages), CIs, and CI history.

Supported Databases

See Installation Prerequisites.

Using a Database Server

When you install (or upgrade Deploy to a latest version), Deploy creates and maintains the database schema. The database administrator requires the following permissions on the database: REFERENCES, INDEX, CREATE, DROP, in addition to the permissions used in operation: SELECT, INSERT, UPDATE, DELETE.
Table definitions in Deploy use limited column sizes. To prevent these limits from restricting users in how they use Deploy, you must configure this for all the supported databases. For example, the ID of a CI is a path-like structure and consists of the concatenation of the names of all the parent folders for the CI. A restriction is set on the length of this combined structure. The maximum character length for all the supported databases is set to 850 chars.
Deploy can be configured to use two different database connections: one for primary Deploy data and one for the task archive. Both database connections can be configured in the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file. The operational database connection can be configured under the repository key and the database connection for the task archive can be configured under the reporting key. By default, the default configuration for the repository database connection is also used for the reporting connection.

Also see...

The Database Configuration File—`deploy-repository.yaml`

Use the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file for configuring the database settings.

After the first run, sensitive values in the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file such as passwords are encrypted with base64-encoded encryption.

In addition, use the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file for configuring the artifacts settings.

Artifacts in Deploy

Deploy stores user supplied artifacts in the filesystem, such as scripts or deployment packages (jar or war files). These are, by default, stored in the filesystem. You can store these artifacts in the database, if required. Deploy can use only one of these options at any time. You must configure where to store the artifacts in the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file.

Specify the type of artifact storage to use. Valid values are:

file: uses the filesystem for storing artifacts
db: uses the database for storing artifacts

Set the type property to file to store the artifacts in the filesystem: type: file

xl.repository:
  artifacts:
    root: artifacts
    type: file

Set the type property to db to store the artifacts in the database: type: db

xl.repository:
  artifacts:
    type: db

Preparing the database and repository

Before installing Deploy, create an empty database. Deploy will create the database schema during installation.

The account that accesses the database must have permissions to create tables during the initial installation and, later, it must have permissions to write, read, and delete from tables.

There are no requirements for the character set of the database.

Important: Deploy must initialize the repository before it can be used. You must run The Deploy setup wizard and initialize the repository after making any changes to the repository configuration. For more information, see Run the server setup wizard.

The following set of SQL privileges are required.

During installation / upgrade:

REFERENCES
INDEX
CREATE
DROP

During operation:

SELECT
INSERT
UPDATE
DELETE

Configure schema name

Optionally, you can configure the database schema name in the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml file.

Use the xl.repository.database.db-schema-name property for the main database configuration and the xl.reporting.database.db-schema-name property for the reporting database.

Using Deploy with MySQL

To use Deploy with MySQL, ensure that the JDBC driver for MySQL JAR file is located in XL_DEPLOY_SERVER_HOME/lib or on the Java classpath.

Important: The user ID accessing the MySQL database must have the following permissions:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, DROP, and INDEX on dbname.todbuser*@host for database initialization and for Deploy version upgrades.
GRANT SELECT, INSERT, UPDATE, and DELETE on dbname.todbuser*@host for ongoing usage.

Configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

Here is a sample configuration for MySQL:

xl.repository:
  database:
    db-driver-classname: com.mysql.jdbc.Driver
    db-url: jdbc:mysql://localhost:3316/xld?autoReconnect=true&useLegacyDatetimeCode=false
    db-password: samplepassword
    db-username: sample-user
    connection-timeout: 30000 
    max-pool-size: 10

Notes:

Name the database created in mysql (that will connect with xl-deploy) as xld.
The MySQL database does not support full unicode character set. For more information, see MySQL documentation.
If you are using Deploy version 8.5 or earlier, the db-url is jdbc:mysql://localhost:3306/xldrepo?autoReconnect=true&useSSL=false&useLegacyDatetimeCode=false.
For MySQL, Deploy versions 8.5 and earlier require the innodb_large_prefix option to be ON. For more information, see the MySQL version specific documentation.
MySQL version 5.7 default max_allowed_packet setting needs to be changed to 64M. For other recommended settings please see Production Environment Installation section.

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Important: As of version 8.6, Deploy officially supports database configuration with MySQL versions 5.7 and 8.0. Starting with MySQL version 8.0, the innodb_large_prefix option is no longer supported. If you want to upgrade your database to MySQL version 8.0, you must first upgrade Deploy to version 8.6 or later.

To upgrade to MySQL version 8.0:

After you upgraded Deploy to version 8.6 or later, stop your Deploy instance.
Back up your database and follow the steps from the upgrade procedure in the MySQL documentation.

Run the following SQL commands:

update `DATABASECHANGELOG` set `MD5SUM` = '8:9bad926a268f4ce1b8e4c9a47cfe6f82'  where `AUTHOR` = 'XebiaLabs' and `FILENAME` = 'SOURCE_ARTIFACTS' and `MD5SUM` = '8:489e9467592bebcb1ec822ea94e1d28b';
drop index `XLD_FILE_ART_LOCATION_IDX` on `XLD_FILE_ARTIFACTS`;
alter table `XLD_FILE_ARTIFACTS` modify column location varchar(255) not null;
create index XLD_FILE_ART_LOCATION_IDX on `XLD_FILE_ARTIFACTS` (location);

Start the Deploy server.

Deploy Collation and Character Set

Digital.ai Deploy supports unicode character set utf8mb4 and utf8mb3.

utf8mb4 charset with collation utf8mb4_0900_as_cs or utf8mb4_0900_ai_ci
utf8mb3 with utf8mb3_general_ci (deprecated in MySQL 8.0)

For more information, see MySQL Documentation

To specify the character set and collation at server start up, use the --character-set-server and --collation-server options.

For example:

If you are running a MySQL server, add the following options to mysql.cnf file.

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

If you are running MySQL in a Docker container, either build your own MySQL image with the updated mysql.cnf file (as shown in the earlier example), or update the docker-compose file with the following command to start the MySQL daemon.
```
mysql:
    image: mysql:8.0
    ..
    command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_0900_ai_ci']
```

Using Deploy with DB2

Important: Note that DB2 is no longer supported as the database of choice for new sites. However, existing DB2 sites are supported until further notice about dropping support for DB2.

To use Deploy with IBM DB2, ensure that the JDBC driver for DB2 JAR file is located in XL_DEPLOY_SERVER_HOME/lib or on the Java classpath.

Configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

This is a sample configuration for DB2:

xl.repository:
  database:
    db-driver-classname: com.ibm.db2.jcc.DB2Driver
    db-url: jdbc:db2://localhost:50000/xldrepo
    db-password: sample-password
    db-username: sample-user
    connection-timeout: 30000

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Using Deploy with Oracle

To use Deploy with Oracle, ensure that the JDBC driver for Oracle JAR file is located in XL_DEPLOY_SERVER_HOME/lib or on the Java classpath.

Configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

This is a sample configuration for Oracle:

xl.repository:
  database:
    db-driver-classname: oracle.jdbc.OracleDriver
    db-url: jdbc:oracle:thin:@//localhost:1521/xe
    db-password: samplepassword
    db-username: sample-user
    connection-timeout: 30000 
    max-pool-size: 10

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Note: If you use the TNSNames Alias syntax to connect to Oracle, you may need to inform the driver of where to find the TNSNAMES file. For more information, see the Oracle documentation.

Using Deploy with Microsoft SQL Server

To use Deploy with Microsoft SQL Server, ensure that the JDBC driver for SQL Server JAR file is located in XL_DEPLOY_SERVER_HOME/lib or on the Java classpath.

Configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

This is a sample configuration for SQL Server:

xl.repository:
  database:
    db-driver-classname: com.microsoft.sqlserver.jdbc.SQLServerDriver
    db-url: jdbc:sqlserver://localhost:1433
    db-password: samplepassword
    db-username: sample-user
    connection-timeout: 30000 
    max-pool-size: 10

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Unlike other supported databases, MS SQL Server does not have Multi Version Concurrency Control (MVCC) activated by default. Deploy requires this feature to function correctly. For more information on the settings described below, see this MSDN article.

After you create a database, you can enable snapshot isolation mode by executing the following commands on an SQL Server:

ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON;        

Microsoft SQL Server—Best Practices

Turn Off the `Full-Text Search` Feature

Microsoft SQL Server and Azure SQL Database support installation options that let users and applications run full-text queries against character-based data in SQL Server tables. Deploy does not use this feature and we also recommend you to turn off this feature in the setup in order to prevent consumption of resources on an unused feature.

Have Dedicated Database File Groups for Deploy Schemas

The Microsoft SQL Server by default creates one PRIMARY file group, we advise you to create two additional data files (one data file and one log file) in the new file group and make that file group the default for a database. This gives you more flexibility about where to place the two data files for better performance. Additionally, having a data file and a log file on separate discs would result in better disc usage performance.

Check the example on how to create separate filegroups. Follow the recommendations found here.

Add SQL Server Agent Job for Periodical Flush of `ad hoc plan cache`

Having the ad hoc plan cache flushed can alleviate some of the internal memory pressure your database may face over time. The SQL Agent Job example given below will run every 8 hours and flush the database ad hoc and prepared plan cache.

USE [msdb]
GO

BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0

 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Flush Ad Hoc Plan Cache',
   @enabled=1,
   @notify_level_eventlog=0,
   @notify_level_email=0,
   @notify_level_netsend=0,
   @notify_level_page=0,
   @delete_level=0,
   @description=N'Flush Ad Hoc and Prepared Plan Cache',
   @category_name=N'Database Maintenance',
   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Flush Ad Hoc Plan Cache',
   @step_id=1,
   @cmdexec_success_code=0,
   @on_success_action=1,
   @on_success_step_id=0,
   @on_fail_action=2,
   @on_fail_step_id=0,
   @retry_attempts=0,
   @retry_interval=0,
   @os_run_priority=0, @subsystem=N'TSQL',
   @command=N'-- Clear ad hoc and prepared plan cache
         DBCC FREESYSTEMCACHE (''SQL Plans'');',
   @database_name=N'master',
   @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Flush Ad Hoc Plan Cache',
   @enabled=1,
   @freq_type=4,
   @freq_interval=1,
   @freq_subday_type=8,
   @freq_subday_interval=8,
   @freq_relative_interval=0,
   @freq_recurrence_factor=0,
   @active_start_date=20141229,
   @active_end_date=99991231,
   @active_start_time=0,
   @active_end_time=235959,
   @schedule_uid=N'7077f7eb-8d6e-44a5-b8da-4889afe805df'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Using Deploy with PostgreSQL

Driver: PostgreSQL JDBC driver.

Place the driver JAR file in the XL_DEPLOY_SERVER_HOME/lib folder.

Configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

This is a sample configuration for PostgreSQL:

xl.repository:
  database:
    db-driver-classname: org.postgresql.Driver
    db-url: jdbc:postgresql://localhost/postgres
    db-password: samplepassword
    db-username: sample-user
    connection-timeout: 30000 
    max-pool-size: 10

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Note: If you are using Postgres 11.6, then you must enable Deploy to discover IPv4 instead of IPv6. To make this change, add the following configuration to your xld-wrapper.conf.common file : wrapper.java.additional.6=-Djava.net.preferIPv4Stack=true

Using Deploy with Derby

Important: Derby is not recommended for production use. If you wish to use Derby in production we strongly advise you to reconsider.

For existing users: If you are not sure if you are using Derby here's how you can check:

You are using Derby if your deploy-repository.yaml file has no xl.repository.database. settings such as db-driver-classname or db-url.
If the db-driver-classname is configured as "org.apache.derby.iapi.jdbc.AutoloadedDriver" and/or the db-url is configured as jdbc:derby:...

For new and existing users:

If you still wish to continue using Derby in production then you must configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

This is a sample configuration for Derby:

xl.repository:
  database:
    db-driver-classname: "org.apache.derby.iapi.jdbc.AutoloadedDriver"
    db-url: jdbc:derby:repository/db;create=true
    db-password: samplepassword
    db-username: sample-user
    connection-timeout: 30000 
    max-pool-size: 10

Important: connection-timeout allows you to specify the connection timeout value for the Hikari connection pool. The timeout value should always be greater than 250 milliseconds. The default value is 30000 milliseconds.

Configuration Keys Available

Note: The configuration keys specified in the table below are used as an example. H2/Derby should not be used in production and is not supported with the current version of XL_Deploy. You should use a production-grade database. To use other database connection strings in configuration keys, see [Database-specific configuration in Deploy](##Database-specific configuration in Deploy)

Here are the configuration keys available in xl.database, xl.reporting and xl.cluster.membership.jdbc:

key	Description	Default value (`xl.database`)	Default value (`xl.reporting`)	Default value (`xl.cluster.membership.jdbc`)
`db-driver-classname`	Driver class name	"org.h2.Driver"	"org.apache.derby.iapi.jdbc.AutoloadedDriver"	use `xl.database.db-driver-classname`
`db-url`	Database jdbc url	"jdbc:h2:file:./repository/db; DB_CLOSE_ON_EXIT=FALSE"	"jdbc:derby:archive/db; create=true;upgrade=true"	value of `xl.database.db-url`
`db-username`	Database username	"sa"	""	value of `xl.database.db-username`
`db-password`	Database password	""	""	value of `xl.database.db-password`
`pool-name`	This property represents a user-defined name for the connection pool and appears mainly in logging and JMX management consoles to identify pools and pool configurations.	"RepositoryPool"	"ReportingPool"	"ClusterPool"
`max-pool-size`	This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. This value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to `connection-timeout` before timing out.	10	10	1
`connection-timeout`	This property controls the maximum number of milliseconds that a client will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. The lowest acceptable connection timeout is 250 ms	30 seconds	30 seconds	30 seconds
`max-life-time`	This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, when it is closed it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. Important: We strongly recommend setting this value. It should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), which is subject to the `idle-imeout` setting.	30 minutes	30 minutes	30 minutes
`idle-timeout`	This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when `minimum-idle` is defined to be less than `max-pool-size`. Idle connections will not be retired once the pool reaches `minimum-idle` connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10 seconds.	10 minutes	10 minutes	10 minutes
`minimum-idle`	This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than `max-pool-size`, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool.	10	10	1
`leak-connection-threshold`	This property controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. The lowest acceptable value for enabling leak detection is 2 seconds.	15 seconds	15 seconds	15 seconds

Supported Databases​

Using a Database Server​

The Database Configuration File—deploy-repository.yaml​

Artifacts in Deploy​

Preparing the database and repository​

Configure schema name​

Using Deploy with MySQL​

Deploy Collation and Character Set​

Using Deploy with DB2​

Using Deploy with Oracle​

Using Deploy with Microsoft SQL Server​

Microsoft SQL Server—Best Practices​

Turn Off the Full-Text Search Feature​

Have Dedicated Database File Groups for Deploy Schemas​

Add SQL Server Agent Job for Periodical Flush of ad hoc plan cache​

Using Deploy with PostgreSQL​

Using Deploy with Derby​

Configuration Keys Available​

Supported Databases

Using a Database Server

The Database Configuration File—`deploy-repository.yaml`

Artifacts in Deploy

Preparing the database and repository

Configure schema name

Using Deploy with MySQL

Deploy Collation and Character Set

Using Deploy with DB2

Using Deploy with Oracle

Using Deploy with Microsoft SQL Server

Microsoft SQL Server—Best Practices

Turn Off the `Full-Text Search` Feature

Have Dedicated Database File Groups for Deploy Schemas

Add SQL Server Agent Job for Periodical Flush of `ad hoc plan cache`

Using Deploy with PostgreSQL

Using Deploy with Derby

Configuration Keys Available