In our AlwayOn environment I have two SQL servers 2012 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 2012 This on a remote server.
two subscribers - Sv1 and sv2 - SQLSERVER 2012 Enterprise
Publishers ( SERVER A and B - from AOAG ) - SQLSERVER 2012
The log reader agent won't read transactions that aren't hardened on the secondary. It never wants to be in a situation where row exist on the subscriber but are lost when an AG fails back to a node that is behind.
There's a trace flag you can set that will tell replication to go ahead and read the data. I can't remember what it is though.
From the Microsoft documentation on Trace Flag 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.
But i wanted to use sync secondaries in AlwaysOn setup. So is that mean no other way around for this to worn for AlwaysOn Sync secondaries.
I'm not sure what you're asking. If you have synchronous secondaries and they are caught up, everything should work fine. If the synchronous secondaries aren't caught up (for example, turned off), replication won't send transactions that aren't on the secondaries.
Actually i am thinking worst case scenarios that may happen in Always On setup. If suddenly my primary replica down for a long time due to hardware faulty, is that mean replication won't send the transactions right? In that case, is that mean i need break the Always On in order for the replication to works? Or i can just turn on the flag to enable replication log reader to move forward? By doing so, is that mean when my primary comes online after long maintenance i need to re-setup back the replication in my primary replica again?
I'm assuming when you say that the primary goes down a new replica becomes the primary? The only way replication can process at all is if one of the secondaries becomes the primary.
If you have a primary with a secondary that is offline, setting that trace flag will allow the log reader agent to go forward. It may require a restart of the log reader but I don't think so. It's been a while since I've done that.
The secondary coming back and catching up won't cause any problems.
Did you re-point the log reader at the availability group instead of the individual node?
Here are the two bad scenarios.
First, if you fail from the primary to a secondary that isn't caught up, replication will have transactions that aren't in that secondary. That breaks replication. And since you just lost some amount of data you've probably got bigger issues to worry about.
Second, if you have to restore the primary from backup I'm not sure the effect on replication. We've always just torn it down and rebuilt it.
Hi graz,
Thanks for the reply.
Yes what i trying to say is when primary goes down a new replica become the primary. The problem is when the previous primary down for a long period, then replication will not send the transaction to the subscriber. is that we need to manually to set the trace in order to log reader agent to mover forward if one of the replica down? The log are pointing to the listener name. Previously in mirroring, no such problem.
Hi Graz,
Thanks for replying.
So is that mean Always On are not very robust in handling failover inf primary replica down for sometimes and we need to manually set the trace flag and it will broke the Always On which we need to resetup back right?
I don't understand what you are saying . I think the default behavior is robust and works the way we want it to.
By default, transactions that aren't fully committed to a synchronous node aren't replicated. That is the default behavior and I think it is correct. It is very easy to override that with a trace flag.
Nothing about this breaks AlwaysOn. Or replication. I'm not sure what you mean there.
Another option is to run you second node asynchronously. I don't think that will impact replication.
Why can't you just set the trace flag and leave it on? It sounds like that is what you want?