Have you ever encountered SQL Server Error 19456 while trying to add a replica in Azure VM? If so, you’re not alone. In this blog post, we will discuss this error and provide a solution to resolve it.

Error Message

The error message you might have encountered is as follows:

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.

Topology

Let’s first understand the topology in which this error occurred. The client was deploying a hybrid cluster with both on-premise instances and instances hosted in Microsoft’s Azure cloud. All machines were domain-joined and part of a multi-subnet network connected via Express-route.

Solution

After analyzing the error message and the network configuration, we found that the IP addresses under the network name in the “Failover Cluster Manager” were in two different subnets: 10.150.xx.xx and 10.160.xx.xx. However, the replica we were trying to add was in the 10.140.xx.xx subnet.

To resolve this error, we added an IP address in the appropriate subnet as a dependency of the network name. In other words, we added a new IP address that could be hosted on a public cluster network for the server.

After adding the correct IP address, we retried the operation to join the replica, and it succeeded as expected.

Conclusion

If you encounter SQL Server Error 19456 while adding a replica in Azure VM or in a similar hybrid cluster scenario, make sure to check the IP addresses configured for the availability group listener. Ensure that the IP address for all subnets is not already in use by one of the nodes. By adding the correct IP address as a dependency of the network name, you can resolve this error and successfully join the replica to the availability group.

We hope this blog post has been helpful in resolving SQL Server Error 19456. If you have any questions or need further assistance, feel free to leave a comment below.