Replication Tips

Here are some tips that you can use to more efficiently configure replication:

Where possible, publish a table only once

Using the same table in multiple publications will cause multiple distribution events (and thus a higher load on network traffic) every time data in that table changes.

Publish only the amount of data required

Since the publication of replicated data is quite easy to set up, there is a tendency to publish more data than is actually required. This has the effect of both consuming additional resources within the distribution databases and lowering the throughput for required data.

Establish as many subscriptions as possible before initial synchronization

Synchronization of databases or even individual tables requires a fairly high amount of system overhead. Using automatic synchronization on a scheduled basis allows an interval between the time a server subscribes to a publication and the initial synchronization of the destination database on that subscription server. This allows subscription servers a period of minimal impact during which they can subscribe to several publications. It also allows synchronization to be scheduled for a low-activity period on the publication server.

When a scheduled initial synchronization time arrives, synchronization occurs only for those subscribers that have requested synchronization (not for all subscribers to that publication). This minimizes the impact on the other subscribers.

To replicate to numerous subscribers, establish a multilevel replication scheme

A publication server can publish to one or more "publishing subscribers," which in turn publish the data to their own subscribers. For more information, see the advanced replication examples in Chapter 15, Advanced Replication.

To replicate to numerous subscribers, reduce their distribution frequency

A single distribution server will be able to distribute transactions to a larger number of subscribing servers if the frequency of the distribution task associated with each subscribing server is scheduled to run less frequently. Additional performance gains can be achieved by staggering the initial times of these distribution tasks so that they do not all attempt to start simultaneously.

For example, suppose transactions are being replicated to 30 subscribing servers, and distribution is scheduled to occur for all machines every 3 minutes. By creating 10 distribution tasks with a starting time of 12:00 AM, 10 tasks with a starting time of 12:01 AM, and 10 tasks with a starting time of 12:02 AM, the times when these tasks start will be staggered, reducing contention for resources and improving overall throughput.