Manage Learn to apply best practices and optimize your operations.

Transactional replication

Excerpted from the McGraw-Hill/Osborne Media book SQL Server 2000 Administration, this tip explains how transactional replication works by sending changes to the subscriber as they happen.

The following tip is excerpted from the McGraw-Hill/Osborne Media book SQL Server 2000 Administration, Chapter 10, 'Replication', written by Mark A. Linsenbardt and Shane Stigler. For a summary and link to the complete chapter, click here.

In what could be considered the opposite of snapshot replication, transactional replication works by sending changes to the subscriber as they happen. As you know, SQL Server processes all actions within the database using Transact-SQL statements. Each completed statement is called a transaction. In transactional replication, each committed transaction is replicated to the subscriber as it occurs. You can control the replication process so that it will accumulate transactions and send them at timed intervals, or transmit all changes as they occur. You use this type of replication in environments having a lower degree of latency and higher bandwidth connections. Transactional replication requires a continuous and reliable connection, because the Transaction Log will grow quickly if the server is unable to connect for replication and might become unmanageable.

Transactional replication begins with a snapshot that sets up the initial copy. That copy is then later updated by the copied transactions. You can choose how often to update the snapshot, or choose not to update the snapshot after the first copy. Once the initial snapshot has been copied, transactional replication uses the Log Reader agent to read the Transaction Log of the published database and stores new transactions in the DISTRIBUTION Database. The Distribution agent then transfers the transactions from the publisher to the subscriber.

Transactional replication with updating subscribers

An offshoot of standard transactional replication, this method of replication basically works the same way, but adds to subscribers the ability to update data. When a subscriber makes a change to data locally, SQL Server uses the Microsoft Distributed Transaction Coordinator (MSDTC), a component included with SQL Server 2000, to execute the same transaction on the publisher. This process allows for replication scenarios in which the published data is considered read-only most of the time, but can be changed at the subscriber on occasion if needed. Transactional replication with updating subscribers requires a permanent and reliable connection of medium to high bandwidth.

From the field: Though the distributed transactions required by updating subscribers work best in high bandwidth, reliable network connections, you can adjust the replication agent profiles to tolerate a higher level of packet loss or general network error Don't think that you cannot use this method if the first installations fail.

Click for the next tip in this book excerpt: Merge replication

Dig Deeper on SQL Server Replication