-> Application was failing with below error,
TITLE: Connect to Database Engine
Cannot connect to JBSAG1.
ADDITIONAL INFORMATION:
Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. (Microsoft SQL Server, Error: 983)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-983-database-engine-error
Microsoft ODBC Driver 17 for SQL Server : Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
-> I checked the database and Always ON availability group status. Database was “Not Synchronizing” and Availability group was resolving,
SQL Server Errorlog
JBSAG1
2022-06-13 06:42:24.070 spid58 Always On: The local replica of availability group ‘JBAG12’ is preparing to transition to the resolving role. This is an informational message only. No user action is required.
2022-06-13 06:42:24.070 Server The availability group ‘JBAG12’ is being asked to stop the lease renewal because the availability group is going offline. This is an informational message only. No user action is required.
2022-06-13 06:42:24.070 spid58 The state of the local availability replica in availability group ‘JBAG12’ has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The state changed because the availability group is going offline. The replica is going offline because the associated availability group has been deleted, or the user has taken the associated availability group offline in Windows Server Failover Clustering (WSFC) management console, or the availability group is failing over to another SQL Server instance. For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-06-13 06:42:24.070 spid78s Always On Availability Groups connection with secondary database terminated for primary database ‘JBSAG12’ on the availability replica ‘JBSAG2’ with Replica ID: {0e5b3832-3dda-41cb-9730-0f119639b535}. This is an informational message only. No user action is required.
2022-06-13 06:42:24.080 spid42s The availability group database “JBSAG12” is changing roles from “PRIMARY” to “RESOLVING” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2022-06-13 06:42:24.090 spid42s State information for database ‘JBSAG12’ – Hardened Lsn: ‘(37:1184:1)’ Commit LSN: ‘(37:1144:5)’ Commit Time: ‘Jun 12 2022 4:48AM’
2022-06-13 06:42:24.090 spid42s Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [JBSAG12] has been released. This is an informational message only. No user action is required.
2022-06-13 06:44:55.330 Logon Error: 983, Severity: 14, State: 1.
2022-06-13 06:44:55.330 Logon Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
2022-06-13 06:55:06.730 Logon Error: 983, Severity: 14, State: 1.
2022-06-13 06:55:06.730 Logon Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
JBSAG2
2022-06-13 06:36:11.050 spid25s The recovery LSN (37:1176:1) was identified for the database with ID 6. This is an informational message only. No user action is required.
2022-06-13 06:42:23.960 spid29s Always On: The local replica of availability group ‘JBAG12’ is going offline because the corresponding resource in the Windows Server Failover Clustering (WSFC) cluster is no longer online. This is an informational message only. No user action is required.
2022-06-13 06:42:23.960 spid29s The state of the local availability replica in availability group ‘JBAG12’ has changed from ‘SECONDARY_NORMAL’ to ‘RESOLVING_NORMAL’. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-06-13 06:42:23.960 spid41s Always On Availability Groups connection with primary database terminated for secondary database ‘JBSAG12’ on the availability replica ‘JBSAG1’ with Replica ID: {cf81c5d4-0dbf-4573-ab9d-018423fb92b2}. This is an informational message only. No user action is required.
2022-06-13 06:42:23.960 spid47s The availability group database “JBSAG12” is changing roles from “SECONDARY” to “RESOLVING” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2022-06-13 06:42:23.960 spid47s State information for database ‘JBSAG12’ – Hardened Lsn: ‘(37:1184:1)’ Commit LSN: ‘(37:1144:5)’ Commit Time: ‘Jun 12 2022 4:48AM’
-> In my case, there was a network issue wherein the nodes were removed from cluster due to quorum loss and then came back online automatically. But the Always On Cluster role went down as shown in below screenshot,
-> I was sure that Cluster was online and also that the nodes are all available. I right clicked on Role “JBAG12” and clicked on “Start Role”. it came online without issues,
-> Availability group came online and everything was working fine,
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.