Multiuser Access and RDA

Multiuser access in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) is available at all times, including when you use remote data access (RDA). A pull or push action can be in progress while another user accesses the same SQL Server Compact Edition database.

Because a pull operation requires that the specific local table not be present at the time of the pull, and the table is created during the process of pulling the data from the server, the table being pulled cannot be accessed until the pull operation is complete. When data is being pushed to the server, if data changes occur after the push has started, those changes will be sent to the server on the next push operation. If the data from the server must be refreshed after a particular push to the server, the application does not allow additional changes to the table until the push and pull operations are both complete. This ensures that the changes will not be lost when the table is dropped.

Allowing multiple connections to access a SQL Server Compact Edition database offers developers a simpler model for development. Because the SQL Server Compact Edition Database Engine handles requests from multiple applications, or multiple connection requests from a single application, developers do not have to schedule database access. When multiple connections are required, the SQL Server Compact Edition Database Engine locks data as needed for specific operations.

Changing Primary Key During Synchronization

For an RDA tracked table, the updates, inserts, and deletes are tracked based on the primary key. Because of the ability to have multiuser access to the primary key columns, it is important that the primary key column values not be changed during synchronizations.

Warning   Although changing a value in the primary key columns is rare, if it occurs during synchronization of a table in which the primary key has been changed, it could cause error and loss of data.

Example

In Table A, a primary key value is changed from 1 to 2. Value 1 has been stored in a tracking column used by RDA, so that when a push occurs, SQL Server Compact Edition knows what value to use for the search criteria when updating the data to the new value.

The application initiates the push operation and the new value of 2 is sent in the upload message to the SQL Server database with the search value of 1. However, if new data value is changed during the synchronization, then the tracking column will not change its search value from 1 to 2 because the push is not complete. When the push succeeds, the next push of this row will always fail. The search criteria of 1 is no longer valid because the primary key value was changed from 1 to 2 at the server.

For more information about multiuser access, see Multiuser Access. For more information about locking, see Locking (SQL Server Compact Edition).