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';
NOTE: If you have any Subscribers that are LOCAL to Always On Node 1 / Publisher, you will need to delete and re-add the Subcribers using the below method before those will work after a fail-over to Node 2 occurs.
EXEC sp_addsubscription @publication = N'PublicationName',
@subscriber = N'ListnerName',
@destination_db = N'SubscriberDB',
@subscription_type = N'Push',
@sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
GO
EXEC sp_addpushsubscription_agent @publication = N'PublicationName',
@subscriber = N'ListnerName',
@subscriber_db = N'SubscriberDB',
@job_login = null, @job_password = null, @subscriber_security_mode = 1;
GO
The key is replication support only. The only disadvantage is if any new article is added the subscriber table will not be automatically created, you will need to go and create it manually.
|
|
|
|
|
|