One of the possible scenarios for SQL Server FCI to AG migration is adding a third node to the Windows Failover Cluster and creating an Availability Groups between the FCI and the third standalone SQL Server, or having one node as FCI and the other node as a standalone SQL.

In my case I am running with the second option. I have one node with SQL Server 2014 FCI and the other node SQL Server 2016 Standalone, but the servers are in the Windows Cluster. When you try to create the Availability Groups between them you get the following error.

  Failed to create, join or add replica to availability group ‘AvailabilityGroupName’, because node ‘Server02’ is a possible owner for both replica ‘Server02’ and ‘SQL2014VirtualName’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again.

If you want to see, this is the whole error message. Click in the image to see it better.

This error happens because the SQL2014VirtualName has as possible owners not only the physical Server Server01, but also the Server02 can be the possible owner, even if the there is not SQL 2014 Failover Cluster Node installed on it. So how can you resolve it?

1) In the Windows Failover Cluster Manager and click in the role name.

2) Right click in the SQL Server Resource and go to properties.

One of the selected itens will be the server that you are trying to setup and the secondary in the AG, and that’s why it is failing.

3) Just unselect the secondary server, save it and try to create the AG again.

You will see that you can create the AG successfully.

And that’s it for today! I hope you enjoy it..

Marcos Freccia
Data Platform MVP