Why is SQL AO-AG slower to failover than SQL Mirroring

Question

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

Answer 1

There are more moving parts in an AG than in mirroring. There is a cluster involved and health checks have a timeout of 30 seconds. Look at the documents below to get and idea of what you are battling.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver16&tabs=new-limits

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

Answer 2

Hi @Soysa, Kris

This feature (Database Mirroring) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead.

Refer to this doc: Database Mirroring (SQL Server)

Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

AOA group will give you a feature which you cannot get it on mirroring. Using AoA group, is like using a combination both clustering and mirroring.

Also, here are some tips on Speeding Up Failover.

Best regards,

Cosmog Hong

---

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.