Failure to Create SQL Server Always On Availability Group

Posted 01/13/2020 9:05 AM by Corey Klass

I am currently in the process of migrating my company's SQL Server replication strategy from Log Shipping to Always On Availability Groups. This involved the addition of several new VMs to my production SQL Cluster.

When I attempted to create the first SQL Server Availability Group, I received the following error message:

Msg 19405, Level 16, State 17, Line 3
Failed to create, join or add replica to availability group 'Primary', because node 'DB05' is a possible owner for both replica 'DB04\ReadOnly' and 'DB05\Primary'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again.

In the Failover Cluster Manager, I had specified that DB05 is not a possible owner of Primary. However, viewing possible owners in Powershell revealed that the owner was still incorrectly set.

First you need to list the cluster resources by executing get-clusterresource to get the name of your SQL Server service:

get-clusterresource

To view the cluster resource owners, you execute get-clusterownernode for the name of your SQL Server from above:

get-clusterownernode

To set the correct cluster resource owners, you execute set-clusterownernode

set-clusterownernode

You can then view the cluster resource owners again with get-clusterownernode to verify that they are set correctly:

get-clusterownernode

The Availability Group can now be created.