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 articles (publications – SQL Server objects) to which one or more subscribers (destination instance/s) can subscribe to get updates.
Replication can be used for disaster recovery, scalability, reporting, data warehousing etc.
Publisher:- 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 server which gets data from the publishers. A subscriber can subscribe to one or more publications.
Distributor:- 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 set of articles or SQL Server objects which are published by a publisher and to which a subscriber subscribes too.
Articles:- 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 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 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 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 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 applies the messages in SQL Server queue or Microsoft Message Queue to the publisher when queuing is specified.
Publication 4 types
Standard transactional publication
Appropriate for topologies in which all data at the Subscriber is read-only
-Each location has identical data, with one Publisher and one Subscriber. -It is possible to update rows at the Subscriber -This topology is best suited for server environments requiring high availability and read scalability.
Peer-to-peer topology
- Each location has identical data and acts as both a Publisher and Subscriber. - The same row can be changed only at one location at a time. - Supports conflict detection - This topology is best suited for server environments requiring high availability and read scalability.
Bidirectional transactional replication
Bidirectional replication is similar to Peer-to-Peer replication, however, it does not provide conflict resolution. Additionally, bidirectional replication is limited to 2 servers.
No comments:
Post a Comment