What is Replication?

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

What are the Components in Replications? 

Publisher: A publisher is any database unit that makes data available to other sources via replication. The publisher can have multiple publications which defines the data to replicate.

Subscriber: A subscriber is any database unit that receives the replicated data. It can receive data from multiple publishers or publications. Data can be republished to the other subscribers.

Article: Article is the basic unit of publication. It shows what is published. An article can simply be a table or a stored procedure. When tables are used as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.

Publication: It is a collection of one or more articles from one database.

Distributor: A distributor is any database unit that acts as a store for the data to be replicated. Each publisher is associated with a single database called as distribution database. Replication status data is stored in distribution database. A distributor can be local or remote.

Pull Subscription: In a pull subscription, changes to a subscriber cannot be publicized without any request from subscriber. This allows the user at the Subscriber to determine when the data changes are synchronized.

Push Subscription: In a push subscription, changes to a subscriber can be publicized without any request from subscriber. The changes can be pushed either periodically or on demand.

Snapshot Agent: A snapshot agent is used in snapshot replication. Snapshots can be created as soon as the subscription is created. The snapshot agent prepares snapshot files that contain details of the published data. The snapshot also assists in establishing connection from the Distributor to the Publisher.

Log Reader Agent: The replication Log Reader Agent is an executable that is used for monitoring transaction logs of each database. It c copies the transactions marked for replication from the transaction log into the distribution database.

Distribution Agent: The distribution Agent is an executable that is used to move the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

Merge Agent: The replication merge agent is an executable that is used to apply the database snapshot to the subscriber. After the initial snapshot, any other changes if made are also merged.

Queue Reader Agent: The Replication Queue Reader Agent is an executable that is used to read messages stored in a Microsoft SQL Server queue or a Microsoft Message Queue and then applies those messages to the Publisher.

What are the terms used in Replication?

Publisher is the database that transmits its data to another database.
 
Subscriber is the database that receives data from another database.
 
Distributor manages the flow of data during data replication.
 
Push subscription is the subscription when data is pushed from publisher database to subscribing database.
 
Pull subscription is subscription when the subscribing database pulls the data from distribution database.
 
Distribution database is the system database that stores snapshot jobs to be distributed to subscribers.

Describe in brief working of Replication?

1. At first data and object is synchronized between publisher and subscribers.
2. The snapshot is created on the publisher and transmitted to subscribers.
3. All the subsequent changes on the publisher are stored in distribution database.
4. Subscriber receives the data either using push or pull mechanism as configured from distribution database