1

Virtual Enviroment description:

  • Hypervisor: VMWare

  • WSFC composed by two nodes with Windows server 2016 Standard

  • SQL Server AlwaysOn with synchronous replica and automatic failover

  • SQL Server 2014 (SP3-CU2) 12.0.6214.1

  • Following the cluster thresholds:
    CrossSubnetDelay : 4000
    CrossSubnetThreshold : 40
    PlumbAllCrossSubnetRoutes : 0
    SameSubnetDelay : 2000
    SameSubnetThreshold : 20

  • AG properties:
    LeaseTimeout: 20000
    FailureConditionLevel: 3
    HealthCheckTimeout: 30000
    VerboseLogging: 0

Issue:

RHS.exe process on primary node lost heartbeat with availability group and it initiated the failover. Immediately after the lost of heartbeat, primary node loses the connection with the secondary node and automatic failover fail. Shortly after, hearbeat with primary node works again and primary node takes over the resources again.

Log Details

In chronological order:

  1. Primary node randomly shows signs of disconnection with the secondary replica already in the days leading up to failover:
    ...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
  2. The primary node storage randomly shows signs of distress already in the days leading up to failover and just before failover:
    ...Long Sync IO: ... IOs in nonpreemptive mode longer than 1000ms
    FlushCache cleaned up 146070 bufs with 20273 writes in 80387ms...
    SQL Server has encountered 4 occourence(s) of I/O request taking longer than 15 seconds to complete...
  3. RHS.exe lost heartbeat with AG and WSFC send failover request to AG:
    [hadrag] Failure detected, diagnostics heartbeat is lost
    The local replica of availability group ... is preparing to transition to the resolving role..
  4. Shortly after the failover request, the primary replica loses connection with the secondary:
    ...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
  5. The listener stops working and there is a checkpoint failure on a specific database:
    One or more recovery units belonging to database .. failed to generate a checkpoint
  6. Eleven seconds after the attempted failover, heartbeat works again and primary replica takes over the resources

Questions

Million dollar questions:

  1. why heartbeat was lost?
  2. why connection with secondary replica was lost ?
  3. Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

I understand that it is difficult to answer but it can help to only have a strategy or hypotheses that can help me find the problem.

EDIT

I found an interesting example of extended event able to trace the duration of all synchronization steps on tigerteam github. The problem is that it can degrade performance and I haven't found a doc reference for analysing the values shows by extended events. Any idea or suggest for troubleshooting?

Improve this question
2
  • What about looking at either disk or network latency? I'm no Availability Groups expert, but based on your error messages and what I've read about how the heartbeat works in the past, I would suggest it might be worth starting there and verifying those two things are consistently performing as expected and show know issues.
    – J.D.
    Nov 25, 2020 at 12:49
  • 1
    It's a challenge! I am still exploring all the metrics available at operating system level but I haven't found nothing yet. I found a chance with extended events provided by tigerteam but the problem is they discourage active them continuously. You should active extended events only for troubleshooting but without a new lease timeout I think I won't find anything
    – Gio
    Nov 25, 2020 at 21:24

1 Answer 1

Reset to default
2

why heartbeat was lost? why connection with secondary replica was lost ?

Depending on which instance of DHS.exe it could be Network Problems or a failure in the local SQL Server instance.

Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

Double-check your cluster quorum so that the primary node does not need to communicate with any far-off resources to maintain cluster quorum.

You also have a storage problem that needs to be addressed, but if you use iSCSI or other network-based storage, that might have the same root cause.

Improve this answer
3
  • 1
    Why are you sure it's a network problem? RHS.exe is a process on the primary node that "pings" SQL Server AG, isn't it? leasemechanism Could it be a resource related issue since sql server did not respond to ping for 40 seconds?
    – Gio
    Nov 25, 2020 at 17:09
  • Good catch RHS.exe also hosts the core cluster heartbeat detection, but it could be either. Updated answer.
    – David Browne - Microsoft
    Nov 25, 2020 at 17:42
  • So, It can be a resource related issue or It can be a network issue. How to detect which of two is the root clause? I need to justify to the customer an increase of resource, so I need to be sure of problem. I can try to search if an extended event trace can help me, but I think it can degrade the performance and I need to wait next problem to know more.
    – Gio
    Nov 25, 2020 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.