Wednesday, October 7, 2015

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Hi, I have set up Always on Availability Groups on top of Windows Server Failover Cluster(WSFC) with two sql server stand alone installations
Server1--Primary Replica
Server2--Secondary Replica
--Both above configured in Always on Availability Groups( listener name=AGListner)
Server 3-Remote Distributor Server where all sql agent jobs (Log,snapshot,dist jobs)...
Server 4--Subscriber server
I have setup Replication on Server 1 which was primary at the time of configuring replication to server 4(subscriber) 
worked fine, replicated data, when failover happened  Server 2 took over as primary and still replication worked fine...
my Issues are below and i am looking for some assistance 
1.When i shout down Server 1(for testing), Server 2 took over the role as Primary and served the applications, but when i insert data at server 2( which is now primary), server 4 Subscriber did not get the data...once i turn on Server1 , the replication is replicated and subscriber is getting data ...does 2 servers(AG Servers) should be ruining in order to replication work?
2.and when i see the Replication monitor I see the All subscriptions are running, Agents are completed and running (ALL GREEN), also jobs on distributor are still running but server 4 Subscriber did not get the data..



his helped me ..this is a life saviour
DBCC Traceon (1448,-1)
--no sql server restart needed. 

--Enables the replication log reader to move forward even if the async secondaries(AG, MIrror databases) have not acknowledged the reception of a change. 
--Even with this trace flag enabled the log reader always waits for the sync secondaries. 
--The log reader will not go beyond the min ack of the sync secondaries. 
--This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, 
--or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of
-- time or when an async secondary fails.

4 comments:

  1. Did you get solution of above issue???

    ReplyDelete
  2. yes...it worked in my production environment ..we did a Planned failover couple of times and it worked fine.

    ReplyDelete
  3. Naresh; This really helped me as I was facing same issue yesterday in our SQL 2014 AlwaysON environment. I failed over that group to Secondary and then failed back to Primary and my replication started working. Thanks a Lot!

    ReplyDelete

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...