A Basic Guide on Transaction Replication in SQL Server

Transactional replication begins with the help of publication database objects and data snapshots. The subsequent data changes and schema modifications created at Publisher are deployed to the Subscribers in real time. The order of the data changes in the same way it has been applied to the subscriber and exists in the same transaction boundaries. Transactional replication is used in server-to-server environment, and proves to be useful in the following cases:

• Need of Incremental Changes to the Subscribers: Low latency in the amount of time required for changes done at the Publisher, and the changes arriving at the Subscriber.
• If Access to the Intermediate Data is Required: If the row changes four times, then it allows the application to respond to each change (like firing trigger) made to the row data.

How Transactional Replication Works?

Transaction replication is carried out by the SQL server snapshot agent, distribution agent, and log reader agent. The SQL agents make, snapshot files containing schema and data from published; tables and database objects. It then stores files in the snapshot and record synchronization folder of the distributor.

The transaction log of each database for transactional replication is monitored by the Log Reader Agent. The transaction marked for replication is copied from the logs into distribution database. The initial snapshot files are copies of the Distribution agent from the snapshot folder.

The changes made to the data if made first at the Publisher, then update conflicts are avoided. Eventually, all Subscribers will get the same values at the Publisher. If immediate or queued updating options are used with transactional replication, users can make updates at the Subscriber.

Components of Transactional Replication

Initial Dataset:

It is created by snapshot agent and applied by distribution agent. The Subscriber can receive every incremental change, and it must have tables with the same schema and data, as the tables at publisher. The initial dataset can be applied through a backup or SQL Server Integration Services. Only those Subscribers are affected by snapshot that applied waiting for initial snapshots and other Subscribers remain unaffected to the publication.

Concurrent Snapshot Processing:

All tables are locked by the shared locks, which are placed by Snapshot replication. This lock can prevent updates from being made on the tables. During the snapshot generation, concurrent snapshot processing doesn’t hold shared locks. This allows users not surface any interruptions, while replication created snapshot files initially.

Snapshot Agent

The snapshot Agent created in the initial snapshot in transactional replication procedure is the same as used in the snapshot replication. After the snapshot file has been created, the user can view them in snapshot folder by using Microsoft Windows Explorer.

Modifying Data and Log Data Agent:

Log data agent continuously runs on distributor and can also run according to the schedule which the user establishes. When executing, Log Agent first reads the database log used for transaction tracking and recovery, during regular database Engine operations and identifies INSERT, DELETE, and UPDATE statements along with other modifications made to the data; marked for replication. After that the agent copies those transaction batches to the distribution database at Distributor. Log Agent uses the sp_replcmds internal stored procedure to achieve the next command set marked for replication. The distribution database then becomes the store-and-forward queue from where the changes are sent to the Subscribers.

Distribution Agent

For pushing subscriptions and pulling up subscriptions, Distribution Agent runs to the Distributor. This agent moves transactions from the distribution database to the Subscriber. The Agent then checks to match with Publisher and Subscriber, when a subscription is marked for validation.

Conclusion:

All of the above described components help transactional replication to proceed properly. And it is used in server–to-server environment. We have learnt that transactional replication is carried out by snapshot agent, distribution agent, and the log reader agent, etc. Sometimes the delay of data flow between these three components(Publisher, Distributor & Subscriber) may cause the bad performance of transaction replication. So, to manage the data flow between these three components & fixing SQL server transactional replication performance issues is an important task for a DBA.