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

Soysa, Kris 0 Reputation points
2024-03-08T16:31:28.7933333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,785 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.
996 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ben Miller-(DBADuck) 110 Reputation points MVP
    2024-03-11T01:09:22.9133333+00:00
    0 comments
  2. LiHongMSFT-4306 27,016 Reputation points
    2024-03-11T02:19:15.4833333+00:00

    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.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.