SQL Always On automatic failover incase of multiple secondary I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always choose node-2 to be made primary and stall node-3 for being primary.
Disable automatic failover for Node 3. This way it will always automatically failover between Nodes 1 &amp; 2 only. If you encounter a scenario where one of these nodes dies and you need Node 3 to become an automatic failover node, simply change the setting.