12 January 2011

Step by Step setting Transactional Replication in SQL Server 2008 R2

There are 3 steps involved for Configuring the Transactional Replication:

1.Configuring the Distribution Database.
A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created. 
2.Creating the publisher.
The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.
3.Creating the subscriber.
The Subscribers the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

How it works

Transactional replication is implemented by the 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 moves the initial snapshot jobs and the transactions held in the distribution database tables to Subscribers.


Configuring the Distribution Database











Creating the Publisher
















Creating the Subscriber

















Special thanks to Satnam Singh and Microsoft

4 comments:

Anonymous said...

Thank you, this helped me set up replication to a server off-site.

John Jefferson Rosales said...

sir i follow your instruction but i got this error

The replication agent has not logged a progress message in 10 minutes. this might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connection to the subscriber. Publisher and distributor are still active

Edith frm DMR said...

thank you very much ,it helps a lot.

Anonymous said...

This topic is very helpful for transactional replication.Kindly sir upload merge and snapshot replication.
Thanks
Regards,
Farhat Ullah