Always On role change but AG didn't fail over

Cooper, David A. [ISS] (Contractor) 71 Reputation points
2022-12-07T04:27:58.48+00:00

Occasionally, our monitoring system reports this message based on event 1480:

"The availability group database "xxxxxxxx" 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."

In this case, the message is pertaining to a secondary HA node in a three-node Always On cluster.

Neither myself nor my colleagues performed a manual failover.

So what can trigger role change alert when the cluster hasn't actually failed over? Does it reflect an issue with infrastructure?

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,815 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
962 questions
Windows Hardware Performance
Windows Hardware Performance
Windows: A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.Hardware Performance: Delivering / providing hardware or hardware systems or adjusting / adapting hardware or hardware systems.
1,551 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,446 Reputation points
    2022-12-07T06:05:45.153+00:00

    Hi @Cooper, David A. [ISS] (Contractor) ,

    Welcome to Microsoft Q&A!

    "The availability group database "xxxxxxxx" 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."

    It seems that there is an issue with the synchronization mode that is used in this Availability Group. The synchronization mode used can be checked from the Always on Availability Group properties page.
    From the properties page below, it is clear that the Failover mode in this Availability Group is configured. If you are configured in manual mode, the, you need to manually perform a failover operation before rebooting or shutting down the server:
    268034-image.png
    You can change the Failover Mode to Automatic, where an automatic failover operation will be performed in case of any unplanned shutdown or reboot:

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Seeya Xi-MSFT 16,446 Reputation points
    2022-12-08T06:09:28.68+00:00

    Hi @Cooper, David A. [ISS] (Contractor) ,

    It is also possible that there is something wrong with the Windows cluster.
    You need check Windows Server Event Viewer for some errors and open Failover Cluster Manager and check if there is any error status.
    If the cluster is unavailable, you need to go to the Windows Services console and right click start.
    268380-image.png
    Here is a useful article for you which contains all screeshoots: https://www.spotlightcloud.io/blog/troubleshooting-sql-server-always-on-availability-groups

    Best regards,
    Seeya

    0 comments
  3. Limitless Technology 43,966 Reputation points
    2022-12-08T08:22:41.023+00:00

    Hi,

    Thank you for posting your query.

    Kindly follow the steps provided below to resolve your issue.

    One thing to note here is that the Cluster action is only if any of the subsystems report an “ error ”, no action is taken on a warning.

    So effectively what happens is the following:

    Cluster service runs LooksAlive check

    Sp_server_diagnostics results sent to Resource Monitor DLL

    Resource Monitor DLL detects any error state and notifies the cluster service

    Cluster Service takes the resource offline

    Notifies SQL Server to issue an internal command to take the availability group offline.

    There is also the whole concept of a lease that is explained here

    Go to this link for your reference and other troubleshooting procedures https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sql-2012-alwayson-availability-groups-automatic-failover-a-look/ba-p/370428

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups

    -------------------------------------------------------------------------------------------------------------------------

    If the answer is helpful kindly click "Accept as Answer" and up vote it.