SQL Server - Transactional Replication

As mentioned in MSDN BOL :

Transactional replication typically starts with a snapshot of the publication database objects and data.

As soon as the initial snapshot is taken, subsequent data changes made at the Publisher are usually delivered to the Subscriber as they occur (in near real time).

How Transactional Replication Works

Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.

The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database

The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
Incremental changes made at the Publisher flow to Subscribers according to the schedule
of the Distribution Agent.

I will show step by step tutorial in configuring transaction replication. In this example,
I have configured distributor, publisher and subscriber in three different server.

We can also have distributor & publisher in one server and subscriber in another server.

Once transactional replication is configured, Insert,Update,Delete, Alter statements on published articles at the publisher database will reflect at the subscriber database immediately or at scheduled interval.

But when a new table needs to be published, below settings needs to be done:

In Server 2 -> Where Publisher is configured -> Object Explorer -> Local Publications -> right-click on Publisher name -> Properties -> Articles ->  include the objects to be published as shown below :



Now, In Server 2 -> Where Publisher is configured -> Object Explorer -> Local Publications -> right-click on Publisher name -> if we click on View Synchronization Status 



We will notice "The initial snapshot for article is not yet available" as shown in above image.

In Server 2 -> Where Publisher is configured -> Object Explorer -> Local Publications -> right-click on Publisher name -> Reinitialize Subscription(s) - All Subscriptions

( or )

If there is only one subscriber, In Server 2 -> Where Publisher is configured -> Object Explorer -> Local Publications -> go to the Subscriber under Publisher name -> right-click -> Reinitialize a Subscription


In Server 2 -> Where Publisher is configured -> Object Explorer -> Local Publications -> right-click on Publisher name -> View Snapshot Agent Status -> Start

See Also:

 
 
Reference:
 
 
 

No comments: