Replication Basics

Replication's ability to deliver data where and when you need it makes it a powerful solution for the following business scenarios:

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

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