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 filesystem. 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 filesystem, 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.

Configure Schema Name

You should use specify schema name in queries ${xl.repository.database.db-schema-name} for the main database configuration and the ${xl.reporting.database.db-schema-name} for reporting

Note: In case no schema name is defined through the configuration, by default using the connection string schema name.

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:

1. Name the database created in MySQL (that will connect with xl-deploy) as xld.
2. The MySQL database does not support full Unicode character set. For more information, see MySQL documentation.
3. 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.
4. 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.

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:

1. After you upgraded Deploy to version 8.6 or later, stop your Deploy instance.
2. Backup your database and follow the steps from the upgrade procedure in the MySQL documentation.
3. 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);

1. 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 Schema.

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:

1. You are using Derby if yourxl-deploy.conf file has no xl.repository.database. settings such as db-driver-classname or db-url.
2. If the db-driver-classname is configured as "org.apache.derby.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/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;create=true"
            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: