Troubleshooting The Publisher failed to allocate a new set of identity ranges for the subscription error 21197

The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData\unc\WIN2K12-1_ADVENTUREWORKS2014_MERGE_ADVENTUREWORKS2014_MERGE\20151029073334\Name_73.sch' due to OS error 3.

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.

Error:-

While creating an  Availability Group Listener I received this error
Msg 41009, Level 16, State 7, Line 1
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.
The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.

Database mirroring connection error 2 'DNS lookup failed with error: '11004(The requested name is valid, but no data of the requested type was found.)'.' Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c

Database Mirroring was in Disconnected state and when checked Errorlog file found below entry in Principal and Mirror Server

How do i change,Update SQL Server Collation for SQL Server failover cluster Instance.

How I do change/Update SQL Server Collation for SQL Server failover cluster Instance?

Failed to open file C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\devenv.exe.config.tmp

Issue:-

SQL Server 2008 R2 setup failed while update from SP1 to CU5

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... 

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Availability replica is in disconnected state