You can segregate data at multiple sites to provide your own guarantee of data consistency. Note that the absence of guaranteed data transactional consistency does not necessarily imply data inconsistency. If you can design your application so each participating site works with data that is strictly segregated, or partitioned from other sites, you can maintain data consistency. For example, design your order entry system so a given sales representative has a unique territory, so that orders will never conflict.
Partitioning adds a crucial third dimension to consider in designing and deploying distributed applications. While some of the Microsoft® SQL Server™ replication technologies allow for detection and resolution of conflicts, partitioning allows for avoidance of conflicts. It is always better to avoid conflicts before the update than to resolve them afterward. Therefore, always design your application to minimize conflicts regardless of the type of replication you use. Conflict resolution always results in some sites’ work being overwritten or rolled back, and the loss of guaranteed data consistency. A high number of conflicts requires substantial processing to resolve, is more difficult to administer, and can result in data states that are entirely unpredictable and not auditable.
A solution like transactional replication with the Immediate-updating Subscribers option relies on an underlying two-phase commit protocol (2PC) for all data modification to avoid conflicts that would compromise guaranteed transactional consistency. 2PC, of course, carries a cost. Any data modification performed under 2PC is going to be slower than one performed without the commit protocol. However, with specific knowledge of your application and data, you can design so that multiple sites will not update the same data. Hence, conflicts will not occur. By doing so, you can develop distributed applications, even disconnected ones, which maintain transactional consistency. SQL Server provides constraints and triggers with special capabilities for replication that can enforce your partition schemes.
A company sells tickets to local entertainment and sporting events. Tickets are sold at two locations, as well as by telephone. The database of seats is partitioned so that each ticket sale location is assigned a unique block of seats to sell. Each location makes updates to the database on their server, which subscribes to the data published at each of the two locations. However, even though each location sees all sales, it doesn’t update the seat inventory at the other location.
Multiple branch offices work mostly autonomously. Each has its own set of customers, and each can change only the information of its particular customers, though all branches can access all read data. Recognizing this, the database for each branch can be defined with constraints so that only data modifications for one of the “correct” customers can be made. The data remains transactionally consistent without the use of 2PC because each data element has one and only one site that changes it.