Load of Transactional Replication at the Publisher

All we need is an easy explanation of the problem, so here it is.

I would like to enable replication on my SQL Server , as per the Document , the load on Server as a Remote Distributor is less(i.e. 8-10% for insert/update in publisher) when compared with load on SQL Server as a Local Distributor (i.e bieng Publisher as well as 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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SQL Server 2000 is a version from ages ago, that I wouldn’t necessarily follow most documentation on best practices for it when dealing with a newer version, such as SQL Server 2017.

Anecdotally, I’ve been using Transactional Replication for the last 10 years for instances running SQL Server 2008 R2 up to instances running SQL Server 2019 currently, and I’ve always ran the Distributor on the Publisher server. I’ve never had any noticeable issues as a result of doing this. I’ve used it with some fairly busy servers that had a good amount of data being replicated across them.

If that is your only option for implementing replication on your servers, then I’d say try it. If you notice adverse performance issues as a result of doing it, then you can disable the replication jobs or drop the Publications. But in my opinion, I’d say low risk of having any tangible issues from running the Distributor on the Publisher server.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply