Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘Long disttance replication’

Remote Replication(Long distance Replication)

Posted by Simon Cho on 08/14/2012

1. Most importance is Pull subscription.

Consider Pull or Anonymous Subscriptions

The Distribution or Merge Agent runs on the Distributor for push subscriptions, and on Subscribers for pull or anonymous subscriptions. Using pull or anonymous subscriptions can increase performance by moving Distribution or Merge Agent processing from the Distributor to Subscribers.

You can also offload agent processing by using Remote Agent Activation. Agent processing can be moved to the Subscriber for push subscriptions and to the Distributor for pull subscriptions. Administration of the agent still takes place at the Distributor for push subscriptions and at the Subscriber for pull subscriptions. For more information, see Remote Agent Activation.

Anonymous subscriptions, which are especially useful for Internet applications, do not require that information about the Subscriber be stored in the distribution database at the Distributor for transactional replication and reduces the storage of information about the Subscriber in the publishing database for merge replication. This reduces the resource demands on the Publisher and Distributor because they do not have to maintain information about anonymous Subscribers.

Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution or Merge Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but still stores information at the Publisher. When a publication supports anonymous subscriptions, the publication is configured to always have a snapshot ready for new Subscribers.

For transactional replication, this means that every time the Snapshot Agent runs, a new snapshot will be generated. Typically, a snapshot is not generated if there are no new Subscribers waiting for a snapshot or no Subscriber needs to be reinitialized at the time the Snapshot Agent is run. So while anonymous Subscribers can reduce the resource demands at the Distributor, the tradeoff is that a snapshot is generated more often. With merge replication, a new snapshot is always generated when the Snapshot Agent runs regardless of the type of subscriptions supported by the publication.

2. Change default distribution agent setup.


3. Performance test

Performance Indicators Test Scenarios SQL Server 2005 on Windows Server 2003 (A) SQL Server 2008 on Windows Server 2008 (B) Performance Gains or Losses
CPU Utilization (%) All




Memory All




Push Replication 1-GB 226.12 (minutes) 110.42 (minutes)


1,000,000 1k character records
Pull Replication 1-GB 174.87 (minutes) 12.5 (minutes)


1,000,000 1k character records
Linked Server 10-MB 107.6 (minutes) 113.6 (minutes) –5.28%
10,000 1k character records
Push Replication 112-MB 247.07 (minutes) 59.13 (minutes)


100,000 varbinary (max) records
Pull Replication Records 112-MB 223.18 (minutes) 1.95 (minutes)


100,000 varbinary (max) records
Snapshot Replication 11.3-GB 10,100,000 1k records Not tested 22.75 (minutes) Comparison not available

Posted in Common | Tagged: , , , , | Leave a Comment »