How to handle SQL server DB failover
The DB team has built redundancy for the database with an AlwaysOn cluster. In the event of a failover, the database will turn the mirror and receive traffic from to the other node. This means that clients must be able to reconnect if a connection is interrupted. The actual transaction at that time will be canceled but all new transactions can be completed. In this case, it is not done, but we have to restart the servers / services on the application servers for them to reconnect.Due to this ,the application cannot be a 24/7 service as it cannot be obtained redundantly..
As per Vendor documentation,Beginning with Microsoft JDBC Driver 6.0 for SQL Server, it is no longer required to set multiSubnetFailover to "true" when connecting to an Availability Group Listener. A new property, transparentNetworkIPResolution, which is enabled by default, provides the detection of and connection to the (currently) active server.
Application is using Microsoft JDBC Driver 6.4 for SQL Server, In that case client server should able to reconnect to DB automatically..
The DB connection pool details are defined as below in context.xml of application server..
The DB team has built redundancy for the database with an AlwaysOn cluster. In the event of a failover, the database will turn the mirror and receive traffic from to the other node. This means that clients must be able to reconnect if a connection is interrupted. The actual transaction at that time will be canceled but all new transactions can be completed. In this case, it is not done, but we have to restart the servers / services on the application servers for them to reconnect.Due to this ,the application cannot be a 24/7 service as it cannot be obtained redundantly..
As per Vendor documentation,Beginning with Microsoft JDBC Driver 6.0 for SQL Server, it is no longer required to set multiSubnetFailover to "true" when connecting to an Availability Group Listener. A new property, transparentNetworkIPResolution, which is enabled by default, provides the detection of and connection to the (currently) active server.
Application is using Microsoft JDBC Driver 6.4 for SQL Server, In that case client server should able to reconnect to DB automatically..
The DB connection pool details are defined as below in context.xml of application server..
<Resource maxWaitMillis="10000" maxIdle="30" maxTotal="100" password="**********" username="pegaTest" url="jdbc:sqlserver://serveraddress; databaseName=Test; selectMethod=cursor; sendStringParametersAsUnicode=false; encrypt=true" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" auth="Container" name="jdbc/PegaRULES"/>
DB Information:-
DBProductName | Microsoft SQL Server |
DBProductVersion | 13.00.5820 |
DBDriverName | Microsoft JDBC Driver 6.4 for SQL Server |
DBDriverVersion | Proprietary information hidden |