We set up transactional replication on a database that is part of an AlwaysOn Availability Group on our work environment:
- The primary is SP1 and the secondary replica is SS2.
- The distributor is on another server SD3. The replication is working fine.
We redirected the Original Publisher to the AG Listener Name using this script:
USE distribution; GO EXEC sys.sp_redirect_publisher @original_publisher = 'MyPublisher', @publisher_db = 'MyPublishedDB', @redirected_publisher = 'MyAGListenerName';
To implement this, we follow this documentation:
- Configure Replication for Always On Availability Groups (SQL Server)
- Setting up Replication on a database that is part of an AlwaysOn Availability Group
The problem is when we perform a failover from my primary SP1 to our secondary replica SS2. The replication monitor is still showing the information from the old primary SP1 (that became the secondary after the failover). And the more important is that the log reader picks up transactions from the old primary SP1. That means if SP1 server fails, replication will not work anymore.
Any idea? Have you made replication with an AG subscriber work? Recommendations or advice?