Partitioning Data to Avoid Conflicts

You can segregate data at multiple sites to provide your guarantee of transactional consistency. The absence of guaranteed transactional consistency does not imply guaranteed transactional inconsistency. If you can design your application so that each participating site works with data that is strictly segregated (or partitioned) from other sites, you can maintain transactional consistency. For example, to ensure that orders never will conflict, design your order entry system so that a given salesperson has a unique, known territory.

Partitioning adds a crucial third dimension to consider when designing and deploying distributed applications. While some of the SQL Server replication technologies allow for detection and resolution of conflicts, partitioning enables avoidance of conflicts. When possible, it is better to avoid conflicts before the update rather than to resolve them afterward. Conflict resolution always results in some site's work being overwritten or rolled back, and the loss of guaranteed transactional 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 are not auditable. As a practical matter, if you choose a distributed technology that does not guarantee transactional integrity, you should be certain that your application and deployment will not produce many conflicts. Conflicts should be the exception rather than the rule, and reconciliation can be used to resolve these exceptions.