Selecting the Appropriate Type of Replication

Data is typically replicated between servers to support the following applications and requirements:

Data is typically replicated between servers and clients (including workstations, laptops, tablets, and devices) to support the following applications:

The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications, but snapshot replication can also be used by itself. Using snapshot replication by itself is most appropriate when one or more of the following is true:

• Data changes infrequently.

• It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.

• Replicating small volumes of data.

• A large volume of changes occurs over a short period of time.

Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.

Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

• You want incremental changes to be propagated to Subscribers as they occur.

• The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.

• The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.

• The Publisher has a very high volume of insert, update, and delete activity.

• The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

By default, Subscribers to transactional publication should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber. For more information, see the section "Updating Data at Subscribers" in this topic.

Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:

• Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.

• Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.

• Each Subscriber requires a different partition of data.

• Conflicts might occur and, when they do, you need the ability to detect and resolve them.

• The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row changes only once at the Publisher to reflect the net data change (that is, the fifth value).

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts.