Working with SQL Server Compact

  • Article

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

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

Setting Up SQL Server Compact 3.5 Environments

You can set up the SQL Server Compact 3.5 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 Visual Studio 2008. For more information, see Installing a Development Environment. Installing SQL Server Compact 3.5 provides the client environment. For more information, see Installing and Deploying on a Device (SQL Server Compact).
  • To build distributed database applications that use SQL Server Compact 3.5, you must set up a development environment, a client environment, and a server environment. Setting up these environments includes installing Visual Studio 2008, SQL Server 2008, and SQL Server Compact 3.5; installing and configuring Microsoft Internet Information Services (IIS); and configuring connectivity. For more information, see Client and Server Environment, Installing SQL Server Compact 3.5, and Installing a Server Environment.

Configuring Security

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

SQL Server Compact 3.5 can also use the Secure Sockets Layer (SSL) feature of IIS to encrypt data propagated between a device running SQL Server Compact 3.5 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 3.5 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.

For more information about connectivity options, see Managing Connectivity (SQL Server Compact).

Working with SQL Server Compact 3.5 Databases and Data

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

SQL Server Compact 3.5 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 3.5 Database Engine occupies approximately 2.2 to 2.6 MB of disk space, depending on the processor of the target device. SQL Server Compact 3.5 supports databases up to 4 GB.

For more information about working with SQL Server Compact 3.5 databases, see Working with Databases (SQL Server Compact).

Building Applications

SQL Server Compact 3.5 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 3.5 can include creating the local SQL Server Compact 3.5 database, updating the local data, and merging changes that were made on the device with a SQL Server database.

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

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

See Also

Other Resources

Installing SQL Server Compact
How-to Topics (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)