My two synchronous-commit SQL AO replicas are set for manual failover mode but are failing over automatically. When I look through the logs and extended events, it looks just like what would happen if it were set automatically. This was set before a time when I was out of this office and the servers have been rebooted multiple times for updates, so I know the service account isn't waiting for a restart. Manual failover works but unsure why automatic is happening. There were 4 nodes, but the 2 remote DR nodes are currently removed both from SQL AOAG and WSFC. Any advice on where to look to figure this out?
Sql-server – AlwaysOn Replicas set to manual but failing automatically
availability-groupsfailoverhigh-availabilitysql-server-2012
Related Question
- Sql-server – Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name
- SQL Server – Always On DDL Operations Explained
- SQL Server 2016 – AG Group Failover and Quorum Explained
- Sql-server – Data Loss when forced failed over to secondary replica on Sql Server 2014
- SQL Server 2016 – Always On Node-2 Stuck in Resolving State
- Sql-server – SQL Server AlwaysOn: Lost of heartbeat and connection with secondary replica
- SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover?
Best Answer
A reboot of a cluster node that has a resource group that's configured to automatically start forces the WSFC to start that resource on a surviving node, implementing HA feature of SQL Server AAG. This action also triggers SQL Server instance to promote an existing synchronous secondary replica to primary. I think you can avoid automatic failover by setting only one replica to be synchronous, however this will effectively make you lose data should you irreversibly lose the cluster node with the primary replica.