Configure the SQL repository
Deploy uses a repository to store all data such as configuration items (CIs), deployment packages, logging, etc. Deploy can use the filesystem or a database for binary artifacts (deployment packages) and CIs and CI history.
Supported databases
By default, Deploy uses the filesystem to store all data in the repository. For production use, we recommended that you use an industrial-grade external database server. The following databases are supported:
- PostgreSQL versions 9.3, 9.4, 9.5, 9.6, 10.1, 10.5 and 11.6
-
MySQL
- For Deploy versions 8.6 and later: MySQL versions 5.7 and 8.0
- For Deploy versions 8.5 and earlier: MySQL versions 5.5, 5.6, and 5.7
- Oracle 11g, 12c and 19c
- Microsoft SQL Server 2012 and later
- DB2 versions 10.5, 11.1 and 11.5
Location of the repository
By default, the repository is located in XL_DEPLOY_SERVER_HOME/repository
. You can change the location of the repository in the XL_DEPLOY_SERVER_HOME/conf/xl-deploy.conf
file.
Using a database server
Important: The default setting in Deploy is to use the internal database that stores the data on the file system. This is intended for temporary use and is not recommended for production use.
- When the installation is upgraded to a new 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/conf/xl-deploy.conf
file. The main database connection can be configured under the repository
key and the database connection for the task archive can be configured under the reporting
key. The default configuration for the repository
database connection is also used for the reporting
connection.
For information, see Back up Deploy and Configure active/hot-standby.
Artifacts in Deploy
In addition to storing the data, Deploy stores user supplied artifacts in the repository, such as scripts or deployment packages (jar
or war
files). These can be stored on the file system, which is the default setting, or in the database server. Deploy can use only one of these options at any time. The repository must be configured before using Deploy in a production setting. This setting can be configured in the XL_DEPLOY_SERVER_HOME/conf/xl-deploy.conf
file.
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
The configuration file: xl-deploy.conf
All configuration is done in XL_DEPLOY_SERVER_HOME/conf/xl-deploy.conf
.
This file is in HOCON format.
After the first run, passwords in the configuration file will be encrypted and replaced with base64-encoded encrypted values.
Configure artifacts settings in the XL_DEPLOY_SERVER_HOME/conf/xl-deploy.conf
file.
Specify the type of artifact storage to use. Valid values are:
file
: use the specified file system location for storing artifacts.db
: use the database for storing artifacts.
Set the location for artifact storage on file system. Only active when type = "file"
.
artifacts {
type = "file"
root = ${xl.repository.root}"/artifacts"
}
The value of ${xl.repository.root}
is repository
by default and is also used to configure the location of the internal database files.
Database-specific configurations in Deploy
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 userid accessing the MySQL database must have the following permissions:
- GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, DROP, and INDEX on
dbname
.* to dbuser@host for database initialization and for Deploy version upgrades. - GRANT SELECT, INSERT, UPDATE, and DELETE on
dbname
.* to dbuser@host for ongoing usage.
Configure the XL_DEPLOY_SERVER_HOME/conf/xl-deploy.conf
to point to the database schema.
This is a sample configuration for MySQL:
xl {
repository {
database {
db-driver-classname="com.mysql.jdbc.Driver"
db-password="samplepassword"
db-url="jdbc:mysql://localhost:3316/xld?autoReconnect=true&useLegacyDatetimeCode=false"
db-username=sample-user
max-pool-size=10
connection-timeout=30000
}
}
}
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
isjdbc: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 beON
. For more information, see the MySQL version specific documentation.
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.
- Backup your database and follow the steps from the upgrade procedure in the MySQL documentation.
- Execute these 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 Deploy.
Using Deploy with 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/conf/xl-deploy.conf
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-password="sample-password"
db-url="jdbc:db2://localhost:50000/xldrepo"
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/conf/xl-deploy.conf
to point to the database schema.
This is a sample configuration for Oracle:
xl {
repository {
database {
db-driver-classname="oracle.jdbc.OracleDriver"
db-password="samplepassword"
db-url="jdbc:oracle:thin:@//localhost:1521/xe"
db-username=sample-user
max-pool-size=10
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.
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 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/conf/xl-deploy.conf
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-password="samplepassword"
db-url="jdbc:sqlserver://localhost:1433"
db-username=sample-user
max-pool-size=10
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.
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;
Best practices for installation and Schema creation for SQL Server
Turn off 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
Use 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/conf/xl-deploy.conf
to point to the database schema.
This is a sample configuration for PostgreSQL:
xl {
repository {
database {
db-driver-classname="org.postgresql.Driver"
db-password="samplepassword"
db-url="jdbc:postgresql://localhost/postgres"
db-username=sample-user
max-pool-size=10
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.
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
xl-deploy.conf
file has noxl.repository.database.
settings such asdb-driver-classname
ordb-url
. - If the
db-driver-classname
is configured as"org.apache.derby.jdbc.AutoloadedDriver"
and/or thedb-url
is configured asjdbc: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/conf/xl-deploy.conf
to point to the database schema.
This is a sample configuration for Derby:
xl {
repository {
database {
db-driver-classname = "org.apache.derby.jdbc.AutoloadedDriver"
db-url = "jdbc:derby:repository/db"
db-username = ""
db-password = ""
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.
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
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.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. |
64 | 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. |
64 | 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 |