Introducing the Types of Replication

There are three types of replication available with Microsoft® SQL Server™ 2000: snapshot replication, transactional replication and merge replication.

Snapshot Replication

Snapshot replication is the process of copying and distributing data and database objects exactly as they appear at a moment in time. Snapshot replication does not require continuous monitoring of changes because changes made to published data are not propagated to the Subscriber incrementally. Subscribers are updated with a complete refresh of the data set and not individual transactions. Because snapshot replication replicates an entire data set at one time, it may take longer to propagate data modifications to Subscribers. Snapshot publications are typically replicated less frequently than other types of publications.

Options available with snapshot replication allow you to filter published data, allow Subscribers to make modifications to replicated data and propagate those changes to the Publisher and then to other Subscribers, and allow you to transform data as it is published.

Snapshot replication can be helpful in situations when:

• Data is mostly static and does not change often.
  
  
• It is acceptable to have copies of data that are out of date for a period of time.
  
  
• Replicating small volumes of data.
  
  
• Sites are often disconnected and high latency (the amount of time between when data is updated at one site and when it is updated at another) is acceptable.

Transactional Replication

With transactional replication, an initial snapshot of data is propagated to Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

SQL Server 2000 monitors INSERT, UPDATE, and DELETE statements, and changes to stored procedure executions and indexed views. SQL Server 2000 stores the transactions affecting replicated objects and then it propagates those changes to Subscribers continuously or at scheduled intervals. Transaction boundaries are preserved. If, for example, 100 rows are updated in a transaction, either the entire transaction with all 100 data modifications are accepted and propagated to Subscribers or none of them are. When all changes are propagated, all Subscribers will have the same values as the Publisher.

• Options available with transactional replication allow you to filter published data, allow users at the Subscriber to make modifications to replicated data and propagate those changes to the Publisher and to other Subscribers, and allow you to transform data as it is published.

Transactional replication is typically used when:

• You want data modifications to be propagated to Subscribers, often within seconds of when they occur.
  
  
• You need transactions to be atomic (either all or none applied at the Subscriber).
  
  
• Subscribers are mostly connected to the Publisher.
  
  
• Your application will not tolerate high latency for Subscribers receiving changes.

Merge Replication

Merge replication allows various sites to work autonomously (online or offline) and merge data modifications made at multiple sites into a single, uniform result at a later time. The initial snapshot is applied to Subscribers and then SQL Server 2000 tracks changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers either at a scheduled time or on demand. Updates are made independently (no commit protocol) at more than one server, so the same data may have been updated by the Publisher or by more than one Subscriber. Therefore, conflicts can occur when data modifications are merged.

Merge replication includes default and custom choices for conflict resolution that you can define when you configure a merge publication. When a conflict occurs, a resolver is invoked by the Merge Agent to determine which data will be accepted and propagated to other sites.

Options available with merge replication include filtering published data horizontally and vertically, including join filters and dynamic filters, using alternate synchronization partners, optimizing synchronization to improve merge performance, validating replicated data to ensure synchronization, and using attachable subscription databases.

Merge replication is helpful when:

• Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
  
  
• Subscribers need to receive data, make changes offline, and synchronize changes later with the Publisher and other Subscribers.
  
  
• The application latency requirement is either high or low.
  
  
• Site autonomy is critical.

See Also

Designing a Replication Topology

Planning for Replication

Replication Options

Types of Replication

Validating Replicated Data