SQL SERVER – Replication Keywords Explanation and Basic Terms

SQL Server replication has a vocabulary that can sound confusing when you first encounter it. I put this reference together after discussing the basics with database administrators who were new to replication.

Publisher

The Publisher makes data and database objects available for replication. A publishing database contains the objects selected for a publication. One Publisher can have multiple publications.

Article and Publication

An article identifies a database object included in replication. Depending on the replication type, an article can represent a table, view or another supported object. Filters can restrict which table rows or columns are sent.

A publication groups one or more articles from one database. Think of it as the set of objects and data you choose to distribute together.

Distributor

The Distributor stores replication metadata and helps move data between the Publisher and Subscribers. Depending on the configuration, it can be on the same server as the Publisher or on another server.

Subscriber and Subscription

A Subscriber receives a publication in a subscription database. A subscription defines the publication to receive and where it goes. Depending on the replication type and configuration, a Subscriber can also send changes back.

With a push subscription, the Distribution Agent or Merge Agent runs at the Distributor. With a pull subscription, the corresponding agent runs at the Subscriber. The right choice depends on the topology and operational needs.

Three Common Types of Replication

Snapshot replication distributes a snapshot of data at a point in time. Transactional replication delivers changes from the Publisher to Subscribers. Merge replication supports changes at both ends and synchronizes them when connections are available.

The behavior and supported configurations differ, so choose a type based on the application rather than on the name alone. A distributed transaction and two-phase commit are separate concepts, not a fourth type of SQL Server replication.

A Simple Example

Suppose a central database publishes a product table to a reporting database. The table is an article, the group containing it is a publication, and the reporting database receives it through a subscription.

Replication is useful for moving selected data, but it does not replace tested backups. A replicated copy can also receive unwanted changes. Monitor the agents and keep a recovery plan for each database.

Replication is not just copying a table, it is a set of roles and processes working together.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.