Multiuser Access

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) permits single or multiple connections to a SQL Server Compact Edition database.

Enabling multiple connections to access a SQL Server Compact Edition database offers developers a simple model for development. Because the SQL Server Compact Edition Database Engine can handle simultaneous requests from multiple applications, or multiple connection requests from a single application, developers do not have to schedule database access.

If SQL Server Compact Edition permitted only a single connection to access a database, one transaction would have to be completed before another could start, restricting concurrency. In SQL Server Compact Edition, you can run multiple simultaneous applications that are accessing or modifying different sets of data. If there is almost no data contention between applications for a particular resource in the database, permitting multiple applications to access the database improves concurrency.

Access to SQL Server Compact Edition databases can occur in the following ways:

• Single connection—a single application that requires a single connection to a SQL Server Compact Edition database.

• Multiple connections—a single application might have to make multiple connections to a single database to support various operations, such as accessing data during synchronization.

  Note

  When an application is using multiple threads to access or modify data through replication or Remote Data Access (RDA), the same Internet logon credentials should be used.

• Multiple applications—multiple applications on a device can access a single database at the same time. The developer is not required to manage exclusive connections. Users can use database tools, such as SQL Server Compact Edition Query Analyzer, while another application is connected to the database.

If your application requires a single connection and you do not want other connections or applications to access the database at the same time, you can open the database exclusively so that multiuser access is not permitted. For more information about exclusive connections, see How to: Set the File Mode When Opening a Database (Programmatically) and How to: Set the File Mode when Opening a Database with OLE DB (Programmatically).

Locking is used as the concurrency control mechanism. Locking lets all transactions run in complete isolation from one another, although more than one transaction can run at any time. For more information, see Locking (SQL Server Compact Edition).

The number of simultaneous connections to a database is limited by the number and amount of resources available, such as the fixed-size session table, which limits the number of simultaneous connections to a maximum of 256 connections.

No roles or privileges are supported. Applications and users can connect to the database by using the same authentication and authorization procedures as in earlier versions of SQL Server Compact Edition. For more information about security procedures, see Configuring and Securing the Server Environment.

Multiuser access can affect data being synchronized between SQL Server and SQL Server Compact Edition. For more information, see Multiuser Access and RDAand Multiuser Access and Synchronization.