SQL Server Performance

Change Push to Pull transactional replication

Discussion in 'SQL Server 2008 Replication' started by WingSzeto, Jun 15, 2011.

  1. WingSzeto Member

    We currently are employing Push transactional replication. Publisher and distrisbutor are on the same SQL server, say server A. We have one subscriber on it own server serving as a reporting server say B. All SQL server versions are SQL 2008 standard.

    We want to add another subscriber server, say C and considering using a Pull subscrption for it but leave the other subscription for server B as Push. The reason being is to off load some of the replication process from publisher server A to server C. Server A is our main transaction database server serving our web customers and sometimes it is quite busy processing queries.

    My question is, is there any concern or issue for setting a push and pull transactional replication instead of all push or all pull?

    w
  2. satya Moderator

    Unless your existing transactional load is manageable its not a problem to add another pair to the replication, it all depends on how your current replication is working...if no errors...then no issues
  3. pyale New Member

    It's worth pointing out, though that PULL replication is much more scalable than PUSH replication.
  4. satya Moderator

    Can you explain on SCALABLE terms between these two?
  5. pyale New Member

    Certainly.
    With PUSH replication, you require a distribution agent for each publication per subscriber. e.g. 5 publications with 5 subscribers = 25 distribution agents all running at the distributor. We use very wide-ranging replication with many publishers, subscribers and publications, and have encountered the situation several times now where the distributor server has exceeded the maximum limit for distribution agent resources (and it's a very big powerful server).

    With PULL replication, the various distribution agents run locally on each subscriber, spreading the load more thinly between the many subscribing members. The distributor server is no longer a bottleneck for the distribution agent management.
  6. satya Moderator

    Did you consider the number of transactions and volatalie processes from the databases, it may not be suitable in this case.

Share This Page