Transactional Replication with Updatable Subscriptions

This is similar to the transactional replication with the additional capability for the subscribers to be able to update the published articles. This way there is a gain in the autonomy as the subscribers can update the existing data, the latency is maintained at the same level.

 In most of the scenarios this considered to be the best solution. This setup uses the two-phase commit process to keep the Publisher/Subscriber in sync. The two phase commit process uses the MSDTC so that the changes are made simultaneously at the publisher-subscribers end.

So before setting up this kind of replication it’s important to have the MSTDC up and running.

To activate the MSDTC, In Windows Explorer -> Start -> Control Panel -> Administrative Tools -> Component Services.

In Component Services, open Services. In the services list, you will find the item on the DTC. Activate it! Once the DTC is activated your object explorer will show DTC as green and runnable as shown in the below screen shot.

This kind of setup is extremely useful for something like railways reservation system with multiple reservation centers. Bookings are replicated transactionally all across to all the subscribers (centers) and also at the publishers (primary server) end. The synchronization takes place all across on a transaction and not on the whole of the article.

But what if a transaction happens on the same record at the same time at the subscriber1 and subscriber2 and publisher, in this case there is a conflict resolution that is specified at the time of setting up of the replication. 

Example the setup makes the publisher transaction winner in case of any conflicts. So the Publisher’s change will be the final one and will be replicated across all the subscribers in case of conflicts.

For Transactional Replication with Updatadable Subscriptions Video