Just saw Aaron Bertrand's post from PASS on SQL Server 2008 Change Tracking vs Change Data Capture (change data capture (or CDC) is in the current CTP; change tracking is not). I'm not at PASS this week myself, but home while the house is being re-roofed. His post seems to confirm something I'd suspected all along.

Change tracking seems to go hand-in-hand with Sync Services for ADO.NET. I've been following Sync Services for a while; it's a set of libraries for controlling and implementing synchronization for disconnected database applications. Its current shipping vehicle is the Visual Studio Orcas Beta 2 release, along with SQL Server Compact Edition version 3.5. The fact that SQL Server 2008 Change Tracking provides a mechanism to keep track of which user (the sync OriginatorID) made a change (CDC doesn't) and also seems to provide automatic change table management for DELETEs (CHANGE_RETENTION) and a mechanism to "get the set of changes that have occured from a baseline" (the "sync_last_received_anchor" in sync services) makes Change Tracking line right up with what Sync Services requires.

Although Aaron mentions "offline stores like Outlook in cache mode", SQLCE is an exciting offline store because its currently deployed in places like Windows Media Player, Zune, Media Center PC, and more. SQLCE runs on mobile devices and desktops. Sync Services isn't available for mobile devices yet, but is said to be "in progress".

When you set up a Sync Service app, you currenly must make changes to the database (triggers, "tombstone tables for deletes", and such) referred to by the "ServerProvider", in order to track the information Sync needs. The Sync "ServiceProvider" architecture layers over/shares concepts with the ADO.NET provider model. But you don't have to make changes for the SQLCE 3.5-side (SQLCE is the only current "ClientProvider" that Sync Services supports) because, "support for sync is built in". Well...maybe it's built in to the server too, with SQL Server 2008. Bet we'll see (at least one) demo with Sync Services when the Change Tracking feature ships.

I received mail today from the SQL Server Compact Edition folks at Microsoft on my blog post on using SQL Server CE 3.5 beta, Visual Studio Beta 2, LINQ, and EDM. The current plan is:

1. Visual Studio 2008 will not ship with LINQ to SQLCE designer support. SQLMetal works just fine with SQLCE, though, as I'd mentioned.
2. There is planned future support for using SQLCE with EDM and LINQ to Entities when EDM ships after Visual Studio 2008 (VS 2008 SP1?).
3. There is a fix in the works for the FK issue that I had with the SQLCE Northwind sample database and SQLMetal.

Along these lines, I asked about the integration of LINQ to SQLCE with the updateable, scrollable, cursor-like behavior of SqlResultSet. Because SQLCE is an embedded database ("the engine" loads into your application) using the DataSet with SQLCE programming adds a layer of buffering (read: memory allocation and data copying) between the data and you. SqlResultSet is a perf win over using DataSet, and using LINQ to SQLCE or EDM to SQLCE, although they don't use the DataSet, doesn't allow in-place updating like SqlResultSet does. And they do use memory allocations rather than read directly from the SQLCE database. The current LINQ to SQLCE doesn't support SqlResultSet-like behavior yet, but perhaps in future...

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "different" data sources. This post covers using SQLCE 3.5. The latest version of SQLCE 3.5 comes with Visual Studio 2008 Beta 2.

Using SQLCE 3.5 data sources doesn't work with the built-in LINQ for SQL designer in Visual Studio 2008 beta 2 or the CTP EDM designer released shortly after beta 2. You can add a Data Connection for a SQLCE 3.5 database. I used the Northwind.sdf sample database that was supplied. But dragging a SQLCE table on to the LINQ to SQL designer produces "The selected object(s) use an unsupported provider". The EDM Wizard doesn't even list the SQLCE ADO.NET data provider as a choice. So there's no LINQ to SQL or EDM designer support for SQLCE 3.5.

SQLCE works quite well with SQLmetal, although my attempt to generate a .dbml for the Northwind.sdf sample database produces the error message:

error DBML1055: The DeleteOnNull attribute of the Association element 'Order Det
ails_FK01' can only be true for singleton association members mapped to non-null
able foreign key columns.

Removing the "Order Details" table provides a 'hit it with a hammer' solution; I haven't figured out a nicer way to solve this problem yet. Then, the SQLMetal-generated classes worked fine.

Using EDMgen against the Northwind.sdf file produced the following error:

error 7001: Could not load System.Data.SqlServerCe.Entity.dll. Reinstall SQL Server Compact.
Could not load file or assembly 'System.Data.SqlServerCe.Entity, Version
=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its depend
encies. The system cannot find the file specified.

Hmmm...the version of SQLCE that came with Visual Studio 2008 B1 did come with this DLL. And, I had it working with EDM then. But the version in VS2008 B2 doesn't. As a last resort, I installed the SQLCE 3.5 version from VS 2008 B1, it's out on the web as a separate download. This changed the error message to:

error 7001: Method 'CreateDbCommandDefinition' in type 'System.Data.SqlServerCe.
SqlCeProviderServices' from assembly 'System.Data.SqlServerCe.Entity, Version=3.
5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not have an implementation.

Sure enough, the required implementation of the ProviderServices class required for EDM support has changed between VS Beta1 and VS Beta2. I confirmed this by checking the methods in the SqlProviderServices in System.Data.Entity.dll, they did change recently, adding CreateDbCommandDefinition. Unless I can find an updated System.Data.SqlServerCe.Entity.dll somewhere, it doesn't look like SQLCE is usable with EDM at this point in beta-time.

That's all for now.

SQLCE 3.5 comes with an ADO.NET data provider that supports entities, by supporting the ADO.NET 3.5 entity data model (EDM). I noticed this in Orcas March CTP with the inclusion of a new DLL, System.Data.SqlCe.Entities. Although the DLL was there, there was no support in Visual Studio for any provider but SqlClient, and trying to do this manually failed (DbProviderFactory.GetService returned null, IIRC).

In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

Here's a simple example of using EDM with SQLCE 3.5, database included. You need to put the pubs.sdf database in c:\temp or change the connection string in the app.config to make this work. The sample uses both EntityClient and Entity Services. It could just as easily use LINQ for Entities.

An interesting idea is that if the internals of Sync Services were tweaked sightly, they could use the EDM and EDM's generated SQL in addition to (or instead of) DataSets. Imagine replicating/synchronizing Employee entities with associated Job information instead of synchronizing Employee and Jobs tables. The logic could be hooked in from what AcceptChanges does.

One of the latest developments in the SQLCE space is the beta for ADO.NET Sync Services. Sync Services ships as a standalone download, runnable on Visual Studio 2005 and is also built-in to Visual Studio Orcas. Both versions require a new version of SQLCE, version 3.5. This runs side-by-side with SQLCE 3.1, although both version's DLLs have the same names they are installed in different directories. The Orcas B1 version has a newer version of the DLLs and install GUI support.

The Orcas GUI support consists of a new component "Local Database Cache" that can be added a various types of C# and VB.NET projects. This bring up a Configure Data Synchronization dialog. In this dialog, you can configure a Server connection and a Client (SQLCE) connection, select "Cached data objects" (tables to be replicated) and replication specifics. You can also configure the feature of Sync Services that allows you to make synchronization service-based, that is, use a middle-tier WCF, Web Service, or other middle-tier component for connect to the database and perform the synchronization.

Besides service-based synchronization, Sync Services allows you to sync to data sources other than SQL Server. You can sync to most any database that supports ADO.NET, for example, Oracle. It works by instanciating a DataSet (although I'd though it could/might use EDM in future, see next post) and performing synchronization through the DataSet. Sync's DbServerSyncProvider uses a SyncAdapter, making it even more ADO.NET-like. Although DbServerSyncProvider can use any compliant ADO.NET provider, SqlCeClientSyncProvider is SQLCE specific.

For some excellent examples of Sync Services in action, check out The Synchronizer's (aka Rafik Robeal) blog. I think Rafik is up to six samples now, one of which works for an Oracle backend database.

For some excellent videos showing Sync Services with Visual Studio Orcas, check out the links on Steve Lasker's blog. Although I haven't tried it out yet, I think his WCF service-based example is running from the Orcas Beta1 version of the designer.

Next: SQLCE and Entities

So now that I've found SQLCE and know which version I have, what can I do with it besides use the ADO.NET and OLE DB APIs? Well, turns out I need some auxiliary pieces. If you use 3.0, the pieces "come with", but when you upgrade to 3.1, you need to upgrade these.

I must admin I found this somewhat confusing, probably because I "started from scratch" and also wanted to use 3.1. I had to intall some pieces before it would "recognize" others. For example, unless you have the Windows Mobile dev tools installed, the 3.1 VS tools won't install. And after installing the Windows Mobile dev tools on VS SP1, I had to install VS SP1 again. And none of these installs made the "Configure Web Synchronization Wizard" app available until I installed the Server Tools.

This turns out to be more important than you might think. For example, the GUI DLLs are used to CREATE the database itself. I've not yet investigated if there is a flie format difference in different SQLCE versions, as there is with different SQL Server versions. Or how/when SQLCE database files with data are upgraded. And using the 3.1-specific features, that is the |Data Directory| connection string directive, and Click-Once deployment support require the updated version of the dev tools. When you install SQL Server 2005 SP2, your bits are updated, but these are a subset of the bits you use for development in VS.

Here's the list of additional bits (most are 3.1 updates) I came up with, along with some terse notes:

-- Server Tools (SQLCE30setupen - localized)
   Updates IIS to support connectivity solutions (Merge Repl)
   Special considerations for IIS 7.0 (IIS on Vista)
   No need if SQL Server and IIS on same machine
   These are not updated for SQLCE 3.1
   Includes Configure Web Synchronization Wizard app

-- Tools for VS2005 SP1 (SSCE31VSTools)
   Requires installating VS2005 SP1 first
   Adds Click Once Support for SQLCE 3.1
   Updates design-time UI to work with SQLCE 3.1
   Adds 3.1 device cab files
   Requires VS WindowsMobile dev tools install
   If VS not upgraded to SP1, uses SQLCE 3.0 design time UI

-- Developer SDK (SSCE31SDK)
   Cab files for devices
   MSI for desktop and tablet PC editions
   Header files
   MSI for Compact Edition help files
   Northwind sample app

-- Books Online (SSCE31BOL)

-- SQL Server 2005 Compact Edition Access Database Synchronizer
   Supports Access 2000 SP3, 2002 SP3, 2003 SP2, 2007
   Not sure if this supports 3.0, 3.1 or both
   Runs as a service, Uses RDA access and HTTP transport.
   Requires Active Sync 4.0 or later

-- SQL Server 2005 Sync Services CTP
   Installs 3.5 side-by-side, but no new device support yet
   Installs new ADO.NET-based sync
   Included with Orcas B1, works with VS2005 also
   There is a separate documentation and demos download

I'll only be using 3.1 and above because (blush) I don't have a compact device except for the emulator in Visual Studio. So I'm working desktop and emulator only for now.

Next: Newest stuff

As part of a project, I've had occasion to look at the SQL Server Compact Edition in earnest. Although I've taken a cursory glance before this, its my first attempt to put all the pieces into place. So I thought I'd write it down as I went along.

SQL Server Compact Edition's original name was SQL Server Mobile, but as of version 3.1 its now supported on a variety of mobile devices but its also supported on Tablet and Desktop PCs. For simplicity, I'll refer to all versions as SQLCE from now on.

SQLCE ships with SQL Server 2005 and will also ship with the next version of SQL Server (Katmai). I believe it installs if you install the SQL Server 2005 adminstrative tools, but doesn't show up in Add/Remove Programs.  It also ships with Visual Studio, if you install the Compact Framework dev tools. You can also download it stand alone.

It consists of 6 DLLs, no services, not even an exe. You can program it with the ADO.NET data provider or OLE DB provider. Each is included as a separate DLL. When installed with VS or standalone it lives in the directory C:\Program

Files\Microsoft SQL Server Compact [or Mobile] Edition\v3.x. There are subdirectories that contain the redistributables for various compact devices and versions of Windows CE. When installed with SQL Server it lives in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE directory.

The versions that come with various product versions are:

SQL Server 2005 RTM and SP1: Version 3.0, build 3.0.5207.0
SQL Server 2005 SP2: Version 3.1, build 3.0.5300.0
Visual Studio 2005: Version 3.0, build 3.0.5207.0
Standalone download of SQL Server Compact Edition 3.1: Version 3.1, build 3.0.5300.0
Mar CTP standalone and Mar CTP of VS Orcas: Version 3.5, build 3.5.5305.0
VS Orcas Beta 1: Version 3.5, build 3.5.5334.0

Interestingly, the DLLs always end with "30" in all versions. You know which version by looking at the file properties, not the DLL names. Version 3.5 will supposedly also ship with SQL Server Katmai, but this is not available yet.

The reason SQLCE ships with SQL Server and Visual Studio is that these are the development and admin environments for it. You don't just start up the .exe and work from the command line, you use either SQL Server Management Studio or Visual Studio's Server Explorer. Each of these tools come with dialog boxes that allow you to create a database and interact through the GUI (VS's Server Explorer/Data Connections or SSMS's Registered Servers/Object Explorer/Query Window). You can also create and manipulate a database through the programmatic APIs. You must have SSMS to make this work with SQL Server, there is no support for SQLCE in SQL Server Management Studio Express.

In addition to DDL and DML, both environments allow you to configure merge replication, as support for what's called "Occasionaly Connected Systems" is one of the main points of SQLCE. Besides SQL Server Merge Replication, you can interact with the "main server database" through RDA (remote data access) or the new ADO.NET Sync Services, currently in beta.

Next: Associated pieces