Data replication is a complex technology, and Microsoft® SQL Server™ replication recognizes that there cannot be a single solution that works for all applications. SQL Server provides a variety of replication technologies that can be tailored to your application’s specific requirements. Each technology produces different benefits and restrictions, depending on your requirements, across three important dimensions:
Transactional consistency in regard to distributed operations like replication adds additional wrinkles to the typical database issues of ACID properties (atomicity, consistency, isolation, and durability) and transaction isolation levels. In the context of replication, transactional consistency means that, after replication, data at any participating site will be the same data that would have resulted had all transactions been performed at only a single site.
Site autonomy refers to the effect of one site’s operations on another. There is complete site autonomy if one site’s ability to do its usual work is independent of its connectivity to another site, and independent of the state of operations at that site. For example, the two-phase commit protocol (2PC) makes every data change dependent on every other participating site being able to accept the transaction successfully and immediately. If one site is unavailable, no work proceeds. At the other end of the spectrum, merge replication sites work independently and can be completely disconnected from all other sites.
You can segregate data at multiple sites to provide your own guarantee of transactional consistency. The absence of guaranteed transactional consistency does not necessarily imply transactional 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 transactional consistency. For example, design your order entry system so a given sales representative has a unique territory, so that orders will never conflict.
| Transactional Consistency | Partitioning Data to Avoid Conflicts |
| Site Autonomy |