Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fixing Error 19405 When Configuring SQL Server Availability Groups


By:   |   Read Comments   |   Related Tips: More > Availability Groups

FREE Webcast > 5 Easy SQL Server Query Performance Boosters


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.

microsoft

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.

failover cluster manager

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.

cluster manager

Step 2:

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

name properties

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.

name properties

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.

properties

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.

failover cluster manager

Step 6:

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

new availability group
Next Steps


Last Update:



next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





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    Notify for updates 


Send me SQL tips:

    



Learn more about SQL Server tools