SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Large scale Transactional replication solution (1200+ Pub & Subs)


Large scale Transactional replication solution (1200+ Pub & Subs)

Author
Message
Sailorking
Sailorking
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 126
So I have been working on a large scale replication project that has suddenly come to a standstill and I’m hoping someone here has some advice. My problem is that adding new subscriptions and publications take 15+ minutes and about 30 of the published databases are now behind like 1+ day.

Environment: (All SQL 08 R2 Latest SP)

Published DB’s and Distribution database:
2 node cluster (24 proc server 256GB ram on a high-end SAN)
1,214 Published DBs

Subscriber Server
Same as above

Can Transactional Replication handle this? Right now the CPU and ram is fine and I don’t see an IO load but everything is very very slow! I’m not even sure what to look for. I do notice the only large table in the distribution is the commands table and I can’t even re-index it because it’s always locked. The distribution table has file groups across different LUNs. Should I be creating multiple distribution databases? Should I have the distribution database on its own physical server? I’m at a loss.
Thanks!
vultar
vultar
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 1220
Hi,

How many subscribers have you got.. Are the push or pull ? 1200+ publishers seems a lot for 1 distribution db. Have you Checked the waits, locking, blocking etc ? I would hazard a guess that there's some contention from the log readers & distribution agents.

Cheers

Vultar
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search