1

Encountered problem: Our use-case has 3 x Server 2022 in a topology of a domain controller with two node cluster. This is a SQL Always On AG architecture. The cluster hosts the SQL availability group roles. This is a replacement for the MS deprecated SQL Mirroring architecture.

While the older Mirror setup had a hard (physical power or network cable out) failover time of 10 seconds or less failing over to the database, the newer Always On (AO-AG) system takes 21 seconds.

solutions tried: Raw database only failover (with simple, threaded client app) takes 22 seconds with all configurations set to optimal as mentioned briefly below:

Same client application when run in the Mirror architecture setup on same hardware and databases show a less than 10 second failover to new principal DB

  • All cluster Role properties and Role Resource properties are set to minimize latency. (image)
  • Network is a lab based dedicated setup with no other resources utilizing it.
  • RegisterAllProvidersIP and HostRecordTTL has been changed respectively from 1 and 1200 to 0 and 10.
  • SQL listener and IP resource roles in use - which is connected to clients before and after failover.
  • Manual failover occurs within 5 seconds.
  • Auto failover (pulling net or power cable) takes around 22 seconds for HA AO-AG setup. SQL Mirror setup for same takes around 10 seconds.
  • MultiSubnetFailover=True and False also has been tried.

Did Microsoft make SQL AO-AG slower than the legacy SQL Mirror?

How do I speed up the failover time?

thanks

(https://i.sstatic.net/9YO7D.png) Role Resource properties - these have been changed in many permutations

Improve this question

1 Answer 1

Reset to default
0

Did Microsoft make SQL AO-AG slower than the legacy SQL Mirror?

You have specified yourself, "Manual failover occurs within 10 seconds." so I guess you answered your own question.

How do I speed up the failover time?

There are many components to failover, I don't see any settings in the OP which have been specifically tuned for failure detection, which is what I'm assuming you want given it seems you want to "speed up" failure scenarios in the case of a "network cable pull" or "power cable pull". Those are settings governed by Windows Clustering and not SQL Server. You'll need to flesh out good settings for your same/cross subnet configurations as that'll govern how/when node level health detections will occur.

There's no mention of database startup times or time spent on recovery phases, it's uncertain how much time this takes, however a good swag would be the previously OP quoted 10 seconds, which is decently quick but may have room for improvement.

Improve this answer
5
  • Thanks Sean, I updated question, after you had answered. New finding show that with a client app running against AO-AG and then against Mirrored system on the same hardware / database, the Mirror system wins the speed race. I will try some of your suggestions and update. As for my answering my own question, I actually wanted to know if MS built AO-AG slower or if my configuration of new setup is the culprit - thanks
    – AllYourBaseAreBelongToUs
    Commented Mar 7 at 18:44
  • Also, I understand that in Mirror the connection is automatically sent to current principal by the NET framework at .open() with a bit of complicated action based on the "failover partner" clause in the conn string. While in AO-AG this is handled by the virtual IP - Listener.
    – AllYourBaseAreBelongToUs
    Commented Mar 7 at 18:50
  • You're ultimately comparing apples to oranges and have two different setups, they operate two different ways (mirroring and AGs) and rely on two different mechanisms. Mirroring was self-contained, AGs (for HA) are not. You also need to specify what you're trying to solve, you're saying overall failover time but then only point to time to detection, but now in the comments are talking about client connectivity. It's hard to understand the moving target you've presented as to what you're trying to solve.
    – Sean Gallardy
    Commented Mar 7 at 19:07
  • to clarify further; client app uses the database on 1)Mirror setup 2)AG setup. When connected to DB and failing over, the Mirror setup recovers and client gets use of new principal within 10 seconds. However, in the newer tech AG setup client has to wait 21 to 25 seconds for the new principal (primary in HA nomenclature) to be available. Since Mirror was deprecated by MS and HA is the replacement, is this performance the new normal? Or is my cluster or AG configuration to blame
    – AllYourBaseAreBelongToUs
    Commented Mar 8 at 16:16
  • Your configuration is to blame as I pointed out in my answer.
    – Sean Gallardy
    Commented Mar 8 at 16:42

Your Answer

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

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