Environment
-> We had an environment which is same as above environment. Remote distributor, Publisher and subscriber servers were configured in Always ON availability group.
-> I worked on a disaster recovery drill. As part of the DR, All database server on primary datacentre were brought down. Below is the representation after the primary datacentre was brought down.
-> I failed over all database servers from Primary Datacentre to Secondary Datacentre.
-> Failover completed fine. Below is the view in SQL Server management studio,
-> I checked the Replication monitor and found below message in Log reader agent,
Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.
Initializing
Validating publisher
Starting agent.
-> Message “Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.” is as a result of Always ON availability secondary replica being down.
-> With the current setup transactional replication log reader agent will not be able to to move forward as the asynchronous secondary replicas have not acknowledged the reception of a change.
-> One option to solve this issue is to wait for the Always ON secondary replica to come online and that should fix the issue.
-> But in my case Always ON secondary replica will be online after 12 hours. So waiting for the Always ON secondary replica to come online is not an option.
-> Second option will be enable trace flag 1448. Please check article1 and article2 for more details on this trace flag.
-> Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.
-> I enabled trace flag 1448 on the Publisher server JBPRIMARY-DR using below query,
dbcc traceon(1448,-1)-> Once the trace flag 1448 is enabled. Log reader agent completed fine.
-> Please note that this trace flag can be enabled without a SQL Server restart.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.