Question

3
Replies
62
Views
Close popover
Brahmeswara Rao (Brahmesh@)
Capgemini
Developer
Capgemini
SE
Brahmesh@ Member since 2013 148 posts
Capgemini
Posted: September 8, 2020
Last activity: October 20, 2020

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

<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:-

DBProductNameMicrosoft SQL Server
DBProductVersion13.00.5820
DBDriverNameMicrosoft JDBC Driver 6.4 for SQL Server
DBDriverVersion6.4.0.0

 

 

Pega Platform 7.3.1 System Administration Financial Services