SQL Server Performance

Scaling out sql2k5 transactional replication

Discussion in 'SQL Server 2005 Replication' started by golem79, Mar 29, 2008.

  1. golem79 New Member

    <p>Hello there,</p><table cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td class="smalltxt" valign="top">I'm searching for thebest solution to scale out transactional replication with hundreds ofpublications, and that's the situation: we use transactional replication (push, no update) with almost 100 articles (tables and sp) on sql2k5.<br>We'vethe online database, that expose a publication with no filters to thedistribution database, which is the remote distributor for thatpublication . We use that way to not stress the online database withreplication log reader of distribution database.<br>The distrib dbitself expose 150 publications (the same base publication, but withdifferent filters on articles - based on subscriber) to 150subscribers, and he is local distributor of itself.<br><br>This seemsto work: we've a latency of 2-max 7 sec. in the normal work, but...whenwhe do large insert/update (almost 100.000 records), the distributionserver goes down, with 100% cpu for hours and hours, and the latencygoes to the sky.<img src="http://www.sqlservercentral.com/Forums/Skins/Classic/Images/EmotIcons/Blink.gif" title="Blink" align="absmiddle" border="0"><br>Theother problem is that it seems whe reached out the hardware resource ofthe distributor server (150 publications, almost 10-15 mb perdistrib.exe), so we're searching for a new scaled solution.<br><br>The question is: there is a good way to scale out sql2k5 transactional replication, having more than one distributor server?<br>Can I have an online database and have a lot of distribution server, each with some publications to load the balance?<br>I also took a look to log shipping, but..can it work with transactional replication?<br><br>Thanks in advance, questions, ideas, criticism and comments are welcome!<img src="http://www.sqlservercentral.com/Forums/Skins/Classic/Images/EmotIcons/Wink.gif" title="Wink" align="absmiddle" border="0"></td></tr><tr><td valign="top"><br></td></tr></tbody></table>
  2. ghemant Moderator

  3. golem79 New Member

    Thanks for the help ghemant, i'll take a look to these articles.
    bye,
    Maurizio Mangiavacchi
  4. satya Moderator

    Can the transaction be kept in smaller batches?
    What is the subscription agent settings?
  5. golem79 New Member

    Thanks for the reply satya.
    Well, i think we should reduce the transaction dimension, not to freeze the replication during big insert/update.
    The subscription agent is push.
    But...there isn't a possibility to have more than one distribution server?
    Thanks in advance,
    Maurizio Mangiavacchi


Share This Page