We are facing a problem in a java based application where the number of database connections to a SQL server database is always increasing. The database is configured in a HA group and the application connection to the database has been configured via a SQL Server always-on listener. Connection pooling has been implemented using dbcp2.
To troubleshoot the problem we tried running the application workload in two different database connectivity modes. The first mode using the always-on listener and the second by connecting directly to the primary database node of the HA group.
We found that the continuous increase in the number of database connections was found only when the connections were configured using the always-on listener. In this case the connections keep increasing leading to hundreds of connection to the database much beyond the limits set as part of the connection pool parameters.
When the same application workload was run with the database connection directly to the primary node, there was no dramatic increase in the number of connections. The number stayed well within the limits set for the connection pool.
The following connection pool parameters are being set.
jdbc.driverClassName=com.microsoft.sqlserver.
jdbc.SQLServerDriver
jdbc.maxActive= 20
jdbc.maxTotal= 20
jdbc.maxIdle= 20
jdbc.minIdle=20
jdbc.maxWait= 30000
jdbc.autoReconnect=5
jdbc.initialSize=10
jdbc.defaultAutoCommit=false
jdbc.validationQuery=select 1
jdbc.testOnBorrow=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=150
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=120000
jdbc.minEvictableIdleTimeMillis=180000
jdbc.numTestsPerEvictionRun=5
jdbc.setMaxWaitMillis=30000
jdbc.removeAbandonedOnBorrow=true
jdbc.removeAbandonedOnMaintenance=true
Irrespective of the configuration type, we were expecting the number of connections to stay within the configured limits.
Is there any specific parameters that needs to be configured for getting this to work as expected in the always-on configuration scenario?
Any pointers will help. Thank you.
