I was preparing some demos for the upcoming Academic Community Launch Tour that we – a bunch of Microsoft Student Partners in Austria – are organizing (see http://anuga.at for details). I had just finished a wonderful little app using SQL Compact Edition, Sync Framework, ASP.net MVC and all the funky stuff. Just before I wanted to call it a night I noticed the sync portion of my app suddenly wasn’t working anymore. Somehow I had done something bad and the sync framework wasn’t finding any rows that had changed. This morning I got up and took a closer look underneath the covers of SqlCeClientSyncProvider. This is what I found out.

A word of warning: The information here only applies to Sync Services for ADO.net and is based on Version 2.0 with some help of Reflector. Use the information and my interpretation with caution. Especially beware of the fact that Sync Services for ADO.net in the Sync Framework are handled completely differently than Sync Services for File Systems and Custom Sync Providers.

The sync framework uses anchors to determine what rows have changed. These anchors are compared to the metadata in the table. When creating the tables on the server you probably inserted columns such as InsertTimestamp or UpdateTimestamp. That’s the metadata. Fine, but what happens on the client side in a SQLce database? At first I thought the metadata columns are transferred to the client in the schema (remember the SqlCeClientSyncProvider retrieves the schema to create on the table from the server) and then used for the change tracking there too.

Well not quite. In the app I’m working on I created the SQLce database first (and used used SQLMetal to generate my Linq2Sql classes by the way which is really easy. I’ll do another post in a moment). SQLce doesn’t support triggers so I only created a timestamp column called ‘TS’. When it came to sync’ing the client with a server database, that looked similar but had more metadata columns, I noticed I had no idea how the metadata part worked on the client. (It’s actually quite nice too know that for standard use cases it just works out of the book.)

Enough bla, let’s get to the details. Thanks to a couple of hours with reflector and running queries against my SQLce database I found out where the metadata goes.

Preparing my custom tables for the meta data

When you configure your SqlCeClientSyncProvider you add SyncTables with a couple of options:

foreach (SyncTable _syncTable in syncTables)
_syncTable.CreationOption = TableCreationOption.UploadExistingOrCreateNewTable;
_syncTable.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;
// This would recreate the schema for each table on each sync, which resets the timestamps to 0.
// m_clientProv.CreateSchema(_syncTable, null);

You can see a line at the bottom that I commented out. I figured that since my client database was in place and I specified UploadExistingOrCreateNewTable that my client database needed to be adapted to include the sync metadata. So I called CreateSchema on the provider which does just that. But you don’t want to create the schema every time, so I should actually move the call into my database initialization code, but I didn’t want to mix my sync code with my DAL code and I didn’t need to. CreateSchema is called at the beginning of the sync session only if the metadata is not found in the table.

Once that method has run all sync tables now have two extra columns: __sysChangeTxBsn and __sysInsertTxBsn. These are initialized with 0 and contain a binary timestamp.

CreateSchema also creates some more tables. First of all it creates a sync subscription table called ‘__sysSyncSubscriptions’. This contains the client’s id and it’s pairing with any server sync providers. Next up is an article table called ‘__sysSyncArticles’. An article is just a sync table, so it contains the table name, the last sent and last received anchors for a specific table.

Once it has done that it just switched on the built-in SQLce change tracking which in turn updates the metadata columns when ever a change occurs.

But wait there is one thing missing. What happens to deleted rows? So far we don’t have any tombstone or archive tables. Actually we do, a table called ‘__sysOcsDeletedRows’ was created that is used by the SQLce change tracking to store deleted rows. It has columns ‘__sysTName’ (the table name), ‘__sysRK’ (the reference key = primary key that was deleted) and the timestamp value when it was deleted and inserted.