email article printer friendly more resources

Lighten Up Your Local Databases
Put local data storage on a resource diet and gain performance with the newly upgraded (and free) SQL Server 2005 Compact Edition.
by Roger Jennings

March 19, 2007

Technology Toolbox: VB.NET, C#, SQL Server 2005, XML, SQL Server 2005 Compact Edition runtime, Visual Studio 2005 Standard Edition SP1 or higher, SQL Server Management Studio [Express] SP2

SQL Server 2005 Compact Edition (SSCE) is Microsoft's recently released contender for lightweight champion of the desktop, laptop, and Tablet PC relational database market.

SSCE is an easy-to-deploy transactional database with a nominal 1.8-MB disk and 5-MB memory footprint. Contrast this with SQL Server 2005 Express Edition (SSX), which is a 53.8 MB download that expands to 197 MB on disk and starts at about 80 MB memory usage. SSCE also is a componentized, in-process database limited to a single-user. You can incorporate the SSCE database engine, query processor, and replication/synchronization components with your smart-client Windows app in a small Click Once MSI package. The components include a .NET managed provider that emulates SqlClient and an optional OLE DB provider. SSCE supports strong password protection and 128-bit RSA encryption. Its code-free database files and in-process architecture minimize exposure to malicious attacks.

SSCE provides several advantages over previously existing Microsoft database technologies if you need a lightweight database solution. For example, many developers find that the typed DataSets they persist as XML files load too slowly, and that SQL Server Express is more than they need for a local data store. Others want to simplify replication with SQL Server 2005 or SQL Server Express. SSCE's small disk and memory footprints make it likely that you'll be able to use it in a multitude of desktop client projects that can benefit from short- or long-term data caching with a lightweight, high-performance database and query engine that's freely distributable.

SSCE's primary role is delivering an efficient, strongly typed client-side data cache that's especially suited for occasionally or usually connected Windows systems. SSCE offers ADO.NET developers a secure, high-performance alternative to persisting DataSets as clear-text XML files. I'll walk you through how to create SSCE database files and add tables and indexes graphically with the Object Explorer of SQL Server Management Studio (SSMS) SP2 or its Express Edition (SSMSX) SP2; and Server Explorer of VS 2005 Standard Edition SP1 or higher. (The VS Express Editions don't include Server Explorer.) I'll also explain how SSCE's subset of SQL Server data types and T-SQL syntax differs from that of SQL Server 2005. This article's sample solution relies on VB code to add tables, indexes, and foreign key constraints, as well as create, populate, encrypt, compact, and repair SDF databases. To run the SSCETest.sln sample project (see the Go Online box for details), you must download and install the SSCE v3.1 runtime bits (see Additional Resources) or run the Orcas January 2007 Community Technology Preview (CTP), which installs an early version of SSCE v3.5.

An SSCE database is an ideal candidate for storing large amounts of slowly changing reference or catalog information because you don't need to repopulate datasets from giant XML files when your client loads. My "Store Large Lookup Tables in DataSets" article in the July 2004 VSM edition provides typical loading times and memory consumption for various DataSet sizes. Your app can keep the reference data up-to-date by push or pull synchronization with Remote Data Access (RDA) and SQL Server 200x or SSX, or merge replication with SQL Server Workgroup Edition or higher. (SSX is a merge-replication subscriber only.) Merge replication lets you create the SSCE tables from the replication snapshot. Microsoft Synchronization Services (code-named OCS for occasionally-connected systems) became available as a CTP in late January 2006 (see Additional Resources). OCS enables desktop, laptop, and Tablet PC SSCE clients to synchronize with SQL Server 2005, SSX, and SSCE databases. I'll cover OCS, RDA, merge replication, and how to use the Click Once bootstrapper to create a "central" MSI package in a future VSM article. A central installation, which requires Administrator credentials, puts the SSCE runtime files in a standard location to enable maintenance by Windows Update. Note that private deployment doesn't require Administrator credentials.