Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Large scale Transactional replication solution (1200+ Pub & Subs) Expand / Collapse
Posted Friday, June 7, 2013 12:12 PM



Group: General Forum Members
Last Login: Tuesday, September 30, 2014 10:10 AM
Points: 15, 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.
Post #1461182
Posted Thursday, June 13, 2013 3:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 15, 2016 1:06 PM
Points: 288, Visits: 1,213

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.


Post #1463334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse