We've got two Windows Server 2016 with SQLServer 2016 on AWS EC2 instances (node 1 and node 2) Because we're using a standard edition, we're limited to having one database per availability group. Therefore, we ended up having 20+ availability groups (we call it "slave" groups) to contain all of our databases and one availability group with a dummy database and a listener attached (we call it "master" group).
We followed pretty much what this article suggests:
Our plan is that, when failover occurs, only ag_master does automatic failover to node 2. Then, in the node 2 we run a script to manually failover the rest of slave groups to PRIMARY (we scheduled the script as a SQL agent job to run every minute on the instance to ensure the slaves' state are the same as master's)
For this setup, I would like to set availability groups such that: The "slave" groups do not failover at all because it is always the scheduled script that does a manual failover based on its master's state. For example, right now we see a lot of "lease expiry" and seeing it trying to failover/stopping. Is it possible to completely turn off the automatic failover for selected groups?