0

I'm configuring a SQL Server Availability Group with three replicas: node1\inst, node2\inst, and node3\inst. I have set node1\inst for automatic failover with synchronous commit, while node2\inst and node3\inst are configured for manual failover.

I have a couple of questions about the failover behavior:

  1. When node1\inst is the primary replica and encounters an issue, will it automatically failover to node2 or node3 even if they are set for manual failover? Additionally, does automatic failover only occur between automatic-failover-and-synchronous-commit replicas?

  2. If node2 becomes the primary replica and a problem occurs, does it still adhere to its manual failover setting, or will there be an automatic failover to node1?

I am trying to understand the intricacies of failover mechanics within SQL Server Availability Groups to ensure a reliable high availability setup. Insights into how automatic failover decisions are determined in these configurations would be extremely helpful.

Thanks in advance for your assistance!

1 Answer 1

2

When node1\inst is the primary replica and encounters an issue, will it automatically failover to node2 or node3 even if they are set for manual failover?

It will not, you'd need a minimum of 2 replicas to be set to synchronous commit and automatic failover in order to have an automatic failover.

Additionally, does automatic failover only occur between automatic-failover-and-synchronous-commit replicas?

Yes (same link above).

If node2 becomes the primary replica and a problem occurs, does it still adhere to its manual failover setting, or will there be an automatic failover to node1?

It will still not automatically failover because the minimum requirements have not been met (same link as #1).

Insights into how automatic failover decisions are determined in these configurations would be extremely helpful.

Here are the Docs on how automatic failover works. If you have further questions, please feel free to ask them in new questions.

2
  • Hi Sean, If automatic failover is not possible, does this mean that if a problem occurs on node1\inst, it will simply go down, and node2\inst and node3\inst will remain unusable at that time? Commented Jan 22 at 3:27
  • @Justalearner Yes and no. If node1\inst goes down then you'll be unable to query either of the two remaining instances (they will be in resolving), however as an administrator you could force a failover to another node with potential data loss. This will not be done automatically because there is a chance for data loss. Commented Jan 22 at 11:10

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.