Transactional Replication

With transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Transactional replication is helpful when:

• You want incremental changes to be propagated to Subscribers as they occur.
  
  
• You need transactions to adhere to ACID properties.
  
  
• Subscribers are reliably and/or frequently connected to the Publisher.

Transactional replication uses the transaction log to capture incremental changes that were made to data in a published table. Microsoft® SQL Server™ 2000 monitors INSERT, UPDATE, and DELETE statements, or other modifications made to the data, and stores those changes in the distribution database, which acts as a reliable queue. Changes are then propagated to Subscribers and applied in the same order as they occurred.

With transactional replication, incremental changes made at the Publisher flow according to the Distribution Agent schedule. This schedule can be set to continuously for minimal latency, or set at scheduled intervals to Subscribers. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. This guarantees ACID properties of transactions will be maintained. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.

If Subscribers need to receive data changes in near real-time, they need a network connection to the Publisher. Transactional replication can provide very low latency to Subscribers. Subscribers receiving data using a push subscription usually receive changes from the Publisher within one minute or sooner, provided that the network link and adequate processing resources are available (latency of a few seconds can often be achieved).

However, Subscribers can also pull changes down as needed. A traveling sales representative can be a Subscriber and request incremental changes to a price list, which is only modified at the corporate office, once each evening. The use of transactional replication for disconnected users can be very effective for read-only data.