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?


Yes, the automatic or manual failover option is an option on each replica on each availability group. So for each of the slave availability groups, you'll set all replicas to manual failover.

| improve this answer | |
  • Thanks, actually I did that for all slaves but still seeing "lease expiry" and eventually I see this error message randomly popping up in the event log... From MSSQLSERVER: The lease between availability group 'ag_xxxxx' and the Windows Server Failover Cluster has expired and eventually from FailoverClustering: Cluster resource 'ag_xxx' of type 'SQL Server Availability Group' in clustered role 'ag_xxx' failed. – Yu Sasaki May 27 at 2:13
  • You'll need to provide a lot more info. When are the log messages occurring? Is there an actual problem, or you're just concerned about these log messages? If you have something set to manual failover and it fails over automatically, then that is a defect and you'll need to contact Microsoft to get it fixed. There is a difference between failover and a role failing. If failover is set to automatic, it should failover it the role fails. If it's set to manual, it should not failover automatically if the role fails. – Tony Hinkle May 27 at 13:25
  • Thanks Tony. I started thinking that the original description of this question is not quite accurate... I probably wanted to ask, "how to properly configure AG and Failover clusters so the error messages regarding lease expiry disappear", something along those lines. I will post another question. – Yu Sasaki May 27 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.