SQL Server Compact, SQLite, and File-Based Stores for Mobile .NET Persistence

When a Checksummed File Outperforms an Embedded Engine

Persistence decisions on mobile.NET estates rarely fail at the query layer. They fail at three in the morning, on a handheld with battery recorded around eleven percent, halfway through writing an inspection record. So the useful starting question is not which engine supports the richest SQL dialect, but which store leaves recoverable evidence when the write is cut in half.

Consider a compact settings document that the application reads once at startup and rewrites only when configuration changes. A versioned file carrying a checksum, written to a temporary copy, validated, then swapped into place while the previous copy is retained, involves less operational machinery than an embedded relational runtime with its own provider assembly, native binary, journal files, and upgrade path. The file has no query planner. It also has no deployment manifest to maintain across four device images.

Selecting the store with the longest feature list moves cost rather than removing it. Every additional engine capability arrives attached to a runtime package, a provider binding, a processor architecture, a migration story, and a diagnostic tool somebody must be able to run in a depot two years from now.

SQL Server Compact, SQLite, and application-managed files are better understood as three allocations of responsibility. One hands validation, indexing, and crash recovery to an engine and accepts the runtime dependency. One hands the same work to an engine and accepts ownership of the wrapper and the synchronization contract. One keeps the format under application control and accepts the obligation to write recovery logic by hand.

Inventory the Offline Data Sets Before Naming a Product

Field teams that choose well tend to inventory data before they evaluate products. For each distinct set the application holds locally, record the maximum expected rows or documents, the largest single object, the filters the UI actually issues, the write sequence, the number of local writers, the loss tolerance, the synchronization direction, and who owns a conflict when the server and the device disagree.

Two support questions belong at the end of that inventory, and they are the ones most often skipped: how does an operator export damaged data from a device that will not sync, and how does a future build recognize and migrate the version it finds on disk?

The inventory then separates naturally into categories. Structured records that need filters, joins, indexes, and referential constraints sit on one side. Compact settings, cached documents, append-only diagnostic logs, queued standalone payloads, and immutable reference bundles sit on the other. A downloaded reference catalog justifies whole-package replacement. An unfinished inspection carrying exception codes and a signature reference justifies transactional grouping.

Classify each set as reconstructible, replaceable, or authoritative. Authoritative data is the only category that genuinely forces engine-managed transactions, and most applications hold far less of it than the architecture diagram implies.

Disconnected duration deserves its own line. Model an offline interval of three to seven days when evaluating field data, and include completed records, queued attachments, deletions, and the server-side changes that arrive before the device reconnects. A sync button proves nothing about behavior at day five.

Storage Decision RuleChoose the least complex store that satisfies the query, integrity, recovery, and synchronization requirements without recreating database behavior in application code. The moment a file-based design grows its own index, its own locking scheme, and its own rollback, the argument for an embedded engine has already been made.

Three Mutations That Separate the Candidates

Compare the stores against concrete mutations rather than feature checklists. Use a multi-row work-order completion to test atomicity. Use a selective lookup by asset and status to test indexing. Interrupt a reference-package download immediately before replacement to test recovery.

The atomicity case has a specific shape worth naming. A work-order header, its line items, its inspection answers, and its signature reference form a single integrity unit when the server must never receive a partially completed order. That split determines the transaction boundary more decisively than row counts do.

SQL Server Compact 3.5 supplies an in-process relational store with SQL queries, indexes, constraints, and transactions, and documents a maximum database size of 4 GB. Provider behavior and deployment support still require verification against the exact Compact Framework build and device image in question.

SQLite permits multiple readers while serializing writes to a database. Its rollback journal and write-ahead logging modes carry different locking and recovery characteristics; WAL arrived in SQLite 3.7.0 in 2010, and the selected.NET wrapper plus target filesystem must actually expose and support the intended mode. The engine's SQLite atomic commit behavior documentation describes the sequence that a file-based design must otherwise reproduce by hand.

Files offer no automatic multi-record transaction. A defensible replacement sequence writes a versioned temporary file in the destination directory, validates its length and checksum, requests a durable flush where the platform exposes one, replaces the live file, and validates again at the next startup.

Provider Assemblies, Native Binaries, and Device Images

