By: Manvendra Singh | Comments | Related: > 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.
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.
Next Steps
- Read more tips about AOAG configuration.
- Explore more knowledge on SQL Server Database Administration Tips.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
