SQL Server Performance

Setting up multiple Distribution DB on Diffrent Se

Discussion in 'Performance Tuning for SQL Server Replication' started by dbdch, Apr 25, 2005.

  1. dbdch New Member

    I would like to set up replication In the scenario where i have 3 servers A (Production), B (Report), C (Backup).
    I want A to be a Publisher and B to be Subscriber and Distributor for Server A. I want another Publication setup ON Server B Which would Use Server C as Its Distributor and Subscriber. I am not having much luck with that.

    I am trying to do this because i would like all database readily available. And Don't have a server which can be just a distribution server to handle replication load.
    Thanks in Advance

  2. deepakontheweb New Member

    Do you want to create a chain of replicaiton for one database ?? Please clear your question.<br /><br />Below can be happen.. <br /><br />Server A <img src='/community/emoticons/emotion-4.gif' alt=':p' />ublisher for db1<br />Server B <img src='/community/emoticons/emotion-2.gif' alt=':D' />istributor for db1/Subscriber for db1 (Also Publisher for db2)<br />Server C <img src='/community/emoticons/emotion-2.gif' alt=':D' />istributor/Subscriber for db2<br /><br />Deepak Kumar<br /><br />--An eye for an eye and everyone shall be blind
  3. satya Moderator

    BOL refers on multiple distribution databases :
    In most cases, you need only one. This feature is for users who are centralizing replication operations and administration and want one distribution server to host many Publishers. You can support many Publishers to one distribution database. However, there may be cases in which you want to separate logical replication applications into separate databases for administration purposes. In this case, use multiple distribution databases. Multiple distribution databases may also provide a performance benefit by reducing contention (both writing to and reading from the distribution database).

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. dbdch New Member

    Thanks for your replies.
    Let me state requierement and setup i have and problem i am running into.

    Database Server Setup:
    Server A: This is on PC [A], Which has Database [Production_db]
    Server B: This is on PC , Which has Database [Reporting_db]
    Server C: This is on PC [C], Which has Database [Backup_db]

    Required Replication Job Configuration:
    Job 1: Configure [Production_db] ON Server A As Publisher,[Reporting_db] ON Server B As Subscriber
    AND Also make Server B as Distributor for this job

    Job 2: Configure [Reporting_db] ON Server B As Publisher,[Backup_db] ON Server C As Subscriber
    AND Also make Server C as Distributor for this job

    Problems I am Running Into:
    I can Configure Job 1 succesfully. While i am setting up Job 1 Server B seems to set up itself as distributor for any future publisher on Server B. So when i try to set up Job 2 It uses server B as Distributor and I can't change that to point to Server C to use as distributor.
    So how can i configure it.

    Reasons why i am looking at this option:
    1) network transfer is vry slow and won't have any hardware upgrade
    2) can't improve the distribution agen't performance. it is copying max 100 transaction consistantly some time little more than that. would like to know though how can i increase thta and also its execution frequency on distribution agent.

    again thanks for your help

  5. Are both the replication types are having same type of Replication - either Pull or Push.

    If it is then you may try to use Pull Replication for 1st Job and Push for the 2nd or vice versa.

    Also keep the latency of job schedule at higher end as small time difference of both job schedules may consume high network traffic on your limited resources.


    Arindam Ganguly

Share This Page