SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data.
An article is the basic unit of SQL Server Replication. An article can consist of tables, stored procedures, and views.
A Publication is a logical collection of articles from a database. The entity allows us to define and configure article properties at the higher level so that the properties are inherited to all the articles in that group.
The Publisher is a database instance that makes data available to other locations through SQL Server replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.
The distribution database identifies and stores SQL Server replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers.
Depending on the replication model, the Distributor might also be responsible for notifying the Subscribers that have subscribed to a publication that an article has changed. Also, the distribution database maintains the integrity of the data.
A database instance that consumes SQL Server replication data from a publication is called a Subscriber. The subscriber can receive data from one or more publishers and publications. The subscriber can also pass data changes back to the publisher or republish the data to other subscribers depending on the type of the replication design and model.
There are two types of subscriptions: push subscriptions and pull subscriptions
- Push subscription: Distributor directly updates the data in the Subscriber database
- Pull subscription: the Subscriber is scheduled to check at the Distributor regularly if any new changes are available, and then updates the data in the subscription database itself.