none
Transactional Replication on Always On

    Frage

  • 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

    Freitag, 29. Juni 2018 03:07

Alle Antworten

  • Hi Tharmendran,

    In your scenario, how did you config the transaction replication? Could you please share us more detailed information if possible?

    Here are some articles for your reference:

    https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Montag, 2. Juli 2018 07:19
    Moderator
  • 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

    I see two Server A's. Is the distribution database on Server A (where the publisher is) of AlwaysOn AG? If yes, then when you failed over/shut down A, the distribution database didn't fail over along to B of AG and hence replication stopped. This is understandable. 

    If Distribution is on a totally separate server (and not part of AG at all) then do you have log reader agent job on both AG nodes (A and B)? If no, then this could be a reason. If log reader agent job only exists on A and you failover to B, there's no log reader agent job on B to process log records, then how would the records be sent to the distribution database?

    Note that distribution database can now be part of an AlwaysOn AG starting 2017 CU 6.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Montag, 2. Juli 2018 13:50
  • Hi Mohsin_A_Khan,

    Thanks for your time to reply my thread.

    Actually the distribution is separate server and not part of AG. Yes log readers exist on both AG nodes. The problem i faced is when the primary replica down for long time due hardware faulty, seem replication won't send the transactions as i 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. Is that true?

    Dienstag, 3. Juli 2018 01:43
  • Hi Xi Jin,

    Thanks for your time to reply my thread.

    I have followed the same link you provided to setup the transaction replication.

    The problem i faced is when the primary replica down for long time due hardware faulty, seem replication won't send the transactions as i 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. Is that true?

    Dienstag, 3. Juli 2018 01:46
  •  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. Is that true?

    That is correct. Log reader agent doesn't process any log records that have NOT yet hardened on all replicas (sync and async replicas). In other words, when a new log record is generated on the primary, it has to be hardened on all participating replicas and then only it processes them and sends to the distribution DB. Hence, be it sync or async should not break your replication after failover. 

    Can you check the following?

    1. when you shut down the primary replica and the failover to secondary replica happens, is the log reader job on secondary (new primary) disabled? if yes, then manually enable and start it.

    2. Have you successfully configured the secondary replica as the publisher? step #4 in this article

    3. Have you redirected publisher to Listener? step #5 in this article.  (using sys.sp_redirect_publisher)

    4. run the validation store procedure. step #6 in the above article.

    5. What happens when you manually start the log reader agent job on the new primary (former secondary)? does it error out?


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Dienstag, 3. Juli 2018 02:48
  • Hi Mohsin_A_Khan,

    Thanks again for your reply.

    1)I have try to stop SQL Service in primary replica to simulate the failover when primary replica down. It can successfully failover. The log reader job seem is in distributor server and not in secondary server. Seem not disabled but got error as below;

    2018-07-03 05:42:04.179 Status: 2, code: 20011, text: 'The process could not execute 'sp_replcmds' on '<primary server name>'.'.2018-07-03 05:42:04.179 The process could not execute 'sp_replcmds' on '<primary server name>'.2018-07-03 05:42:04.179 Status: 2, code: 64, text: 'TCP Provider: The specified network name is no longer available.'.2018-07-03 05:42:04.179 Status: 2, code: 64, text: 'Communication link failure'.

    2) Yes done create the linked server

    3)Yes already done redirected publisher to Listener

    4) As mentioned in point 1) log reader it in enabled state but got error.

    Dienstag, 3. Juli 2018 06:02

  • 'The process could not execute 'sp_replcmds' on '<primary server name>'.'.2018-07-03 05:42:04.179 The process could not execute 'sp_replcmds' on '<primary server name>'.2018-07-03 05:42:04.179 Status: 2, code: 64, text: 'TCP Provider: The specified network name is no longer available.'.2018-07-03 05:42:04.179 Status: 2, code: 64, text: 'Communication link failure'.

    OK, it looks like the log reader agent is somehow attempting to connect using the primary replica's name and it's obvious that it'd fail with primary being down. It is expected to use the listener name. (Listener would have correctly routed it to the new primary (former secondary) replica).

    Can you try creating the log reader agent on the secondary replica instead of running it on the Distributor?

    See if that helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Dienstag, 3. Juli 2018 06:48
  • Hi Mohsin_A_Khan,

    Thanks for the reply again.

    I am using push replication where it will create log reader at Distributor itself.

    Dienstag, 3. Juli 2018 08:25