LogReader errors ‘Validating publisher’ after AlwaysOn failover.
Problem of the day: Transactional Replication was configured on a database in AlwaysOn Availability Group. When the group was failed over to synchronized secondary node Transactional Log Reader stopped working.
Initial LogReader errors when failover occurs. If configured correctly, LogReader will retry and reconnect to new Primary.
2018-02-21 00:08:31.165 Status: 16384, code: 20007, text: ‘No replicated transactions are available.’.
2018-02-21 00:09:16.210 Status: 4, code: 22021, text: ‘Batch committed. Batch consisted of 1 commands, 1 xacts. Last xact: 0x00000037000001e80001, ‘0x00000037000001e80004′.’.
2018-02-21 00:10:16.318 Status: 2, code: 20011, text: ‘The process could not execute ‘sp_replcmds’ on ‘SQLSERVER-0′.’.
2018-02-21 00:10:16.318 The process could not execute ‘sp_replcmds’ on ‘SQLSERVER-0’.
2018-02-21 00:10:16.318 Status: 2, code: 10054, text: ‘TCP Provider: An existing connection was forcibly closed by the remote host.’.
2018-02-21 00:10:16.318 Status: 2, code: 10054, text: ‘Communication link failure’.
Additional error occurs are reported if LogReader is not configured correctly to redirected LogReader to new Primary. Error below shows LogReader still trying to connect to sqlserver-0 which is now ReadOnly secondary.
2018-02-21 00:11:57.584 Status: 32768, code: 53044, text: ‘Validating publisher’.
2018-02-21 00:11:57.615 Status: 0, code: 20015, text: ‘The target database (‘AdventureWorks2012′) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.’.
2018-02-21 00:11:57.615 The target database (‘AdventureWorks2012’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
2018-02-21 00:11:57.615 Status: 0, code: 22037, text: ‘The target database (‘AdventureWorks2012′) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.’.
I setup this demonstration using Microsoft Azure VMs preconfigured with SQL 2016 AlwaysOn. I added AdventureWorks2012 to availability group then created a standard Transactional Replication publication on 1 table using primary sqlserver-0 as Publisher.
Next, I did a failover to secondary sqlserver-1 causing LogReader to fail with first block of connectivity errors. Had I configured it correctly using link below, the LogReader would have retried, reconnected to new primary and continued to replicate. However, I skipped sp_redirect_publisher steps on purpose causing LogReader failure. My objective was to see if I could go back through this blog posting while LogReader was in a failed state still trying to connect to old primary (sqlserver-0 secondary and sqlserver-1 now primary).
On a restart, the LogReader fails with error below showing sqlserver-1, new Primary was not yet configured as publisher. This makes perfect sense, as for this repro I had on purpose not yet added sqlserver-1 as possible publisher.
Error messages:
The publisher ‘SQLSERVER-1’ with distributor ‘distributor’ is not known as a publisher at distributor ‘distributor’. Run sp_adddistpublisher at distributor ‘distributor’ to enable the remote server to host the publishing database ‘AdventureWorks2012’. (Source: MSSQL_REPL, Error number: MSSQL_REPL21891)
Get help: http://help/MSSQL_REPL21891
Errors were logged when validating the redirected publisher. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
On the Distributor, under Distributor Properties, Publisher I added sqlserver-1 as a Publisher.
I verified on the Distributor, now shows linked servers to both Publishers.
Following the blog posting step 3) I added my remote distributor on sqlserver-1. I had to specify the replication administrator password created when I first setup Replication. If I forgot it, I could change it on the Distributor Properties, but I’d have to update original publisher with new password. Thankfully I knew the replication admin password, I should, I just set it up 10 minutes ago.
3) Configure Remote distribution on possible publishers (with screen shots)
For the possible publishers and secondary replicas: SRV2 and SRV3, we’ll have to configure the distribution as a remote distribution that we created on SRV1.
Launch SQL Server Management Studio. Using Object Explorer, connect to SRV2 and right click the Replication tab and choose Configure Distribution. Choose ‘Use the following server as the Distributor’ and click Add. Select SRV4 as the distributor.
I did step 4 adding linked server to Subscriber. I tried using Wizard, got error, so I just ran script in blog posting step 4.
EXEC sys.sp_addlinkedserver @server = 'subscriberserver’;
Finally, I ran step 5 in blog against the Distributor updating the MSredirected_publishers table with AlwaysOn Listener name AWListener. Note, only specify name of Primary server where publication was created, not current Primary node name. For me this was sqlserver-0.
USE distribution; GO EXEC sys.sp_redirect_publisher @original_publisher = 'sqlserver-0', @publisher_db = 'AdventureWorks2012', @redirected_publisher = 'AWListener';
I verified distributor was updated correctly.
SELECT TOP (1000) [original_publisher]
,[publisher_db]
,[redirected_publisher]
FROM [distribution].[dbo].[MSredirected_publishers]
original_publisher publisher_db redirected_publisher
------------------------ ---------------------------- ----------------------------------------
sqlserver-0 AdventureWorks2012 AWListener
SUCCESS! I was now able to restart the LogReader while sqlserver-1 (original secondary) is now primary, no failover back to original Primary sqlserver-0 was needed. LogReader, running on my Distributor, connected to new primary and started replicating pending transactions.
Chris Skorlinski
Microsoft SQL Server Escalation Services
