About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

Adding Replica of Multisubnet gives error operation encountered SQL Server error 19456 and has been rolled back.


Issue:-

None of the IP addresses configured for the availability group listener can be hosted by the server 'Node3SQL03'. 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.
Failed to join local availability replica to availability group 'SQL0040DAG01'.  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. (Microsoft SQL Server, Error: 19456)

Failed to join local availability replica to availability group 'SQL0040DAG01'.  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.

Disconnecting connection from Node3SQL03...

Issue:-
Servers in Multi-subnet clustering with AlwaysOn Configured in Multi-subnet Envirnment
DataCenter-1
IP
DataCenter-2
MgmtIP
Node1SQL01
192.168.1.10
Node3SQL03
192.168.2.12
Node2SQL02
192.168.1.11
 Node4SQL04
192.168.2.13

and existing Availability group is -- SQL0040DAG01

The requirement is to add 3rd replica{Node3SQL03} in existing AG which belongs to a different data center and hence in the different network.


I encountered below error, while adding Availability Replica in AG in Multi-subnet environment:



None of the IP addresses configured for the availability group listener can be hosted by the server 'Node3SQL03'. 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.

Failed to join local availability replica to availability group 'SQL0040DAG01'. 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. (Microsoft SQL Server, Error: 19456)



Failed to join local availability replica to availability group 'SQL0040DAG01'. 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.

Disconnecting connection from Node3SQL03...

And



Connecting to Node3SQL03...

Msg 41158, Level 16, State 3, Line 2

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 41106 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.

Disconnecting connection from Node3SQL03...

Reason of this issue:-


For this AG, which is  implemented in multi-subnet cluster, existing listener was configured only with  one of the subnet IPaddress [192.168.1.*], which belongs to datacenter 1.

 This error message popped up while configuring the replicas in another subnet, and the reason for error message was that the listener was configured with only one subnet IP address.
Hence there is a need to add IP address for another subnet in this existing listener.

Resolution:-

So once we will configure listener with  IPaddress of second subnet, as shown in the figure -1, we will be able to successfully add replica of the secondary subnet’s [192.168.2.*].
To do this, I clicked on existing listener SQL0040DAG01 –> Property --> and added an IP address of 192.168.2.14(Datacenter2)



Then Click OK.

Now try to add 3rd replica on existing AG, the issue will get resolve.

Hope this will help to someone who has this issue.

Thanks for reading... 



Labels: , , , ,