3

In our AlwayOn environment I have two SQL servers 2014 Ent.edition (SERVER A, and SERVER B). I setup a transaction replication for [database A ], pointing to listener which shouldn't cause any issue if a failover happened. Tested, worked just fine after a failover, but when I shut off one of the servers in AOAG ,in test environment, replication stops working. There is no error on the replication monitor. For testing purpose,I have inserted few records on the database it works fine on AOAG, but on the Subscriber the data modification is not replicated until I bring up the other server. Once the server is up, then the replication start to work. I'm pretty sure, I might be missing something, this can't be this way by design.

Any Idea/recommendation ?

Here is what I have for Replication on AOAG.

  • Distribution Server A ( contains Distribution database ) - SQLSERVER
    1. This on a remote server.
  • two subscribers - Sv1 and sv2 - SQLSERVER 2008R2 STANDARD
  • Pbulishers ( SERVER A and B - from AOAG ) - SQLSERVER 2014
Improve this question
6
  • is the node you're shutting down the one that is running your replication jobs?
    – Ali Razeghi - AWS
    Commented Apr 17, 2017 at 22:27
  • @Ali, no, replication jobs are on remote distributer.
    – reshad
    Commented Apr 17, 2017 at 22:32
  • Do you get 1 if you run this on Server A and B? USE master; GO DECLARE @installed int; EXEC @installed = sys.sp_MS_replication_installed; SELECT @installed;
    – SqlWorldWide
    Commented Apr 17, 2017 at 22:37
  • I suggest you go thru the steps outlined in this document. That will confirm that your configuration is done as recommended by Microsoft.
    – SqlWorldWide
    Commented Apr 17, 2017 at 22:39
  • @SQlWW, yes, I have 1 for the result set. but seeing this " Replicated transactions are waiting for next Log backup or for mirroring partner to catch up." on the log reader, I think i should have dig into this, may be a hint.
    – reshad
    Commented Apr 17, 2017 at 22:43

1 Answer 1

Reset to default
7

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

Why does Microsoft impose this restriction on synchronous replicas? If the LSN has not been hardened on the synchronous replica, then the transaction isn't fully committed. Replication is very conservative in what it considers a fully-committed transaction that is safe for replication. Say for example, that the AG isn't in sync due to a network issue, or some other problem (ie, it is online, but the Primary node just doesn't know that). If you force a failover of the AG, and allow data loss, then your replication secondary would have data that was lost during failover-- potentially breaking replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

Enables the replication log reader to move forward even if the async secondaries 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.

Improve this answer
5
  • thanks for the great comment, I think this is a fix where Asyn commit is applied, but I have AG in Symc commit, and want to keep it that way due to the flavor of automatic failover. Any workaround for this? can you repost the link, it didn't work for me?
    – reshad
    Commented Apr 18, 2017 at 12:49
  • I copy/pasted that link from MSFT documentation. Looks like the link is bad in their documentation, and I just copied the bad link. I've removed the link from my answer--if I can find an updated link, I'll add it back in.
    – AMtwo
    Commented Apr 18, 2017 at 13:05
  • There is no way to make the log reader read past transactions that are uncommitted at the synchronous secondary. I've updated my answer with a little bit more detail on that aspect. Essentially, if you lose a synchronous replica, Replication won't consider the transaction fully committed, and won't replicate that transaction.
    – AMtwo
    Commented Apr 18, 2017 at 13:14
  • I think to solve this issue, based on your suggestion, I have to apply a sync primary, Asyn secondary, and turn on the flag?
    – reshad
    Commented Apr 18, 2017 at 14:37
  • I'm going to vote your comment for the best answer. The flag worked for me without changing my commit statues from SYNC to ASYNC. However, I'm going to a add a piece in here to run EXEC sp_replicationdboption 'YOURDATABASE/DISTRIBUTION','sync with backup',false which provokes that the transaction shouldn't be backed up, and can be replicated. Thanks again for the good comment.
    – reshad
    Commented Apr 18, 2017 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.