Performance considerations v7
Keep these general guidelines for performance in mind as you design your replication system.
When to use snapshot or synchronization
Generally, synchronization is the quickest replication method since it applies changes to the target tables only since the last replication occurred.
However, suppose a large percentage of rows change between each replication. At some point it might be faster to completely reload the target tables using a snapshot than to execute individual SQL statements on a large percentage of rows as with synchronization replication. You might need to experiment to determine if, and at what point, a snapshot is faster.
Snapshot replication might be an option for tables with the following characteristics:
- Tables are relatively small
- A large percentage of rows change between replications
Synchronization replication is the better option for tables with the following characteristics:
- Tables are large
- A small percentage of rows change between replications
In a single-master replication system, you might find that one group of tables consistently replicates faster using snapshot replication. In this case, you can make these tables part of a snapshot-only publication while the remaining tables are members of a publication that uses synchronization replication.
When to use on-demand replication
The Replication Server console and CLI both enable you to immediately start a replication. This ability is called on-demand replication.
You can perform on-demand replication at any time regardless of whether there's an existing schedule. An on-demand replication doesn't change the date and time when the next replication will occur according to an existing schedule.
If a publication is a snapshot-only publication, then the only type of on-demand replication that you can perform on the publication is a snapshot.
If a publication is not a snapshot-only publication, you can perform an on-demand replication using either the snapshot method or the synchronization method.
When you're in the development and testing phases of your replication system, you typically use on-demand replication so that you can immediately force the replication to occur and analyze the results.
When your replication system is ready for production, you typically use a schedule so that replications can occur unattended at regular time intervals. See Creating a schedule.
In other situations, you want to force a replication to take place ahead of its normal schedule. Reasons for performing an on-demand replication include the following:
- The number of changes to the source tables is growing at a faster rate than usual, and you don't want to wait for the regularly scheduled synchronization time to replicate all of the accumulated changes.
- You set up your replication system to perform synchronizations, but an unusually large number of changes were made to the source tables. You want to perform a snapshot of all source tables rather than execute a large number of SQL statements against the target tables.
- Changes were made directly to the rows of the target tables so that they no longer have the same content as their source table counterparts. You can perform an on-demand snapshot replication to reload all rows of the target tables from your current set of source tables.
Note
In a multi-master replication system, you can make on-demand snapshots only from the primary definition node to another primary node.
See On-demand replication for information on performing an on-demand replication for a single-master replication system. See On-demand replication for a multi-master replication system.