Immediate Updating

SQL Replication


Immediate Updating

Immediate updating allows snapshot replication and transactional replication Subscribers to update the replicated data at the Subscriber and send those changes back to the Publisher and to other Subscribers. Immediate updating benefits applications in which snapshot or transactional publications are preferred but occasional updates need to be made at the Subscriber. If using immediate updating, the Publisher and Subscribers must be available and connected.

The immediate-updating option:

  • Ensures that there are no conflicts. A Subscriber can perform inserts, updates, and deletes on replicated data only if it can perform a two-phase commit protocol (2PC) transaction with the Publisher. The Publisher must accept every update before it is made at the Subscriber. Conflicts do not occur because they are detected before a transaction is committed.

  • Initiates two-phase commit (2PC) automatically.

  • Replicates the committed update down to all other Subscribers through the standard snapshot replication or transactional replication mechanism.

  • Lets the Subscriber continue working without waiting for the successful update to propagate to other Subscribers.

  • Forestalls the requirement for the updating Subscriber to have a distribution database or log reader and get involved in the administrative issues of replication publishing.

  • Has fewer failure points with every site than with full 2PC involving every Subscriber, and it is also more scalable.

  • Because there are no conflicts, there is no loss of ACID properties.

  • Registers a uniqueidentifier column in the publishing table named MSrepl_tran_version. This column is used for tracking changes to replicated data and to perform conflict detection at the Publisher. Adding this uniqueidentifier column will cause INSERT statements without column lists to fail and increase the size of the publishing table.

If you were using the immediate updating option with Microsoft® SQL Server™ version 7.0 and are upgrading to SQL Server 2000, there are additional upgrade requirements. For more information, see Replication and Upgrading.