none
Transactional Replication - Disaster Recovery Steps RRS feed

  • Question

  • Hi,

    We have  SQL2016 server with multiple databases and we have some of the databases using the Transactional replication and the subscriber and distributor is in same data center.   We using zerto to replicate these boxes to DR datacenter. Question is, in DT situation, if we bringup the publisher server as of 10.05am  and Distributor as of 10.00am and Subscriber as of 9.55am.

    Publisher is latest and other 2 are 5 and 10mins behind.   Will the replication automatically pickup and continue or since the Publisher is 5 min ahead of distributor, so will it not get those transactions happend in 5mins, because on primary datacenter, logreader already read the tlog and marked those completed.

    Similarly, if the distributor has already delivered some transactions to subscriber and again if those transactions are coming to distributor and tries to deliver to subscriber, it will throw an error right.

    Logreader agent and Distribution Agent, both mark the transactions/repl commands as completed.  If we keep the history, will it work fine with out losing any data.

    Thanks



    ao


    • Edited by Kutty_123 Thursday, November 14, 2019 6:04 PM
    Thursday, November 14, 2019 4:45 PM

All replies

  • Hi Kutty_123,

    >> Will the replication automatically pickup and continue or since the Publisher is 5 min ahead of distributor.

    Yes, the replication will pick up automatically.

    >> if the distributor has already delivered some transactions to subscriber and again if those transactions are coming to distributor and tries to deliver to subscriber, it will throw an error right.

    Please refer to Transactional Replication.

    Best Regards,

    Amelia Gu


    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.

    Friday, November 15, 2019 9:01 AM
  • This is unlikely to work. What will happen is that the subscriber has more current data than the distributor which has more current data than the publisher.

    What you will need to do is run an sp_replrestart which will get everything going again. There is potential for some data loss however. 

    Zerto is a block level SAN replication solution. It takes a copy of the VM/LUN and mirrors it to another server/Data Center. It does not know about any watermark to synchronize all 3 servers at the same point in time.

     

    Friday, November 15, 2019 4:16 PM
    Moderator
  • As Hilary said.

    Because the databases are out of sync, you need to reinit the subscriptions.  In almost all cases with replication on DR, you will need to reinit.  You cannot guarantee the databases are all in sync after the restore.

    Friday, November 15, 2019 5:59 PM