Post reply

Cost of Transactional Replication at the Publisher

  • July 13, 2022 at 10:25 am

    #4062648

    I would like to enable replication on my SQL Server , as per the Document , the load on Sever as a Remote Distributor is less(i.e. 8-10% for insert/update in publisher) when compared with load on Server as a Local Distributor (Publisher and Distributer) (16-25% for insert/update).[Page no : 11]

    Also The Document (related to MSSQL Server 2000) mentions the cost under stress conditions can be as low as 8-10% when using a remote Distributor, and somewhat higher when using a local Distributor , does this hold good for MSSQL 2017 also? Why I ask this is , I am planning to make my SQL server as Local Distributor because of my current limitations. Should I go ahead with my plan or are there any serious Drawbacks?

  • July 14, 2022 at 11:10 am

    #4063222

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • July 14, 2022 at 5:08 pm

    #4063428

    I've searched for a more recent document that mentions performance in terms of overhead percentages but found none.  I wouldn't expect to see such a thing from future MS documentation because people try to hold MS to the letter if they quantify overhead.

    I don't use any type of replication and haven't for about 15 years so I can't even provide you a guess.  I did hate it for a lot of reasons but that's a different story.  I do suspect that the percentages you mention from that old article haven't change so very much if they were accurate to begin with.

    What you also need to really read up on is how it affects the log files for backups and when they'll actually be "truncated".  You might also want to read up on VLF sizes and the frequently significant issues that can occur when VLFs are too small, etc.

    You also need to know that if you have anything where you're trying to take advantage of minimal logging (like big imports or index rebuilds), that's going away because you'll no longer be able to switch to the "BULK LOGGED" recovery model for those evolutions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply