Fixing Error 19405 When Configuring SQL Server Availability Groups

Problem

I have written many articles on AlwaysOn Availability Groups (AOAG) and you can
take a look at them
here. Today, I will show you how to fix AOAG error 19405 that occurs when configuring
an Availability Group (AG) on two failover cluster instances.

Here is what the error looks like.

Failed to create, join or add replica to availability group 'DBAG_***',
because node 'U*******2' is a possible owner for both replica 'U*******DR'
and 'U********0'. If one replica is failover cluster instance, remove
the overlapped node from its possible owners and try again. (Microsoft SQL Server,
Error: 19405)

Solution

I had a requirement to configure Availability Groups between two failover cluster
instances. I received the below error while configuring the AG between the FCIs.

The error suggests that the Availability Group failed to create because the primary
node is a possible owner for both replicas. To fix this issue we need to remove
the overlapped node from its possible owners. I decided to check the Failover Cluster
Manager to have a look at the possible owners for its resources.

Environment Details

Before going ahead, let me provide some details about the AOAG configuration and
database environment. We have 2 nodes A and B in datacenter 1 and another set of
nodes C and D in datacenter 2. A Windows cluster is configured between all four
nodes. We have two sets of storage; one set of storage is shared between node A
and node B in DC1 and another set of storage is shared between node C and node D
in DC2. We installed a SQL Server failover cluster instance (MSSQLSERVER) between
node A and node B on their respective shared drives. Similarly, we installed another
FCI (MSSQLSERVERDR) in DC2 between node C and node D. Finally, we are configuring
AOAG between these two FCIs instances between DC1 and DC2 and we received the above error.

If you click on disks from the left side to check the shared storage between
the respective nodes, you will get the below screen and here you can see there are
two sets of storage configured between the respective nodes for the respective SQL
Server instances. Remember, both SQL Server instances and shared storage are part
of single Windows Server Failover Cluster.

Fixing Error 19405

Follow the below steps to fix this issue.

Step 1:

In Failover Cluster Manager, check the possible owner details as suggested in
error details. You can see there are two SQL Server roles running with their respective
owner nodes. Right click on the server node and click Properties.

Step 2:

Once you click on Properties, you will get the below property window.

Step 3:

You can see there are four tabs in the above window. Click on the “Advanced
Policies” tab. The below screen will appear under Advance Policies tab. You
can see all four nodes are checked, whereas only the nodes should be selected that
are part of the SQL Server failover cluster instance.

As we have installed SQL Server failover cluster instance MSSQLSERVER on node
A and node B and another MSSQLSERVERDR on node C and node D. So, when you select
any SQL Server FCI in FCM then possible owners should only be set to those nodes
on which SQL Server is installed.

Step 4:

To fix the issue, we need to deselect node C and node D from SQL Server instance
MSSQLSERVER that was installed for node A and node B as shown in the below screenshot.

Step 5:

As you can see in Failover Cluster Manager I have selected SQL Server instance
MSSQLSERVER and made changes for node A and node B that is installed in DC1.

Similarly, we need to do the same change for MSSQLSERVERDR. Open the properties
window and deselect node A and node B from Advanced Policies tab because for MSSQLSERVERDR
it should only be node C and node D in DC2.

Step 6:

Now configure the Availability Group again between these two failover cluster
instances and this time it will configure successfully.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.