MS SQL Basics Tutorial: Planning for replication options

SQL Server 2000 provides more replication options than previous versions, including immediate updating of subscribers, queued updating, queued updating as an immediate update to hot backup, transfer of replicated data, filtered publication data, and more. The first three options indicate that the copied data can be modified at the Subscriber, and then the modifications are reflected in the appropriate way to the publisher. If these replication options are used in replication, making replication more difficult to configure and manage, it is necessary to have a clearer understanding and understanding of these replication options.

16.6.1 Immediate updating of subscribers

An immediate updating Subscriber refers to the immediate update option used at the time of replication. In general, both snapshot replication and transactional replication are one-way data replication, where data is replicated from the publisher's source database to the target database of subscribers. However, SQL Server enhances the functionality of this pattern by allowing subscribers to modify replicated data. The immediate updating of the Subscriber option allows you to modify replicated data either at the Publisher or at the Subscriber. Immediate updating refers to the immediate updating of data between subscribers and publishers who modify the replicated data, i.e. to immediately reflect the change of the subscriber to the Publisher. It also provides potential transactional consistency for other subscribers, that is, the modification of the subscriber immediately after being reflected to the publisher, allows the modification to be not immediately synchronized to other Subscribers. Set this option when you create a publication.

It should be emphasized that a two-phase submission agreement was used between the subscriber and the publisher who modified the data, thus requiring that the modification be submitted to the Subscriber only after the modification of the data has been successfully submitted by the Publisher. If the commit is successful, the Publisher will then distribute the change to the other subscribers when the Distribution Agent executes, if snapshot replication is done, or when the snapshot is refreshed.

As opposed to a full two-phase commit protocol, the immediate updating of the Subscriber option reduces the complexity of replication and the likelihood of a transaction committing failure. Because a full two-phase protocol requires a reliable network connection between all servers participating in the transaction, and modifications to the data are only successful committed in all participants, but when you set the immediate updating of the subscriber option, Since the two-phase commit agreement was used only between the subscriber and the publisher who modified the replicated data, so the integrity of the transaction remains well maintained, and the two-phase commit protocol is not used between publishers and other subscribers, eliminating the complexity of all participants having to keep data synchronized.