SQL Server Compact in Windows Mobile 6.5 Apps

SQL Server Compact Edition (SQL CE) served as the primary embedded database engine for Windows Mobile 6.5 enterprise applications, providing a lightweight, in-process relational store capable of offline data persistence. By coupling SQL CE with the .NET Compact Framework, developers built robust line-of-business mobile tools that operated seamlessly in disconnected environments and synchronized with central SQL Server instances once connectivity was restored.

Embedded In-Process Architecture

Unlike client-server database engines, SQL Server Compact operated strictly in-process within Windows Mobile 6.5 applications. The database engine loaded directly into the calling application's memory space via compact dynamic link libraries (DLLs), requiring a footprint of only 2 to 3 megabytes.

Data was stored locally in single, self-contained files with the .sdf (SQL Server Database File) extension. Because the engine shared the memory space of the host process, inter-process communication overhead was eliminated, allowing fast read and write operations on resource-constrained ARM processors typical of Windows Mobile devices.

Development and Data Access

Integration was primarily driven through Visual Studio 2008, the standard development environment for Windows Mobile 6.5. Developers targeted the .NET Compact Framework (typically version 3.5) and interacted with local databases using standard ADO.NET abstractions:

  • System.Data.SqlServerCe Namespace: Provided mobile-optimized classes such as SqlCeConnection, SqlCeCommand, SqlCeDataReader, and SqlCeDataAdapter.
  • Direct Table Access: To maximize performance on low-power devices, developers frequently used SqlCeCommand.ExecuteResultSet alongside SqlCeResultSet. This allowed direct, bidirectional cursor access to underlying indexes without loading entire datasets into RAM.
  • Native Development: C++ developers used the OLE DB Provider for SQL Server Compact to query local .sdf files without the overhead of the managed runtime.

Data Synchronization Techniques

The defining feature of SQL Server Compact on Windows Mobile 6.5 was its ability to synchronize edge data with central corporate databases (SQL Server 2005 and 2008). This was achieved via two main mechanisms:

  • Remote Data Access (RDA): A lightweight synchronization model using HTTP or HTTPS. Mobile clients used RDA to execute a Pull to extract data from a remote server, a Push to send local modifications back, or SubmitSQL to run pass-through SQL commands directly against the remote host.
  • Merge Replication: A robust, bidirectional synchronization framework built on top of Internet Information Services (IIS). Merge Replication tracked row-level changes using triggers and metadata tables on both the client and server. It supported complex schema structures, automatic schema updates, and programmatic conflict-resolution rules directly suited for field-service workflows.

Security and Resource Management

Windows Mobile 6.5 lacked modern platform sandboxing, making localized data protection essential. SQL Server Compact addressed this by offering native, 128-bit database-level encryption. Developers protected .sdf files by specifying password and encryption properties directly in the connection string. Furthermore, the engine included auto-shrink and compacting routines (Engine.Compact and Engine.Shrink) that developers invoked programmatically to prevent fragmented .sdf files from exhausting limited flash memory on mobile devices.