Supported database vendors include Microsoft SQL Server, PostgreSQL, Oracle and MySQL. It is possible to use other database systems if you provide the database driver.
The following SQL database versions (with the default configurations) are currently tested as Shared Databases:
- Microsoft SQL Server 2019
- PostgreSQL 13.2
- Oracle Database 19c
- MySQL 8.0
In most cases, the default configuration for whatever SQL database system you are using is sufficient.
To avoid connection failures when many users access the Shared Database, the default connection pool should be adjusted to accommodate 6 connections for each user.
However, there are a few exceptions where we recommend changes to the default configuration, listed below:
Character Set Encoding
The character set encoding for MS SQL should be set to UCS-2, all others should use UTF-8 to avoid any potential problems indexing documents containing unusual characters. Note that MySQL 5.6 and 5.7 use latin1 as the default encoding, this should be changed to CHARACTER SET utf8 and COLLATE utf8_unicode_520_ci.
MYSQL
Some changes you may need to make to the MySQL configuration file (my.cnf) to improve performance are as follows:
- innodb buffer pool size=[On a dedicated database server you may set this parameter up to 80% of the machine physical memory size]
- innodb flush log at trx commit=2
- query cache limit=2M
- query cache size=128M
- max allowed packet= 1073741824 (Note this is the maximum size for max allowed packet and can prevent errors when handling large files)
Further optional improvements:
- innodb log file size=256M
- innodb log buffer size=16M
Note: If you change the innodb log file size then you will need to delete the current log files before the server will start up again.
Microsoft SQL Server
The character encoding for MS SQL should be changed to UCS-2. Other default settings for MS SQL server are generally sufficient. However, we strongly recommend using Snapshot Isolation to avoid deadlocks. These can occur when multiple users use the shared database at once and may lead to failure of some actions. This setting becomes necessary if there will be more than a handful of users using the database concurrently. Read more about Snapshot Isolation here.