Overview of three types of replication for SQL SERVER2005

One, transactional replication

Transactional replication typically starts with a snapshot of the publication database objects and data. After the initial snapshot has been created, data changes and schema modifications made at the publisher are usually passed to the subscriber at the time of the modification (almost real-time). Data changes are applied to subscribers in the order they occur at the publisher and at the transaction boundaries, so transactional consistency can be guaranteed within the publication.

Transactional replication is typically used in a server-to-server environment and is appropriate for transactional replication in the following various scenarios:

• You want the incremental changes to propagate to subscribers when they occur.
• When changes are made from the Publisher to the Subscriber, the application needs a short lag between the two.
• The application needs to access the intermediate data state. For example, if a row is changed five times, transactional replication will allow the application to respond to each change (for example, firing a trigger) rather than responding to the row's final data change.
• The publisher has a large number of INSERT, update, and delete activities.
• The Publisher or subscriber is not a SQL Server database (for example, Oracle).

By default, Subscribers to transactional publications should be treated as read-only because changes will not propagate back to the publisher. However, transactional replication does provide the option to allow updates at the Subscriber.

Second, snapshot replication

Snapshot replication distributes data at the instantaneous state of a specific moment without monitoring updates to the data. When synchronization occurs, a full snapshot is generated and sent to the Subscriber.

Attention:
Snapshot replication can be used by itself, but snapshot processing, which is responsible for creating copies of all objects and data specified by the publication, is also typically used to provide the initial set of data and database objects for transactional and merge publications.

Using snapshot replication itself is most appropriate when one or more of the following conditions are met:

• Data is rarely changed.
• Allows for a period of time a copy of data that has a relative publisher that is obsolete.
• Copy small amounts of data.
• A large number of changes occur in the short term.

Snapshot replication is most appropriate when the amount of data changes is large, but rarely occurs. For example, if a sales organization maintains a list of product prices and these prices are to be fully updated one or two times a year at a fixed time, it is recommended that the full data snapshot be replicated after the data changes. For certain types of data given, more frequent snapshots may also be appropriate. For example, if you update a relatively small table at the publisher during the day, but you can accept a certain latency, you can pass the change as a snapshot at night.

The continuous overhead of snapshot replication at the publisher is lower than the cost of transactional replication, because incremental changes are not tracked. However, if the dataset you are replicating is very large, you will need to use a lot of resources to build and apply the snapshot. When you evaluate whether to use snapshot replication, you need to consider the size of the entire dataset and how often the data is changed.

Third, merge replication

As with transactional replication, merge replication typically starts with a snapshot of the publication database objects and data, and uses triggers to track subsequent data changes and schema modifications made at the Publisher and Subscribers. The Subscriber synchronizes with the publisher when connected to the network and swaps all rows that have changed between the Publisher and the Subscriber since the last synchronization.

Merge replication is typically used in server-to-client environments. Merge replication applies to the following scenarios:

• Multiple Subscribers may update the same data at different times and propagate their changes to the publisher and other Subscribers.
• Subscribers need to receive data, change data offline, and synchronize changes with the Publisher and other subscribers at a later time.
• Each subscriber requires a different data partition.
• Conflicts can occur and you need to have the ability to detect and resolve conflicts when a conflict occurs.
• The application requires the result of the final data change, rather than accessing the intermediate data state. For example, if a row at the Subscriber changes five times before the subscriber synchronizes with the publisher, the row is changed only once at the publisher to reflect the final data change (that is, the value of the fifth change).

Merge replication allows different sites to work autonomously and merge updates into one consolidated result at a later time. Because updates are made on multiple nodes, the same data may be updated by the Publisher and multiple subscribers. As a result, conflicts may arise when merging updates, and merge replication provides multiple ways to handle conflicts.