Database size is the smallest line in the deployment budget. Build a manifest for every supported device image recording the managed provider assembly, the native library, the processor architecture, the on-disk database format, the installer action, the upgrade order, and a diagnostic tool capable of opening exported data.

One boundary catches archival work repeatedly. SQL Server Compact 4.0 was never a smart-device runtime, so a Windows Mobile deployment cannot infer compatibility from a desktop installation of that version. Legacy handheld applications generally need the 3.5-era provider and runtime path validated as its own package, on its own image.

SQLite travels widely, and portability still does not remove binding, architecture, and upgrade testing. The managed wrapper and the native binary must stay aligned through a clean install, an in-place upgrade that retains an existing database, and a deliberate failure case where the native library is missing or built for the wrong processor. Packaging defects should surface on a bench, not in a depot.

File formats need the same discipline in cheaper form: reserve a format-version field from the first release, retain representative files from every shipped version, and run a migration test that opens the oldest supported file, upgrades it once, closes it, and reopens the result with the current build.

Before Field RolloutApprove a store only after clean installation, in-place upgrade, rollback, and data export have each been exercised on every device image. The SQL Server Compact route is chiefly relevant to an existing, compatible mobile.NET estate; where its device runtime, provider, or servicing path is unavailable, it should not be treated as a current cross-platform default. For systems dating from roughly 2008 to 2012, evaluate the runtime and provider combinations those platforms could actually host rather than assuming modern desktop packages were deployable.

Replayable Synchronization Beats a Faster Insert Loop

A local store does not solve synchronization. The design still needs stable identity, change tracking, conflict policy, retries, explicit deletion handling, and idempotent server operations. Specify that protocol before benchmarking anything.

Assign client-generated operation identifiers, persist outbound state before transmission, make server acceptance idempotent, represent deletions as tombstones rather than absences, and declare whether conflicts are rejected, merged, or resolved by an authoritative side. Then measure with all of that bookkeeping enabled, because change tracking and queue maintenance are part of the write cost.

A reconnect test earns its place in the suite: submit the same completed operation twice, lose the first acknowledgement, restart the application, and resend. The server must recognize the operation identifier instead of creating a second work order.

SQL Server Compact sat near synchronization and replication paths in parts of the Microsoft ecosystem, though availability and suitability depended on the server product, edition, framework generation, and device platform. SQLite typically relies on an application-defined synchronization layer with no inherent enterprise server-sync contract, which makes protocol ownership an explicit staffing decision rather than a product feature.

Where To Place TimersInstrument serialization, provider execution, transaction commit or file flush, index maintenance, change tracking, and queue updates. Timing object construction or an in-memory query omits the expensive parts of the persistence path. Repeat the run near the device's low-free-space threshold, since journal creation, temporary-file replacement, and index growth all demand additional storage precisely when there is none.

Pick the Store Whose Failure You Can Repair

SQL Server Compact fits when preserving a compatible legacy application that already depends on its relational schema, transaction behavior, or established integration route. Record the exact runtime and provider versions alongside the deployment package, and state the lifecycle position plainly in the decision record.

SQLite fits when indexed filtering, joins, constraints, and transactional updates are genuinely required and the team can maintain the chosen wrapper, the native binary, migration scripts, and an application-defined synchronization contract.

Files fit bounded settings, append-only diagnostic records, queued standalone payloads, media manifests, and immutable reference bundles that can be validated and replaced as whole units.

Whichever wins, store a schema or format version inside every database and every file rather than only in build metadata, so startup can distinguish a supported older version, an unknown future version, an incomplete replacement, and structurally invalid content. Test interruption at three points: while writing temporary state, after the durable write but before replacement, and immediately after replacement. Those three moments decide whether the next startup sees the previous state, the new state, or an incident that application code must repair.

Keep product-specific calls behind a narrow storage interface, while leaving transaction boundaries and conflict semantics visible to the application services that depend on them. Hiding a commit boundary behind a repository method is how partially completed work orders reach a server.

One number frames the whole exercise better than any throughput chart. SQL Server Compact 3.5 documented a 4 GB ceiling, generous by handheld standards and almost never the constraint that decided a deployment. The constraint was a version boundary: 4.0 shipped without a smart-device runtime, so a working desktop installation predicted nothing at all about the handheld in an engineer's hand.