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
Options available with snapshot replication allow you to filter published data, allow Subscribers to make modifications to
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
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
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.