AlwaysOn errors 19456 and 41158 for SQL Server

AlwaysOn errors 19456 and 41158 for SQL Server

Recently in the company to do remote data storage disaster, using AlwaysOn remote nodes for data synchronization, in the process of building encountered some problems

Software version

SQL Server2014

Windows2012r2

Network environment

Two room, one computer room is a network segment, another computer room is another network segment, that is, two subnets, two network segments, but in the same domain, and the use of VPN

AlwaysOn is a multi-subnet support, in the cross-room, multi-subnet environment there are several places to pay attention to

1, in the construction process need to remove the voting rights of the remote node, this in the construction process did not encounter problems
NodeWeight
Log on to the computer as a domain user on any cluster node, run PowerShell as an administrator, and execute the following command, JXxx08 the computer name of the geo-node, and set the voting rights of the JXxx08 node to 0
The command is as follows

import-="JXxx08"(Get0= (get-= Get-clusternode-| Format-table-property NodeName, State, NodeWeight

2, because the availability group set up the listener, then in the failover will encounter the listener IP can not overwrite another subnet problem, resulting in an error

Unable to join instance xxx to availability Group XXX

The server "XXXXXX" cannot host any IP addresses configured for the Availability Group listener. Either configure a public cluster network that can host one of the specified IP addresses, or add another listener IP address that can be hosted on the public cluster network on this server.
The local availability replica could not be joined to the availability group "xxxxxx". The operation encountered SQL Server error 19456 and has been rolled back. For more information, see the SQL Server error log. After the error source has been corrected, retry the ALTER availability GROUP JOIN command. (Microsoft SQL Server, error: 19456)

The solution to this problem is to add the VIP to another network segment in the listener.

So the listener now has two network segments of the VIP, the remote room of the listener IP after the addition will be automatically offline, regardless of it

3, because is the newly added geo-node, added to the existing availability group, you may encounter the following error

Unable to join instance xxx to availability Group XXX

Unable to join availability replica to availability Group XX, the operation encountered SQL Server error 41106 and has been rolled back.
For more information, see the SQL Server error log. After the error source has been corrected, retry the ALTER availability GROUP JOIN command. (Microsoft SQL Server, error: 41158)

This problem is not related to the multi-subnet, and it's in the way.

The solution is to create a new AG, and add the geo-node to this AG, you can solve

Because an offsite node is a newly created node, the AlwaysOn mirroring endpoint has not been set to cause the error above to be reported

I hope this article will help you

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

The copyright of this article is owned by the author and cannot be reproduced without the author's consent.

AlwaysOn errors 19456 and 41158 for SQL Server