Updatable Subscriptions, Create Publication Wizard
With snapshot replication or transactional replication, replicated data is by read-only by default; 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. |
The following options are available on the Updatable Subscriptions page of the Create Publication Wizard.
Options
Immediate updating
Enables immediate updating. Immediate updating allows data modifications to replicate 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
Enables 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 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.
Select both Immediate updating and Queued updating to enable 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 updates 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.
After you enable immediate updating and/or queued updating when creating the publication, you will also need to specify the updatable subscription option when creating subscriptions to the publication.
Note You cannot enable or disable immediate updating or queued updating after the publication is created by modifying Publication Properties. Updatable subscriptions and transforming published data are mutually exclusive options. If you choose to enable updatable subscriptions, you cannot also enable transforming published data (another option available with snapshot and transactional publications).