Database Replication


70-462 SQL Server 2012 Flashcards on Database Replication, created by Michael Mahfouz on 04/01/2016.
Michael Mahfouz
Flashcards by Michael Mahfouz, updated more than 1 year ago
Michael Mahfouz
Created by Michael Mahfouz over 8 years ago

Resource summary

Question Answer
Snapshot Replication Takes a snapshot of the published objects and applies it to a subscriber. Completely overwrites the data at the subscriber each time a snapshot is applied. Best suited for a fairly static data or if data can go out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected
Transactional Replication Replicates each transaction for the article being published. A snapshot of the publisher or a backup is taken and applied to the subscriber to synchronise the data. When a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency.
Merge Replication Changes are merged to keep data consistency and a uniform set of data. An initial synchronisation is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated. The merge agent has the capability of resolving conflicts that occur during data synchronisation
Peer-to-Peer Replication Built on Transactional Replication. It maintains transactional replication between servers and allows publishers and subscribers to send data to each other.
Push Subscription Pushes data from publisher to the subscriber. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.
Pull Subscription Requests changes from the Publisher. This allows the subscriber to pull data as needed. This is useful for disconnected machines such as notebook computers that are not always connected and when they connect can pull the data.
Snapshot Agent Used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files and records information about synchronisation in the distribution database. The Snapshot Agent runs at the Distributor
Log Reader Agent Used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher (the Distributor can be on the same computer as the Publisher)
Distribution Agent Used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions
Merge Agent Used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions
Queue Reader Agent Used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.
Replication Types vs. Agents
Replication Monitor A tool available that enables replication monitoring. Can be used to configure alerts based on performance benchmarks. Also provides performance-quality information for transactional replication and merge replication.
Controlling Replication of Constraints, Columns and Triggers NOT FOR REPLICATION option can be enabled to carry out the following with the replication agent: - Foreign key constraints - Check constraints - Identity column values - Triggers
Show full summary Hide full summary


Data Analytics
7. Mirroring & Replication
Michael Mahfouz
Computing Hardware - CPU and Memory
SFDC App Builder 2
Parker Webb-Mitchell
Data Types
Jacob Sedore
Intake7 BIM L1
Stanley Chia
Software Processes
Nurul Aiman Abdu
Design Patterns
Erica Solum
CCNA Answers – CCNA Exam
Abdul Demir
Shannon Anderson-Rush