HADR - Replication

Overview

- Replication is a mechanism to create online, standby copies of database objects.  We can create multiple secondary copies.   It runs by SQL Agent Jobs

- There are three servers >> Publisher / Subscriber / Distributor

- To Monitor >> Replication >> Launch Replication Monitor

 ====================================================

Terminology:

ARTICLES	These are database objects that participate in replication.   Ex: table articles, view article
PUBLICATION	A group of related articles from a given source database.
PUBLISHER	Server that provides the source database for replication
PUBLICATION DATABASE	Database involved in the replication (source)
SUBSCRIBER	Server that receives the above published data (dest)
SUBSCRIPTION DATABASE	Database in the subscriber server used to store the articles being replicated
DISTRIBUTOR	Server used to monitor & control the replication of data between publisher & subscriber
DISTRIBUTION DATABASE	Database used to store replication history.

1         Replication Agents

- Agents are used  to capture more information about error messages and procedure calls to a text file. We can mention the path of the agent output file

Agent	Using Replication Methods
Snapshot Agent	Snapshot Replication  Transactional Replication Merge Replication
Distribution Agent	Snapshot Replication  Transactional Replication Peer - Peer Replication
Log Reader Agent	Transactional Replication Peer - Peer Replication
Merge Agent	Merge Replication

- Snapshot Agent / Distribution Agent / Log Reader Agent /Merge Agent

Types of Replication:

Snapshot- 

- Publisher (Source) sends data to Subscriber (Destination) using Distributor Server. Whenever new publication is generated at source and sent to destination then old existing Publication data are overwritten.

- Agents Involved: Snapshot Agent & Distribution Agent

----------------------------------------------------------------------------------
Transactional:-

 - Publisher (Source) sends data to Subscriber (Destination) using Distributor Server.  PK mandatory

- Whenever new publication is generated at source and send to destination then old existing publication data are NOT overwritten. Instead, only the incremental changes are propogated.

- Agents Involved: Snapshot Agent, Log Reader Agent & Distribution Agent

----------------------------------------------------------------------------------
Merge

- Publisher and Subscriber both updatable and both incremental changes are updated to others.

- Agents Involved: Snapshot Agent, Merge Agent

----------------------------------------------------------------------------------
Peer - Peer 

- Every server is to be configured as a Publisher, Subscriber and a Distributor.  Everu server can be updatable and incremental changes probacated to all

- First Backup and Restore, Distribution Agent, Log Reader Agent

 ----------------------------------------------------------------------------------

Configuration	1.       Step 1 - Configure Distribution 2.       Configure Publisher 3.       Configure Subsciber
Step 1	-    Start all three servers (Distributor, Publisher, Subscribers) -    Recommended practice is Distributor and Publisher is in the same server
Configure Distributor	-    Distributor database is to store the replication (snapshot, merge and peer to peer) history information
Configure Publisher	- Distributor and Publisher may be in same server - -    SSMS >> Replication >> New >> Publication
Add one or more  Subscription	-    SSMS >> New >> Subscription

Reference:

- Step by Step /  How to enable output logging for a replication agent? / Replication Tables / xxx

Poovdba