Working with SQL Server Compact Edition

  • Article

Working with Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) or building an application that uses SQL Server Compact Edition occurs in the following stages:

  • Setting up a SQL Server Compact Edition environment
  • Configuring security, connecting clients and servers
  • Working with SQL Server Compact Edition databases and data

Setting Up SQL Server Compact Edition Environments

You can set up the SQL Server Compact Edition environment to build either stand-alone desktop applications or distributed database applications.

  • To build an application on your desktop that can be deployed to a device intended to run locally, you must set up a development and a client environment. The development environment involves installing Microsoft Visual Studio 2005. For more information, see Installing a Development Environment. Installing SQL Server Compact Edition provides the client environment. For more information, see Installing and Deploying on a Device (SQL Server Compact Edition).
  • To build distributed database applications that use SQL Server Compact Edition, you must set up a development environment, a client environment, and a server environment. Setting up these environments includes installing Visual Studio 2005, Microsoft SQL Server 2005, and SQL Server Compact Edition; installing and configuring Microsoft Internet Information Services (IIS); and configuring connectivity. For more information, see Client and Server Environment, Installing SQL Server Compact Edition, and Installing a Server Environment.

Configuring Security

After the necessary SQL Server Compact Edition systems are installed, you must configure the IIS security options before any data can be exchanged between clients and servers.

SQL Server Compact Edition can also use the Secure Sockets Layer (SSL) feature of IIS to encrypt data propagated between a device running SQL Server Compact Edition and a computer that is running SQL Server.

For more information, see Configuring and Securing the Server Environment.

Connecting Clients and Servers

SQL Server Compact Edition supports two methods of connecting to and exchanging data with a data source:

  • Remote data access (RDA)
  • Merge replication

These connectivity solutions use the authentication, authorization, and encryption services of IIS. The connectivity can be performed over wired and wireless LANs and WANs. Microsoft ActiveSync can use the network connection on a desktop computer to communicate with SQL Server. This eliminates the requirement for the device to have a network card or modem.

Working with SQL Server Compact Edition Databases and Data

You can modify and manage SQL Server Compact Edition databases and data by using the following components:

  • Programming APIs
  • SQL language
  • and Express

SQL Server Compact Edition also provides multiple-connection access to a database, multiple-column indexes and matching index limits, transaction support, referential integrity, 128-bit file-level encryption and password protection, and a query optimizer.

The SQL Server Compact Edition Database Engine occupies approximately 2.2 to 2.6 MB of disk space, depending on the processor of the target device. SQL Server Compact Edition supports databases up to 4 GB.

Building Applications

SQL Server Compact Edition and the .NET Compact Framework provide development infrastructure technologies that you can use to build applications for devices that access enterprise information systems.

An application that uses SQL Server Compact Edition can include creating the local SQL Server Compact Edition database, updating the local data, and merging changes that were made on the device with a SQL Server 2005 database.

The following programming models are supported by SQL Server Compact Edition:

  • ADO .NET, accessible by using C# or Microsoft Visual Basic
  • OLE DB, accessible by using Visual C++ option from within Visual Studio 2005