SQL Server 2005 Compact Edition RTM Available

Microsoft delivered its Web release of SQL Server 2005 Compact Edition (SQLceSSCE) on January 11, 2006. The Web release finally eliminates previous references to Windows Mobile and SQL Server 2005 Everywhere Edition.

SSCE is a lightweight, single-user relational database engine designed for client-side data storage on Windows desktops, laptops, and devices. Its query engine supports a subset of SQL Server 2005 data types and T-SQL.

The Web release consists of the following four downloadable components:

• Microsoft SQL Server 2005 Compact Edition Runtime (SQLServerCE31-EN.msi) installs the SQLce redistributable components to a specific location (\Program Files\SQL Server Compact Edition\v3.1\) on the destination computer which enables future replacement by Windows Update.
• Microsoft SQL Server 2005 Compact Edition Developer SDK (SSCE31SDK-ENU.msi) installs files required to set up SQLce on mobile devices, and desktop and tablet PCs, Books Online for SQLce, and the Northwind sample application.
• Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005 SP 1 (SSCE31VSTools-ENU.exe) installs Click Once support for SQLce applications, updates the Smart Device Development components in VS 2005 SP1 with the SQLce design-time UI and dialogs and device CAB files.
• Microsoft SQL Server 2005 Compact Edition Server Tools (Sqlce30setupen.msi) installs the SQL Server Replication Components on the machine running IIS. It isn't required if SQL Server 2000 or 2005 is on the same machine as the IIS replication instance.

SQLce30setupen.msi—the only file that doesn't use the SSCE abbrevation—apparently didn't need updating from the SQL Server Mobile 3.0 version.

Support: SSCE's main page is here and the newsgroup is here. There's an SQL Server Compact Edition blog and Steve Lasker's blog has links to many SSCE resources. Steve is the SQL Server Program Manager/Technical Lead for Smart Client and Occasionally Connected Systems.

Programming and Managing SSCE

The SqlServerCe namespace provides data access classes similar to ADO.NET's SqlClient. Installing the runtime also registers an OLE DB driver for applications running unmanaged code. Data files (*.sdf) are stored in the file system and are compatible with Windows Mobile 3.0 applications. SSCE 3.1 is ideally suited for Click Once installation of data-driven Windows forms apps. See my May 1, 2006 "SQL Server 2005 Mobile Edition Goes Everywhere" Special Report for an early look at SSCE. The SQL Server Management Studio [Express] SP2 December 2006 CTP or later (SSMS[X] SP2) is required to manage SSCE if you aren't running Visual Studio 2005 SP1 because the VS Express editions don't include Server Explorer. But you'll find SSMS[X] SP2 to have somewhat better management tools than VS 2005 SP1.

Note: Download SSMSX December 2006 CTP here or the full SSMS version here.

Use SSMS[X] or VS 2005 SP1's or T-SQL DDL scripts to create and modify SSSCE's *.sdf data files, which store character-based data in Unicode only. You'll need to use DDL for operations other than creating databases and adding, deleting and modifying tables.

For an in-depth look at programming SSCE with Visual Basic .NET, download a copy of SQL Server guru and author Bill Vaughn's Hitchhiker's Guide to Microsoft SQL Server 2005 Compact Edition eBook (a bargain at the US$9.95 introductory price).

Replicating SSCE

SSCE supports merge replication and Remote Data Access synchronization scenarios with SQL Server 2000 SP3 and later. The next version (3.5) of SSCE will scheduled to include a new Microsoft Synchronization Services for ADO.NET (code-named OCS for Occasionally Connected Systems) in Visual Studio Orcas. A pre-release version of SSCE v3.5 is included in the Microsoft Pre-release Software Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP).

Update 1/27/2007: Microsoft released on January 22, 2007 a Microsoft Synchronization Services for ADO.NET CTP, which also includes support for SSCE LINQ to Entities.

For more details on SQL Server <-> SSCE replication technologies, see the "ADO.NET Sync Framework for Occasionally Connected Systems" post. An SQL Server Client Access License (CAL) is required for replication between SQL Server and SSCE. (SQL Server 2005 Express Edition (SSX) has the same CAL requirement for replication.)

Note: Microsoft says Virtual Server 2005 R2 SP1 is not a supported system for SSCE. This might explain my problem with installing the runtime on "virtualized Vista" but not the lack of problems with "virtualized Windows Server 2003 R2" installation.

Update 1/15/2007: Abbreviation changed from SQLce to SSCE. Google shows 124,000 hits for the SQLce and 14,000 for SSCE abbreviations, but Microsoft appears to have standardized on SSCE. SSCE is consistent with use of SSX for SQL Server Express and SSMS[X] for SQL Server Management Studio [Express].

Update 1/27/2007: Updated for OCS CTP and added links to SSMS[X] downloads.

Technorati tags: SQL Server 2005 Compact Edition, SQL Server Compact Edition, SQL Server CE, SQLce, SSCE, SQL Server 2005 Everywhere Edition, SQL Server Everywhere, SQL Server 2005 Mobile Edition, SQL Server Mobile, MSSQL, Orcas, Occasionally Connected Systems, OCS, Microsoft Synchronization Services, SyncServices