Multiuser Access and Synchronization
Because Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) supports multiuser access, a user can continue to work on the database while the database is replicating. This can cause conflict with changes coming from the Publisher, referred to as local data conflicts, that you must consider when you develop applications that use SQL Server Compact Edition.
Effects of Multiuser Access
When you design an application that uses SQL Server Compact Edition, you must consider the effects of multiuser access on the database. The following table outlines some of the common features built into SQL Server Compact Edition, and the issues associated with each feature.
| Feature | Issue |
|---|---|
Locking |
During synchronization, changes from the Publisher might not be applied to the Subscriber database because of data locks. If data changes are being made on the Subscriber and there are long-lasting locks on the data, synchronization might fail. To fully prevent this, add logic to your application that prevents a user from changing data during synchronization. |
Validation |
If validation is being used, and the number of rows in the SQL Server Compact Edition database changes during synchronization, the validation will fail. To fully prevent this, add logic to your application that prevents a user from changing data during a synchronization with validation. |
Reinitialization |
During reinitialization of a subscription, the replication layer deletes and re-creates all replicated user and system tables. As with SQL Server subscriptions, any changes made after synchronization starts are lost when the reinitialization occurs. To prevent this data loss, add logic to your application that prevents a user from changing data during reinitialization. |
Schema changes |
All DDL operations (schema changes) must have exclusive access to the table. A schema change will fail if the table is being used by another process. |
Changes during synchronization |
If a data change occurs during synchronization, that change will be sent during the next synchronization. If a synchronization session causes a local conflict, the row is resolved as row-level, even if the article is column-level tracked. |
Conflict Detection and Resolution
When you work in a multiuser environment, changes can occur during synchronization that cause conflicts. SQL Server Compact Edition detects client-side conflicts, but does not handle conflict resolution. Instead, the conflict information is passed to the Publisher for resolution during the next synchronization.
Most conflicts are resolved at the Publisher during the next synchronization. However, if a Referential Integrity (R/I) conflict occurs, the Publisher will request an automatic resynchronization on the device. If this behavior occurs, no more than two resynchronizations will occur.
For more information about conflict detection and resolution, see Replication Conflict Detection and Resolution.
Using the SubscriberConflicts Property
Changes made to the local database during synchronization could cause a local conflict. If a row from the Publisher cannot be applied at the Subscriber, it is considered a Subscriber conflict, and the SubscriberConflicts property is set. With SQL Server Subscribers, conflicts are resolved at the Subscriber. However, SQL Server Compact Edition does not have a reconciler. All conflicts must be resolved at the Publisher. When you develop an application, you can design your application to examine the SubscriberConflicts property after each synchronization. If it is set to a nonzero value, the data must be resynchronized so that the Publisher can resolve the conflicts.
See Also
Concepts
Synchronizing Data (SQL Server Compact Edition)
Synchronous Data Synchronization
Asynchronous Data Synchronization
Replication Conflict Detection and Resolution