Configuring connection pool failover
Introduction
When the database is configured for HA, the application can be configured to automatically detect primary database node failover and drain the connections pools currently mapped to the primary node. New connections mapped to the secondary database node, which takes over the primary role after database failover, get created and added to the pool as per the minimum pool configuration. Existing connections, in use, during the database failover will be marked as dirty and closed on return to the pool. Application transactions with the existing connections will fail and if configured for retry, will proceed with new connections from the pool. The application detects a failover based on a set of configurable SQL error codes. There is also a connection pool failover monitor which will periodically check for failover error conditions. A default set of error codes have been defined out of the box for each supported database vendor, namely, Oracle, MSSQL Server and DB2. Additional error codes can be added to the list based on failover testing and error conditions specific to each database vendor deployed on-premise or available as a cloud service. There are additional configuration options available to fine tune the failover probe interval, number of retries and retry intervals.
Configuration properties
Connection pool failover configurations are available in jdbc.properties which can be overridden using customer override properties file or customization UI with the existing prefix for jdbc.properties (jdbcService).
- connectionPool.failover.enable: The connection pool failover feature can be turned on or off with this configuration. The default it is set to false. It can be turned on once the database is configured for HA and failover testing has been performed.
- connectionPool.failover.probeIntervalSeconds: The time interval (in seconds) at which the connection pool monitor will check for failover error conditions and refresh connection pools, if required. The default is set to 300 seconds.
- connectionPool.failover.numberOfRetries: The number of retries the connection pool failover check will perform before proceeding with pool refresh. The default is set to 3.
- connectionPool.failover.errorCodes: The failover SQL error codes to check for detecting failover condition specific to the database vendor. A default set of error codes is set for each of the supported database vendors.
- connectionPool.failover.retryIntervalSeconds: The time interval, in seconds, between each of the retries. The default is set to 5 seconds.
Configuring the Connection Pool Failover properties
There are multiple ways to update the connection pool failover properties. The recommended method for the current properties is to update the configurations using customer override property file or Customization UI.