I have set up an availability group where a primary is standalone server and replica is SQL failover cluster. This setup allows me only manual failover and when ever I try to set it automatic in FCI instance it says, this is not allowed on FCI instance.
Can anybody tell me the exact reason why it is not allowed or if its allowed please tell me the steps.
Automatic failover is not allowed if availability replica is hosted on SQL Server FCI. Quoting MS Arvind Shyamsundar
This is simply because the SQL Server team did not wanted to add more complexity to failovers scenarios. You already have automatic failover provided in SQL Server FCI and introducing automatic failover with availability group in FCI would make it too complex and may result in unwanted situations.
If you go ahead and try to configure automatic failover you would get below error message. Copied from this blog
Msg 35215, Level 16, State 17, Line 1 The Alter operation is not allowed on availability replica 'INST1', because automatic failover mode is an invalid configuration on a SQL Server Failover Cluster Instance. Retry the operation by specifying manual failover mode.
In the same blog you can see Arvind Shyamsundar writing
This is expected and by-design, the logic being that the FCI already has automatic failover configured between the nodes. The current implementation of AG doe not allow a failover outside of the FCI nodes.
