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:

image

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.

image

image

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.

image

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.

image

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.

image

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  Warning

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

So no blaming if it breaks or corrupts your databases.

About these ads

8 comments

  1. Dominic S · · Reply

    How have you accounted for MethodAccessExceptions thrown when using the SyncAgent or SyncOrchestrator? I am aiming to use CF 4.1 and CE 4.0 with Sync Framework 2. If I understand your guide correctly:
    1. Produce a CE 3.5 version of the database.
    2. Apply the provisioning mechanism to the database using CE 3.5.
    3. Modify the app config so the runtime assembly used is 4.0.0.
    4. Upgrade the database to CE 4.0.
    5. Using this to perform synchronisation between SQL Express and CE 4.0 database.

    To test that your fix worked I did these steps separately rather than taking on the logistics of making a separate exe produce the database in CE 3.5 before it is used in the application (as the runtime assembly would force any provider to make a version 4 database).

    When trying to use the SyncOrchestrator.Synchronize() there is a brief pause and then “Method access exception” Details: “Attempt by method ‘Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncScopeHandler.GetLocalTimestamp(System.Data.IDbConnection, System.Data.IDbTransaction)’ to access method ‘System.Data.SqlServerCe.SqlCeChangeTracking.get_CurrentTxCsn()’ failed.”

    I tried switching out the references and playing around with various components I am using with no change. Here are the relevant dlls I am using all of them x86: Microsoft.Synchronisation (v2.0.0.0), Microsoft.Synchronisation.Data + SqlServer + SqlServerCe (all v3.0.0.0), System.Data.SqlServerCe (v3.5.1.0).

    I have attempted to use the SyncAgent as an alternative but hit the same type of error message just setting the local provider.

    Can you post your source or clarify see any mistake I have made? I am very keen to get this working as you might be able to tell from the post! :)

    1. do you have SQL CE 3.5 SP2 installed? are you getting the error in both SQL CE 3.5 or 4.0 references?

  2. Dominic S · · Reply

    Yes I have SQL CE 3.5 SP2 installed, I also have 4.0 installed referencing either
    Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll 3.5.1.0
    or
    Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.dll v 4.0.0.0
    I get the same error.

    The path for synchronisation files is “Microsoft Sync Framework\2.0\Runtime\ADO.NET\V3.0\x86\Microsoft.Synchronization.” for the Data.dll, Data.SqlServer.dll and Data.SqlServerCe.dll. The Microsoft.Synchronisation.dll is taken from “Microsoft Sync Framework\2.0\Runtime\x86\”. All my references are x86 dlls. All the runtime versions of the files say they are v2.0.50727 Do you see any conflicts? I would be more than happy to see just a cored out project file so I can review the references used and try to spot any difference.

  3. Dominic S · · Reply

    I reinstalled everything and switched to Sync Framework 2.1 (from 2.0) I tried using all 64 bit drivers with the following files:
    Microsoft.Synchronisation (2.1), Data + Data.SqlServer + Data.SqlServerCe (3.1), with either the CE 3.5.1 or 4.0.

    The new error was “SyncException was unhandled”: “Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0×80040154 (REGDB_E_CLASSNOTREG)).” The inner exception target site is “{IApplicationSyncServices get_ApplicationServices()}”. I switched back to using the 86 versions of the files and I am through the thick of it!

    I am getting exceptions on floating point storage now DbSyncException “The conversion is not supported. [ Type to convert from (if known) = varbinary, Type to convert to (if known) = float ]“. I can only hypothesise about the cause of this error being explained here: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/0d0669f5-c884-4c2e-9d15-15e217de63d1

    I just tested and confirmed that using a decimal rather than a double or float fixes this issue. I have synchronised!!! Thank you jtabadero!

    1. good to hear you got it up and running…

  4. I have also found that you need to set the CreateDatabaseIfNotExists property to false on the SqlCeClientSyncProvider if you use the |DataDirectory| macro in the connection string.

    Why? Because the SqlCeClientProvider uses the SqlCeConnection.Database property and File.Exists to check for the existence of the sdf file. The version 3.5 SqlCeConnection.Database property resolves the |DataDirectory| macro so File.Exists successfully detects the sdf file. However, the version 4.0 SqlCeConnection.Database property still contains the |DataDirectory| macro. File.Exists does not successfully detect the sdf file because it is ignorant of the |DataDirectory| macro. Then failure occurs when the SqlCeClientProvider attempts to create a database and it is already there.

  5. Rick Cloud · · Reply

    I am trying to do the same, and I get an ‘InvalidCast’ exception… with the message saying ‘Microsoft.Synchronization.KnowledgeSyncProvider’

    Any thoughts?

    1. not sure without having a look at the stack trace…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 186 other followers

%d bloggers like this: