Multiuser Access

  • Article

Microsoft SQL Server Compact permits single or multiple connections to a SQL Server Compact database.

Enabling multiple connections to access a SQL Server Compact database offers developers a simple model for development. Because the SQL Server Compact 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 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, 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 databases can occur in the following ways:

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

  • Multiple connections—a single application might have to make multiple connections to a single database to support various operations.

  • Multiple applications—multiple applications 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 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). Some 64-bit platform scenarios do not support simultaneous access to a database file with older versions of SQL Server Compact. For information about 64-bit components, see Managing 64-bit Database Applications.

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).

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.

Important

Now SQL Server Compact 4.0 supports complete 256 connections, without throwing any error for running out of Virtual Memory.

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.