Components of SQL Server Replication
These basic components comprise SQL Server's replication model.
- Publisher
- The Publisher is a server that makes data available for replication to other servers. In addition to identifying the data to replicate, the Publisher detects the data that has changed and maintains information about all publications on the server. Any given data element that is replicated has a single Publisher, even if the data can be updated by any number of Subscribers or published again by a Subscriber.
- Subscribers
- Subscribers are servers that store replicas and receive updates. In earlier versions of SQL Server, updates could be performed only at the Publisher. However, SQL Server 7.0 allows Subscribers to make updates to data, although a Subscriber making updates is not the same as a Publisher. A Subscriber can, in turn, also become a Publisher to other Subscribers .
- Publication
- The publication is a collection of one or more articles, and an article is a grouping of data to be replicated. An article can be an entire table, only certain columns (using a vertical filter), or only certain rows (using a horizontal filter), or even a stored procedure (in some types of replication).
- Distributor
- The distributor is a server that contains the distribution database. The exact role of the Distributor is different in each type of SQL Server replication.
- Pull subscription
- The pull subscription is one in which the Subscriber asks for periodic updates of all changes at the Publisher. Pull subscriptions are best for publications that have a large number of Subscribers (for example, Subscribers using the Internet). Pull subscriptions are also best for autonomous mobile users because they determine when the data changes are synchronized. A single publication can support a mixture of push and pull subscriptions.
- Push subscription
- The push subscription is one in which the Publisher propagates the changes to the Subscriber without a specific request from the Subscriber. Push subscriptions are used to propagate changes as they occur, or to set the schedule set by the Publisher.
- Snapshot Agent
- A Snapshot Agent prepares the schema and initial data files of published tables and stored procedures, stores the snapshot on the Distributor, and records information about the synchronization status in the distribution database. Each publication has its own Snapshot Agent that runs on the Distributor and connects to the Publisher.
- Log Reader Agent
- A log reader agent 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
- A Distribution Agent moves the transactions and snapshot jobs held in distribution database tables to Subscribers. Transactional and snapshot publications that are set up for immediate synchronization when a new subscription is created have their own Distribution Agent that runs on the Distributor and connects to the Subscriber. Transactional and snapshot publications not set up for immediate synchronization share a Distribution Agent across the Publisher/Subscriber pair that runs on the Distributor and connects to the Subscriber. Pull subscriptions to either snapshot or transactional publications have Distribution Agents that run on the Subscriber instead of the Distributor. The Distribution Agent typically runs under SQL Server Agent and can be administered directly using SQL Server Enterprise Manager.
- Merge Agent
- A Merge Agent moves and reconciles incremental data changes that occurred after the initial snapshot was created. In merge replication, data moves either in both directions (first from the Subscriber to the Publisher, and then from the Publisher to the Subscriber) or in one direction only. Each merge publication has its own Merge Agent that connects to both the Publisher and the Subscriber and updates them. Push subscriptions to merge publications have Merge Agents that run on the Publisher, while pull subscriptions to merge publications have Merge Agents that run on the Subscriber. Snapshot and transactional publications do not have Merge Agents. The Merge Agent can also be embedded and driven in an application using the Microsoft ActiveX control.
- Two-phase commit
- A two-phase commit is a process that ensures transactions that apply to more than one server are completed either on all servers or on none.