bi-directional transactional replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

bi-directional transactional replication

I am testing the following configuration: Servers: (all are Windows 2003 with SQL Server 2000)
Publication server/database
Distrubution server/database
Subscriber 1 server/database
Subscriber 2 server/database
Distribution2 server/database (if needed) I have set up the transactional publication to have queued updatable subsciptions.
What would be the pros and cons to either of the following:
1) Have the Distribution Agent run at the Subscribers
2) Create a second distribution database/server for just the subscriber databases.

When using SQL Server 2000 queue, each Subscriber has its own queue in the form of a SQL Server 2000 table (MSreplication_queue) in the subscription database. The triggers store all messages in the SQL Server 2000 queue until the Subscriber reconnects to the network after updating published data. The Subscriber and the Publisher must be connected and available for the updates to occur. I don’t see any specific recommendations in this fashion and ensure the SQLAgent has required privileges between the server, also to maintain similar levels of service packs, fixes and MDAC levels. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I agree with you on what you have stated about queued updates but that does not answer the question.
In our environment, the Publisher and Subscribers will be connected most of the time and the Distribution Agents are set to run continuously, but where they should run and what configuration is best, those are the question? Assume that you have 250 users hitting each Publication and/or Subscription databases for 8 hours a day for 5 days a week. Also, assume that the Servers were built to handle the load (in other words, no performance issues if the servers were stand alone). Finally, assume that the network is up to snuff and can also handle the load and the sites will connect via T1 or T3 lines.
option 1) While all servers are connected all transactions from the Publication DB, Subscriber DB1 and Subscriber DB2 go through one DISTRIBUTION DB. To clarify, the Publication DB has one publication with a subscription to Subscriber DB1 and another subscription to Subscriber DB2. Both Subscriptions are set up to have the Distribution Agent run remotely on the subscriber server as well as are configured for queued updates. This is one way of relieving contention or a "bottle neck" at the DISTRIBUTION DB is to have the distribution agent run remotely on either of the subscriber databases Publication DB
|
|
DISTRIBUTION DB
| |
| |
| |
Subscriber DB1 Subscriber DB2 (Subscriptions are configured to remotely run Distribution agent on the subscribers instead of at the DISTRIBUTION DB)
option 2) Lets say you have six servers. Three of server are configured to be a Publisher and three are configured to be distribution databases only. Lets name the boxes, MSTR PUB, PUB1, PUB2, MSTR DIST, DIST1, and DIST2. MSTR PUB uses the MSTR DIST distribution db and has a publication with subscriptions to PUB1 and PUB2. PUB1 uses the DIST1 distribution db and has a publication with Subscriptions to MSTR PUB and PUB2. PUB2 uses the DIST2 distribution db and has a publication with subscriptions to MSTR PUB and PUB1. —–> MSTR PUB <—-
| | |
| | |
| MSTR DIST |
| | | |
| | | |
| Pub1 Pub2 |
| | | |
| | | |
Dist1 Dist2

]]>