none
How to affectively configure and implement a transactional replication?

    Question

  • Hello,

    After several days of researching on how to configure replication, we have decided to go with transactional replication. We will be using the push subscription. With the push subscription does that mean we will have to have a separate server for the distribution agent?  The SQL Server replication topology will be as follows:

    - The Publisher (source) will be on one sever
    - There will be only 1 subscriber (destination) server for now

    Question:

    - Where should the distributor process residues?
    - Will the network speed play a major part in this?

    Thanks.

    Thursday, February 28, 2013 8:27 PM

Answers

  • By default the Distributor is the same server as the Publisher (local Distributor) and is the recommended configuration.  Under a very high load, e.g. multiple publication databases on your Publisher, you may want to move to a remote Distributor to offload processing from the Log Reader and Distribution Agents.  If you are only publishing 1 database then I would recommend to use a local Distributor.

    The network speed will play a role and can be a bottleneck.  Microsoft recommends a fast network of 100 megabits per second or faster in Enhance General Replication Performance.


    Brandon Williams (blog | linkedin)

    Thursday, February 28, 2013 10:02 PM
    Moderator

All replies

  • By default the Distributor is the same server as the Publisher (local Distributor) and is the recommended configuration.  Under a very high load, e.g. multiple publication databases on your Publisher, you may want to move to a remote Distributor to offload processing from the Log Reader and Distribution Agents.  If you are only publishing 1 database then I would recommend to use a local Distributor.

    The network speed will play a role and can be a bottleneck.  Microsoft recommends a fast network of 100 megabits per second or faster in Enhance General Replication Performance.


    Brandon Williams (blog | linkedin)

    Thursday, February 28, 2013 10:02 PM
    Moderator
  • If you are replicating over a wan use pull subscriptions as there are network optimizations with pull.

    On a local segment the network hop is not really that significant but it is over a wan.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, March 01, 2013 1:50 AM
    Moderator
  • Additionally you want consider your needs and ability to recover from a failure state. If your distributor resides on the publication server then the distributor will (should) recover according to whatever your publication server's strategy is. If you distributor is on another server it can fail independently of the publication and has to be provisioned for its own high availability or recovery scenario.

    If you're happy and you know it vote and mark.

    Monday, March 04, 2013 9:53 PM