Replication Agents fail to connect to listener in a multisite cluster
Published Jan 15 2019 04:35 PM 659 Views
Microsoft
First published on MSDN on Apr 27, 2015

A listener (network name) for a multisite cluster will be dependent on more than 1 ip address.  When connecting to a listener for a multisite cluster, it is recommended to add the parameter multisubnetfailover to the connection string for the application.

Legacy applications may not be able to use the latest version of Microsoft SQL Server Native Client (SNAC 11 or Higher), or the connection strings are not exposed to be able to use the parameter.  The replication agents (distrib.exe, logread.exe, snapshot.exe, and replmerg.exe) would all be considered legacy applications, because they do not implement the multisubnetfailover parameter.  There are two recommended solutions for using multisubnet listener with replication.

1) Increase the login timeout

If the replication agents are the only legacy application connecting to the listener, then using option 1 of increasing the login timeout value is the recommended solution. This can be done, by creating a new profile for the agent and changing the login timeout value from the default of 15 to 60.

The agent will need to be changed to use the new profile, and then stopped and started for the change to take effect.

For more information on updating the timeout parameter of the replication agent, see the following link on working with replication agent profiles.

https://technet.microsoft.com/en-us/library/ms152515(v=sql.110).aspx

This option is the recommend since most agents connect once and run continuously, so the delay would be experience in the initial connection.  If the agent is set to run in an interval, then increasing the login timeout may cause a longer latency between the publisher and subscriber.

2) Change the RegisterAllProvidersIP for the listener from 1 to 0

https://msdn.microsoft.com/en-us/library/hh213080.aspx (RegisterAllProvidersIP Setting)

Version history
Last update:
‎Jan 15 2019 04:35 PM
Updated by: