none
SQL Server Replication Options RRS feed

  • Question

  • We currently have three main sql servers. Server sql4 (on prem) is our Publisher and Distributor and does push transactional replication to servers sql3 (on prem) and sql7 (ec2 instance AWS).

    We have issues often with replication getting behind on the two subscribers. We are looking to see what our best options are for keeping current data on both sql3 and on sql7. We have other applications and databases on both sql3 and sql7, but are only replicating one database which is over one terabyte in size. We would like to know "in a perfect world" what the best way is to have current data on both the second on prem server and the server in AWS.

    One option I know of is to set up a stand-alone distributor on prem, but I don't know how much of a difference that will make. I see deadlocks often between sql4 replication and other applications. If sql4 was NOT doing the distributing, would those deadlocks go away? If a stand-alone distributor did the distribution to the two subscribers, would that eliminate replication distribution deadlocks of the distributor did nothing else but distribute?

    I'm open to any suggestions. I just want to eliminate the bottleneck and have the data be more consistently current on both the subscribers.

    Thanks in advance for your suggestions.

    Monday, August 5, 2019 8:55 PM

All replies

  • Hi carlaromere,

     

    >>We have issues often with replication getting behind on the two subscribers. We are looking to see what our best options are for keeping current data on both sql3 and on sql7.

     

    It is the transactional replication latency. How long is the data synchronization delay time? Would you please follow the steps below to troubleshoot the transactional replication latency issue: Divide and Conquer Transactional Replication using Tracer Tokens and Troubleshooting transactional replication latency issues in SQL Server

     

    Best regards,

    Dedmon Dai


    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

    Tuesday, August 6, 2019 3:00 AM
  • If the deadlocks are between the replication agents and user processes and your server is cpu bound - moving to a remote distributor might reduce deadlocks.

    You might want to try to determine what is causing the replication latency. It is likely trying to replicate large batch processes or long running transactions. Use tracer tokens to determine where your latency is occurring and address is that way.

    Tuesday, August 6, 2019 2:09 PM
    Moderator
  • I am putting together a plan to move the distributor to its own server. This is not something I have ever done. When I do this, will I have to completely kill replication and start over, or I can I stop all of the replication jobs, modify the replication script to point to a different distribution server, and then start all the jobs back up again and have them actually work?
    Wednesday, August 7, 2019 4:14 PM
  • No, when you move the distributor you will need to drop all publications on that server (and all subscriptions) and any other publications on remote servers which use the distributor.
    Wednesday, August 7, 2019 4:16 PM
    Moderator
  • I was so afraid that would be the case. That just turned into a many-hour project!
    Wednesday, August 7, 2019 6:01 PM
  • Hi there,

    Did you ever work with the trace token in the Replication Monitor to check where the latency is? I mean in the log reader agent or distributor agent? 

    Thursday, August 8, 2019 8:54 AM