Replication's ability to deliver data where and when you need it makes it a powerful solution for the following business scenarios:
- Supporting a disconnected mobile sales force
- Maintaining a warm-standby server for failover
- Maintaining an online analytical processing (OLAP) server
- Aggregating sales from distributed servers across the enterprise into one database
- Scaling out to multiple e-commerce database servers from one database
There are three main types of replication—snapshot, transactional, and merge—and each supports varying degrees of data latency, from realtime updates to offline or disconnected scenarios. Snapshot replication, the simplest type of replication, involves capturing an instantaneous view of a publication as a data "snapshot" and transferring this snapshot to the Subscriber. Snapshot replication is useful when your data is relatively static or when you can tolerate high latency. You also use snapshot replication to provide the initial data for both merge and transactional replication.
Transactional replication begins with a snapshot of the publication data that you want to transfer to the Subscriber. From then on, the replication process sends changes to the Subscriber as they occur. Although you generally use transactional replication to transmit data only from the Publisher to the Subscriber, it supports the ability to propagate updates made at the Subscriber back to the Publisher—an ability known as updateable subscriptions. The replication process can even queue these Subscriber changes if connectivity is lost. However, if you need to support a scenario in which users are making many changes to the data at the Subscriber or in which the Subscriber will be offline a significant amount of time, your best bet is merge replication.
In merge replication, users can freely make changes to published data at both the Publisher and the Subscriber, even in a fully disconnected state. When a connection between the Publisher and Subscriber is established, merge replication uploads the Subscriber's changes to the Publisher, uses predefined rules to resolve any of the Subscriber's changes that conflict with changes made at the Publisher, and downloads the Publisher's changes (including any resolved conflicts) to the Subscriber. This process is known as synchronization. Merge replication also supports SQL Server 2000 Windows CE Edition Subscribers, making it the ideal solution for replicating data to handheld devices.
SQL Server replication is implemented by a set of replication system tables at the Publisher and Distributor (and at the Subscriber, in the case of merge replication), a group of replication system stored procedures, and a group of replication agents. These agents, which the SQL Server Agent can host and schedule, manage the flow of replication data and implement replication functionality. The principal replication agents are
- The Snapshot Agent. This agent, used in all types of replication, runs at the Distributor and manages the data snapshot.
- The Log Reader Agent. In transactional replication, each publication database has its own Log Reader Agent, which runs at the Distributor and reads data from the log on the publication database.
- The Distribution Agent. This agent, used in snapshot and transactional replication, runs either at the Distributor or at the Subscriber and is responsible for moving data from the Distributor to the Subscriber.
- The Merge Agent. The Merge Agent runs either at the Distributor or at the Subscriber to apply the initial snapshot at the Subscriber. It then transfers and reconciles all data changes during a merge synchronization.
How the Distribution and Merge agents behave also depends on the type of subscriptions they're managing. Replication has two main types of subscriptions: pull subscriptions, which are managed by the Subscriber, and push subscriptions, which are managed by the Distributor. For push subscriptions, these agents run on the Distributor, and the Publisher/Distributor manages how the agents behave. For pull subscriptions, the agents run at the Subscriber. Anonymous pull subscriptions are a special type of pull subscription in which the Publisher doesn't need to maintain the complete set of metadata about the Subscribers.
You can monitor all these agents by using Enterprise Manager's Replication Monitor component. In addition, you can access most of their functionality programmatically by using replication stored procedures or command-line utilities or by using replication ActiveX controls, as the main article describes. For more information about programming replication and SQL Server replication in general, see the wealth of information in SQL Server 2000 Books Online (BOL).
