Updatable Subscriptions, Subscription Wizards
With snapshot replication or transactional replication, replicated data is by default read only; however, you have the ability to modify replicated data at the Subscriber by using updatable subscriptions. If you need to modify data at the Subscriber using snapshot or transactional replication, you can choose one of the following options depending on your requirements.
| Updatable Subscription | Requirements |
|---|---|
| Immediate Updating | Publisher and Subscriber must be connected to update data at the Subscriber. |
| Queued Updating | Publisher and Subscriber do not have to be connected to update data at the Subscriber. Updates can be made while offline. |
| Immediate Updating with Queued Updating as a Failover | Publisher and Subscriber are connected most of the time, but you may occasionally need to make updates offline. |
Options
None – changes are not replicated
Replicated data that is modified at the Subscriber will not be propagated to the Publisher or other Subscribers.
Immediate updating
Configures this subscription to use immediate updating. Immediate updating allows data modifications to replicated data at the Subscriber. Those changes are then sent back to the Publisher and to other Subscribers. Immediate updating ensures that there are no conflicts. Data modifications are propagated from the Subscriber to the Publisher using two-phase commit protocol (2PC).
Queued updating
Configures this subscription to use queued updating. Queued updating allows data modifications to replicated data at the Subscriber, without requiring an active network connection to the Publisher. When you create a publication with the queued updating option enabled and a INSERT, UPDATE, or DELETE statements are performed on replicated data at the Subscriber, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored. You can use either SQL Server queues (default) or Microsoft Message Queuing.
Immediate updating with queued updating as a standby in case of failure
Configures the subscription for immediate updating with queued updating as a failover. Immediate updating with queued updating as a failover can be used when you expect the Publisher and Subscribers to be connected, but you do not want to lose the ability to make data modifications at the Subscriber if a system failure results in the loss of network connectivity. Immediate updating with queued updating as a failover allows you to use immediate updating and switch to queued updating when needed.
Note You cannot configure immediate updating or queued updating after the subscription is created by modifying Push Subscription Properties or Pull Subscription Properties.