While dotCMS does cache aggressively and will attempt to limit database traffic on the front end of your site, the database is an important part of your overall dotCMS system and your database performance is vital to dotCMS, especially in the authoring environment.
Supported Databases
The following table lists all of the databases supported by dotCMS, the versions of dotCMS each database is supported in, and official links for each database.
Database | dotCMS Versions | Official Links |
---|---|---|
PostgreSQL | All versions (Community and Enterprise) | Main site / Documentation / Download |
Microsoft SQL Server | Enterprise only | Main site / Documentation |
To see the section of this document specific to each database, click the link for that database in the first column. To view an official site for a database, click the appropriate link in the last column. Please see the dotCMS features list for more information on features supported in different dotCMS versions.
Note: Current versions of dotCMS no longer support MySQL or Oracle databases. If you are upgrading a system using MySQL, this tool can simplify the migration to PostgreSQL. It is believed to work in most cases, though it has not been tested with significantly old versions of the software. We still recommend, as we do generally, that you make backups before performing any major operation on a database.
Supported Versions
New dotCMS installations should always use the most recent release of the selected database. Support for older database versions is provided only to customers with dotCMS support contracts in place before new database versions are released.
For a complete list of the current versions of each database supported, please see the Database portion of the dotCMS Technology Requirements documentation.
Configuration
The Data source can be initialized in multiple different ways - we always recommend using environmental variables to configure your datasource. Any of the following methods can be used, and these sources will be checked in the order they are listed here:
Database Configuration File (db.properties)
Database configuration should be done using environmental variables.
The db.properties file contains parameters you can modify to change how dotCMS connects to your database.
Note: Previous versions of dotCMS allowed configuration of the database through the context.xml file in the WEB-INF folder. This file is still supported for backward compatibility, but is deprecated, and will stop being supported in a future release.
System Variables
The following system variables can be set to initialize a datasource using this option:
System Environment | Default Value |
---|---|
DB_DRIVER | org.postgresql.Driver |
DB_BASE_URL | jdbc:postgresql://localhost/dotcms |
DB_USERNAME | |
DB_PASSWORD | |
DB_MAX_TOTAL | 60 |
DB_MIN_IDLE | 10 |
connection_db_max_wait | 60000 |
DB_VALIDATION_QUERY | SELECT 1 |
DB_LEAK_DETECTION_THRESHOLD | 60000 |
DB_DEFAULT_TRANSACTION_ISOLATION | |
DOT_POSTGRES_PUBSUB_JDBC_URL | if primary datasource is postgres, dotCMS attempts to use the same datasource for the PUB/SUB connection. Setting this env variable allows you to override this default behavior. |
PostgreSQL
dotCMS uses Postgres by default and for our internal cloud infrastructure as well. There are some caveats:
- dotCMS does not support AWS Aurora Postgres Serverless
- dotCMS does not test against AWS Aurora Postgres or other cloud provider's managed Postgres services
- dotCMS recommends using AWS Postgres RDS.
Database Specific Requirements and Known Issues
The following sections cover the specific requirements and known issues for each specific database supported by dotCMS.
MS SQL Server
Note: Use of the MS SQL Server database is only supported in dotCMS Enterprise editions. Please see the list of dotCMS versions for more information on features supported in different version of dotCMS.
Important First Steps
Authentication
Important: You must use SQL user authentication and not Windows User (integrated) authentication. There are no longer any supported open source tools to provide authentication of SQL Server databases using integrated Windows authentication.
The database user which is used to create the dotCMS database should have the db_owner role on the dotcms database
Transaction Locking
Microsoft SQL Server databases can experience issues with transaction locking. Make sure to run the following command on your db BEFORE running dotCMS or establishing any connections to the dotCMS database.
ALTER DATABASE dotcms SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE dotcms SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
Note:
- Make sure to replace “dotcms” in the above commands with the name of your own SQL server database.
For more information on these commands, please see the MS SQL ALTER DATABASE documentation.
Checking Existing SQL Server Settings
If you wish to find out what your current settings are, run the following query:
SELECT sd.is_read_committed_snapshot_on,sd.snapshot_isolation_state_desc
FROM sys.databases AS sd
WHERE sd.[name] = 'dotcms';
Notes:
- If you need more information on SQLServer regarding row locking and isolation levels see the Microsoft SQL Server 2005 Row Versioning-Based Transaction Isolation document.
- Additional information is available from Atlassian who had the same issue with SQLServer.
- If you need to execute SQL queries which might lock a table or row while others are connected to the database, make sure you run
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
first.- If you are writing a dotCMS plugin and you get your connection from the DBConnection factory this will already be done for you.