AlwaysON -automatic failover

Question

Hi We have SQl server 2019 CU20 servers with always on configuration. Setup synchronise between primary and secondary replica . Business wants to set it "automatic failover" at the event of failover

Env: two nodes cluster with witness share

I understand SQL server document says "automatic failover couldn't occur if the right secondary replicas weren'tin the proper state." .

Since "automatic fialover" handled by WSFC culster. not SQL server .

Q1. Could any one explain how that will happen if the wintness share is offline(assumed other two nodes up and running) .

Q2. if in that case secondary is behind to primary, what would be the outcome ?

(I understand manual failover with accepting data lost)

REQUIRED_COPIES_TO_COMMIT =1

thanks

Answer 1

Hi @Ashwan

Q1. Could any one explain how that will happen if the wintness share is offline(assumed other two nodes up and running) .

The cluster will not go down until a node cannot form quorum. When the file share witness is offline, the node that currently owns quorum will maintain ownership because it can still get votes.

Refer to Understanding Quorum in a Failover Cluster for more details.

Q2. if in that case secondary is behind to primary, what would be the outcome?

This has different sub options, such as being too far behind for a common point and needing to be rolled up to a common fork, or a common fork exists, the secondary can have the log stream begin at the common fork and continue until caught up.

Most of this is automatic, the only part that isn't is if there is no common fork for the primary and secondary and manual intervention will need to take place. This will result in an error stating the database is too far behind, which is self-explanatory.

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.

Answer 2

Since "automatic fialover" handled by WSFC culster. not SQL server . Q1. Could any one explain how that will happen if the wintness share is offline(assumed other two nodes up and running) .

A failover can occur if SQL Server is not healthy. But if node goes down, there cannot be any failover, since the cluster has lost quorom.

Q2. if in that case secondary is behind to primary, what would be the outcome ?

If you have synchronous commit, the secondary cannot really be behind. Or, well, what synchronous commit means is that once the log has been hardened on both primary and secondary it can commit. So the log entry still has to be applied on the secondary. And if that has not happened, the secondary could be said to be behind. In that case, it will not come online until it has completed recovery.