The second mode of replication is called transactional replication. Transactional replication monitors changes to the publishing server at the transaction level: insert, update or delete operations. Changes made to the Publisher flow continuously or at scheduled intervals to one or more subscribing servers. Changes are propagated in near real time; typically, with a latency of seconds. With transactional replication, changes must be made at the publishing site to avoid conflicts and guaranteeing transactional consistency.
Only committed transactions are sent to subscribing servers, and in the guaranteed order in which they were committed at the Publisher. This guarantees loose transactional consistency: ultimately all the subscribing sites will achieve the same values as those at the Publisher. The data at any participating site will be the same as it would be had all operations been performed at a single site.
If Subscribers need near-real-time propagation of data changes, they will need a network connection to the Publisher. Transactional replication in a well-networked environment can provide low latency to Subscribers. Push Subscribers would often receive changes within 5 or 10 seconds of when they occurred at the Publisher, provided the network link remains available.
Because transactional replication relies on a given data element having only a single Publisher, it is most commonly used in application scenarios that allow for logical partitioning of data and data ownership. A branch system with centralized reporting (corporate rollup) is an appropriate use of transactional replication. In this scenario, data ownership is maintained at the branch level and is published to a centralized server for read-only reporting. Transactional replication is also an appropriate solution for periodic downloads of read-only data in either a well-connected or disconnected scenario. For example, every night from a hotel room, a mobile salesperson pulls down the incremental changes to a price list, which is modified only at the corporate office.
In their simplest form, both snapshot and transactional replication are based on a model of one-way replication, in which data is modified only at the Publisher and flows downstream to a Subscriber. However, some applications require the ability to update data at subscribing servers and have those changes flow upstream. The Immediate Updating Subscribers option, available with either snapshot or transactional replication, allows data to be updated at subscribing sites.
This option is set when the article is created and allows a Subscriber to update the copy of its local data, as long as that update can be immediately reflected to the Publisher with the two-phase commit (2PC) protocol. If the update can be performed successfully between the Subscriber and the Publisher, the Publisher propagates those changes to all other Subscribers during the next distribution. Because the Subscriber making the update already has the data changes reflected locally, the user can continue working with the updated data secure in the guarantee that the Publisher data also reflects the change.
The Immediate Updating Subscribers option is most appropriate for use with well-connected and reliable networks where application contention for data is relatively low. For example, a distributed ticketing system maintains local replicas of the ticketing database so that available seating can be accessed quickly. However, a seat can be sold only once, so it is critical that the local transaction (for example, the ticket sale) is updated immediately and committed to the central server, the Publisher. The transaction can then be replicated to all other local ticket offices in the next distribution, and any attempt to sell the same seat will be prohibited.
When a publication is enabled to support the Immediate Updating Subscribers option, a Subscriber site can modify replicated data if the transaction can be performed using 2PC with the Publisher. This approach provides latent guaranteed consistency to other Subscribers without requiring that updates be made only at the publishing site. The 2PC transaction with the Publisher is done automatically so that an application can be written as though it is updating just one site. This approach does not have the availability constraint of doing full 2PC to all participating sites because only the Publisher must be available. After the change is made at the Publisher, it is published to all other Subscribers to the publication, thereby maintaining latent guaranteed consistency.
With this option, applications are not required to be written to perform data modification operations at the Publisher and read operations at the Subscriber. The application works with data at one site, and the 2PC transaction is performed automatically. The application should be equipped to deal with a failure in the transaction, just as it would in a nonreplication environment. If the transaction is successful, the Subscriber can work with the changed values immediately, as the update has been accepted at the Publisher without conflict and will eventually be replicated to every subscription of the publication. A Subscriber performing updates does not have full autonomy because the Publisher must be available at the time of the update. Nevertheless, autonomy is much higher than it would be with the full 2PC case in which every site must be available for any site to perform changes.