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).

Available configurations are:
  • 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.
Note: The connection pool failover is only supported for application runtime and not during application installation.
In case of Certified Container deployments, turn-off the connection pool failover by setting the connectionpoolFailoverEnable configuration, available in the helm values configuration file, to false while executing the database setup job, dataSetup.enabled=true. It should only be turned-on once the database setup is complete as part of the upgrade, dataSetup.enabled=false.
Tip: From Sterling B2B Integrator v6.2.0.1 onwards, you don't need to turn-off connectionpoolFailoverEnable in the helm configuration during database setup.
Note: The connection pool failover enhancements has been certified (or qualified) on Oracle RAC but is supported for all other compatible database vendors with HA configuration option. We recommend performing an extensive failover testing on your configured database HA instance to fine tune the configurations for errorCodes, probe and retry intervals. before switching on the feature on production.