Sync Framework and SQL Compact 4 (Yes, You Can!)

Inspired by Erik’s Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!) blog post, here’s how you make Sync Framework work with SQL Compact 4.0

While the Microsoft documentation is very clear in stating that Replication and Synchronization is not supported in SQL Compact 4, it’s not uncommon to see questions on forums asking if it’s true or if there’s a way to make this work.

To give you an idea about the support statement, here’s how it goes in the MSDN documentation:

Source: Features not supported in SQL Server Compact 4.0

“Does not support” can mean many things. It could mean the feature isn’t there, the feature is there but is not fully tested,  the feature is there but is not a supported scenario, or for some other reasons, it’s simply not supported. Period. End of story.

I’ll admit I’ve been guilty of answering questions on the forums and stating that SQL Compact 4 synchronization via Sync Framework is not supported. But I think I owe it to show whether it works or not.

So here you go.

In Sync Framework, there are two providers that work with SQL Compact, the first is the offline provider SqlCeClientSyncProvider,  which is also the same provider used by the Local Database Cache Project Item. This works with the SyncAgent/SqlCeClientSyncProvider/DBServerSyncProvider combination.

The other provider is the collaboration or peer-to-peer provider SqlCeSyncProvider which works with the SyncOrchestrator/SqlSyncProvider/SqlCeSyncProvider combinations.

So you wonder which one isn’t really supported?

Let’s go thru each provider.

If you try to use the Local Database Cache Project Item wizard, you’ll see that you can only add a connection for a SQL CE 3.5 database as shown in the two screen shots below.

You’ll probably think, what if I we use the wizard to create a SQL Compact 3.5 database initially and simply upgrade the resulting database to 4.0 or even replace the 3.5  database with an empty 4.0 database.

If you do, you’ll most likely run to an error similar to what is shown below.

If you try to use the SqlCeSyncProvider on the other hand, you’ll run into the same error above and in addition, you’ll run into  another error similar to what is shown below.

Fortunately, to make it work with either SqlCeClientSyncProvider or SqlCeSyncProvider, you can simply put a binding redirect in you application config file as shown below.

If you want to use the Local Database Cache Wizard, you can have it use SQL Compact 3.5 initially and then simply upgrade the database to 4.0 or even replace the database with a new SQL Compact 4.0 database.

For SqlCeSyncProvider, you can simply add the binding redirect and that is all you need to provision and synchronize a SQL Compact 4.0 database.

  Warning

Again, just to remind you, as per Microsoft documentation, this is not supported.

So no blaming if it breaks or corrupts your databases.