Recently, while deploying a hybrid AlwaysOn availability group for a client, I faced this error. Since it was something I was not able to find many hits on internet search, I thought of sharing this via this blog. I am sure it would help others.
Topology
The client was trying to deploy hybrid cluster with both on-premise instances and instances hosted in Microsoft’s Azure cloud. All machines were domain-joined and it they were part of multi-subnet network connected via Express-route.
Error message
Here was the error message when they were trying to add a replica in Azure VM.
Msg 19456, Level 16, State 1, Line 3
None of the IP addresses configured for the availability group listener can be hosted by the server ‘AZURESQL-1’. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.
Msg 41158, Level 16, State 3, Line 3
Failed to join local availability replica to availability, group ‘HR_AG’. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
Workaround / Solution
I looked at the IP addresses under the network name in the “Failover Cluster Manager” and found that there were two IPs: one in 10.150.xx.xx and one 10.160.xx.xx range. We saw that the replica that we were attempting to add was in 10.140.xx.xx. So, we then added an IP address in the appropriate subnet as a dependency of the network name.
In short, this error can be resolved by adding a right IP to the listener. The IP address for all subnets cannot be an IP address already in use, i.e. The IP address of one of the nodes. After adding above, we again attempted to join the replica. And as expected, the operation succeeded.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
Hi,
I appreciate the effort you put into this article, however, without any specific example code, it might be hard for someone to know which “appropriate subnet” needs an IP allocated. Was it the .160, the .150, or the .140? What was “[a] right IP for the listener?” With multi-subnet clusters becoming more popular particularly those stretched into AWS or Azure its easy to get confused.
Some T-SQL code would have made this a little clearer.
Thanks again,