Setting up multiple Distribution DB on Diffrent Se | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Setting up multiple Distribution DB on Diffrent Se

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
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
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.
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

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. Regards. Arindam Ganguly