• 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.

Agents used with Microsoft® SQL Server™ 2000 replication carry out the tasks associated with copying and distributing data. SQL Server 2000 replication uses SQL Server Agent as well as agents that are specific to replication.

SQL Server Agent

SQL Server Agent hosts and schedules the agents used in replication, and provides an easy way to run replication agents. SQL Server Agent also controls and monitors several other operations outside of replication, including monitoring the SQL Server Agent service, maintaining error logs, running jobs, and starting other processes.

Snapshot Agent

The Snapshot Agent is used with all types of replication. It prepares schema and initial data files of published tables and stored procedures, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent typically runs under SQL Server Agent at the Distributor and can be administered using SQL Server Enterprise Manager.

Log Reader Agent

The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Distribution Agent

The Distribution Agent is used with snapshot replication and transactional replication. It moves the snapshot jobs and transactions held in the distribution database to Subscribers. The Distribution Agent typically runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Merge Agent

The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber, and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent typically runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions. The Merge Agent typically uploads changes from the Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber during a typical bidirectional merge. Changes can also be moved in one direction by configuring the exchange type of the agent.

Queue Reader Agent

The Queue Reader Agent is used with snapshot replication or transactional replication with the queued updating option, or if the immediate updating with queued updating as a failover option is enabled.

The Queue Reader Agent is a multithreaded agent that runs on the Distributor. It is responsible for taking messages from a queue and applying them to the appropriate publication.

Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given Distributor.

Miscellaneous Agents

Clean up agents listed under the Miscellaneous Agents folder in Replication Monitor complete scheduled and on-demand maintenance of replication.

Replication Monitor

Through Replication Monitor in SQL Server Enterprise Manager, you can view and manage replication agents responsible for various replication tasks. For example, you can set up transactional replication so that the log on the Publisher is read continuously, transactions are distributed to Subscribers every ten minutes (although this is often also continuously), and initial snapshots are generated every night at midnight. You can also execute replication agents on demand.

Replication Monitor provides a way to set alerts on replication events. When the event occurs, Replication Monitor responds automatically, either by executing a task that you have defined or by sending an e-mail or a pager message to a specified individual.