Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Log Reader Agent not starting after AlwaysON Availability Groups Failover
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-05-12








During recent testing of a new Microsoft Azure Cloud setup of SQL Server 2016 Standard running Always On Availability Groups Basic, with Transactional Replication configured, we ran into an issue with the Log Reader Agent not starting after a Always On Availability Groups initiated fail-over. This setup required a Remote Replication Distributor and that is where the Log Reader Agent was running. The error was initially seen within Replication Monitor and then a subsequent check of the Log Reader Agent SQL Job History on the Remote Distributor showed logging of these errors:


Status: 2, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'SRV'.'.
The process could not execute 'sp_replcmds' on 'SRV'.
Status: 2, code: 10054, text: 'TCP Provider: An existing connection was forcibly closed by the remote host.'.
Status: 2, code: 10054, text: 'Communication link failure'.


The issue was related to the redirect of the Original Publisher to the AG Listener Name not working properly after an Always ON Availability Groups fail-over. The workaround we implemented was that immediately following an AG(s) fail-over, we had to run the below T-SQL and ensure the value for the @original_publisher variable would need be the current Primary/Active Node hosting the AG(s). Immediately after running the below code the Log Reader Agent would be able to startup and replicated commands would go through to the Subscriber.


T-SQL:

At the Distributor, in the Distribution database, run the stored procedure code to associate the original publisher and the published database with the Availability Group Listener Name of the Availability Group.

USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'MyPublisher',
@publisher_db = 'MyPublishedDB',
@redirected_publisher = 'MyAGListenerName';