Exploit SQL Server Compact Edition and Occasionally Connected Systems

Take advantage of SQL Server Compact Edition as a local data cache and automatically synchronize its data with a back-end SQL Server 200x instance.

SQL Server Compact Edition v3.1 and v3.5's free license covers deployment on Windows PC clients as well as devices, so you can take advantage of this lightweight, in-process relational database as a local data cache and automatically synchronize its data with a back-end SQL Server 200x instance.

SQL Server Compact Edition (SSCE, formerly SQL Server Everywhere Edition) is a full-featured, freely distributable, embedded relational database that runs in-process on Windows devices as well as Tablet PCs and, as of v3.1 released in 2006, conventional laptop and desktop PCs. (Microsoft's SSCE license for versions 3.0 and earlier was directed primarily to devices and permitted installation on PCs that only ran a Windows Tablet PC operating system.) SSCE's installation size is about 1.8 MB and its memory footprint is less than 5 MB; it supports transactions, password protection and data encryption. SSCE's query language is a subset of Transact-SQL (T-SQL), so you don't need to learn another language. The SSCE database engine is a small set of DLLs, which lets you include it with your application in a conventional MSI file or ClickOnce deployment.

Installing SQL Server 2005 Mobile Edition with Visual Studio 2005 installs v3.0, which isn't licensed for use on PCs. Current SSCE versions are 3.1 for VS 2005 and 3.5 beta for Visual Studio 2008, which installs side-by-side with v3.1. SSCE v3.5 adds the timestamp data type, supports nested queries in SELECT FROM clauses, provides new CROSS APPLY, OUTER APPLY, CAST and TOP keywords, and enables the Entity Framework for SSCE data sources.

SQL Server Management Studio [Express] SP2 lets you open, create and manage SSCE DataBase.sdf files and the graphical table designer in VS 2008's Server Explorer lets you add foreign key constraints. Both tools have problems with SSCE in VS 2008 beta 1, but the Server Explorer problem should be fixed in beta 2. You may need a Management Studio version from a SQL Server 2008 CTP to solve the problem with SSCE v3.5. (SSCE v4.0 is scheduled to ship with the new SQL Server release). You can keep up to date on SSCE issues in the SQL Server Compact Edition forum.

SSCE is one of the two databases that the Entity Framework's managed EntityClient provider supports; the other is SQL Server [Express], of course. The beta 1 version of the Entity Data Model Wizard, which starts when you add a class to a VS 2008 project with the ADO.NET Entity Data Model template, won't let you change the data provider to SSCE. The Entity Framework no longer is part of Orcas, so it's likely that an out-of-band Community Technical Preview (CTP) will be needed to fix the EDM Wizard. The ADO.NET Team blog and ADO.NET Orcas forums support the Entity Framework.

Occasionally Connected Systems (OCS) technology introduces Microsoft Synchronization Services (Sync Services) for ADO.NET run-time beta 1 for VS 2005 and 2008; Sync Services requires .NET Framework 2.0, not 3.x. Download Sync Services Books Online because the 2-MB run-time installer doesn't include it. OCS supplements — but doesn't replace — SQL Server merge replication to keep local databases up to date. SQL Server 2005 Express (SSX) isn't a merge replication publisher, so you must choose Sync Services if you want to use an SSX instance as the sync server. SSCE with Sync Services is a good candidate to replace DataSets containing look-up tables to and from which you save and load local XML files. MSDN's The Synchronizer blog offers links to well-documented C# sample projects from Rafik Robeal's SyncGuru blog that use the Sync Services run-time, and the Microsoft Synchronization Services for .NET forum provides Microsoft and peer support for Sync Services. (OCS is a component ofSQL Server 2008's Dynamic Development pillar.

VS 2008 adds a graphical Sync Services Designer, which opens when you add a class to your project with the Local Data Cache template. The designer automates the process of creating and initially populating the SSCE Database.sdf file from tables that you select from the SQL Server [Express] source database. Only a few lines of code are necessary to implement a fully functional bidirectional synchronization app for SSCE and SSX. VS 2008 beta 1's Sync Services designer has problems, too. Rafik Robeal says "changes to [the] run-time and designer are coming in beta 2.0." Steve Lasker is the Senior Program Manager for the Sync Services Designer; he offers several presentations about the designer on his blog.

About the Author

Roger Jennings is an independent XML Web services and database developer and writer. His latest books include "Special Edition Using Microsoft Office Access 2007" (QUE Books, 2007) and "Expert One-on-One Visual Basic 2005 Database Programming" (WROX/Wiley, 2005). He’s also a VSM contributing editor and online columnist and manages the OakLeaf Systems blog. Jennings’ Code of Federal Regulations Web services won Microsoft’s 2002 .NET Best Horizontal Solution Award. Reach him at

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.