Step by Step Configuring Transactional Replication in SQL Server 2008 R2
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
