Sql-server – SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover

availability-groupsfailoverhigh-availabilitysql server

It is my understanding that for automatic failover to occur in an Always On AG environment, the replicas just need to be configured with "automatic failover," "synchronous commit" and the environment must have proper quorum (i.e. odd number of voting members).

However, according to the "SQL Server 2019 Always On" textbook by Peter A. Carter, for automatic failover to work, the secondary cannot be active and must be passive. Passive meaning the Readable Secondary setting is set to 'No' so it is unavailable for direct connections or read access, and obviously no backups can be done on them either.

Here is the exact statement from the textbook:

"Although we can change the value of Readable Secondary through the
GUI while at the same time configuring a replica for automatic
failover without error, this is simply a quirk of the wizard. In fact,
the replica is not accessible since active secondaries are not
supported when configured for automatic failover."

Source: Pro SQL Server 2019 Administration: A Guide for the Modern DBA

Here's a screenshot of the "quirk"

enter image description here

Note: According to the textbox, the Readonly Secondary should be set to 'No' if you want automatic failover to work. Thinking back, I don't recall a successful automatic failover occurring with this set up and I believe when an issue occurred, the AG was in a RESOLVING state. Note, we have third server for proper quorum.

I've searched through Microsoft's documentation to confirm whether or not the author's statement is true but have yet to find anything that explicitly states that the secondary replica must be passive for automatic failover to work. This Microsoft article (Failover and Failover Modes (Always On Availability Groups)) states the conditions required for automatic failover but does not mention needing a passive secondary replica.

So is the author correct and Microsoft has failed to advertise this OR is the author incorrect and automatic failover does work when the secondary replica is active/readable? My objective is to have a highly available environment with automatic failover but do we need to sacrifice offloading read operations in order to accomplish this? Adding another server is not an option.

Best Answer

You can fail over to a readable secondary. The author is either wrong, or just using some confusing terminology. Reading the exact quote you included, I'd lean towards the former explanation. But I don't know all the context around that statement.

Here I have an AG - all replicas are configured to be readable when they are in the secondary role, and NODE2 is set up for automatic failover:

Screenshot of AG config in SSMS as described above

Here's the current state of things on the AG dashboard:

Screenshot of AG dashboard in SSMS showing all replicas healthy

I'll go ahead and connect directly to NODE2 (sync secondary) and start some queries running:

SELECT COUNT_BIG(*)
FROM dbo.A;

WAITFOR DELAY '00:00:01';

GO 100

And then do something to cause an automatic failover to NODE2. In this case, I will kill the SQL Server service on the current primary (NODE1).

Now I'll check the AG dashboard, and can see that NODE2 automatically became the new primary.

Screenshot of AG dashboard showing NODE2 as the new primary

Note that during the failover, my SSMS window running queries on NODE2 got this message:

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Which is normal (clients being disconnected while the database undergoes recovery as part of becoming the primary). I retried the query and it continued running. I wouldn't have to do that if I were connecting to the listener via read-only intent.

Related Question