SQL Server Replication is a way to automate data distribution from a source to one or more destination database. SQL Server replication follows the Magazine Publishing terminology. A publisher (source instance) publishes article/s (publications – SQL Server objects) to which one or more subscribers (destination instance/s) can subscribe to get updates.
SQL Server Replication can be used for disaster recovery, scalability, reporting, data warehousing etc.
A reference to SQL Server vocabulary is given below
Publisher
A Publisher is a server which makes data available for other servers i.e. subscribers. The articles (SQL Server Objects) which are to be replicated are defined at the publisher.
Subscriber
A Subscriber is a server which gets data from the publishers. A subscriber can subscribe to one or more publications.
Distributor
A Distributor is a server which distributes data from publishers to subscriber. It contains the Distribution database. The distribution database keeps track of the changes to the publication which are to be replicated to subscribers. A distributor can be a separate server or publisher itself.
Publication
A Publication is a set of articles or SQL Server objects which are published by a publisher and to which a subscriber subscribes too.
Articles
An Article is a SQL Server object such as Table, view, function, stored procedure etc. which is replicated.
Replication Agents
Replication is performed by the replication agents. These are external applications configured as SQL Server Agent Job. These are listed below
– The Snapshot Agent
The Snapshot Agent is used to synchronize or initialize initial schema in Merge/Transactional replication and to replicate data in Snapshot replication. This is done by snapshot.exe.
– The Distributor Agent
It takes the snapshot/T-log data from the snapshot/log reader agents and makes it available to the subscribers. It is done by Distrib.exe
– The Log Reader Agent
It extracts the appropriate transactions from the publisher’s log which are to be replicated. It then sends these logs to distributor (Distribution database) in proper sequence. It is done by LogRead.exe.
– Merge Agent
The Merge Agent applies the initial snapshot to the subscribers and also applies the incremental data changes at the publisher to subscribers. It is done by Replmerg.exe.
– Queue Reader Agent
It applies the messages in SQL Server queue or Microsoft Message Queue to the publisher when queuing is specified.
Further reading
http://msdn.microsoft.com/en-us/library/ms151198.aspx
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
