Following are the steps for configuring Transactional Replication on Server:
There are 3 steps involved for Configuring the Transactional Replication:
2.Creating the publisher.
3.Creating the subscriber.
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.
The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.
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.
Activity Task:
1. We need instance name to configure replication so connect to publisher and get instance name:
Choose database:
Connect distributor and get instance name
Configure distributor:
Configure Publisher:
Done
Check the row transfer and match from Publisher rows to Subscribers row:
Check blocking when snapshot capture at publisher:
Monitoring Replication:
