I wrote a sample application that illustrates how the SQL Compact (what Microsoft calls “Microsoft SQL Server Compact Edition”) database used with the Local Data Cache gets out of sync (which I would expect it to) with the host database and how the application can get it back in sync. However, it seems that when they are really out of sync, the only thing to do is to clear out the client database and start over.
Things get out of sync because SQL Server does not keep changes indefinitely, so it returns:
Sync Exception:
“Unable to enumerate changes at the DbServerSyncProvider for table 'Customers' in synchronization group 'CustomersSyncTableSyncGroup'.”
Inner Exception:
"SQL Server Change Tracking has cleaned up tracking information for table 'dbo.Customers'. To recover from this error, the client must reinitialize its local database and try again"
These exceptions indicate that SQL Server has cleaned up since the last sync. Details here: http://msdn.microsoft.com/en-us/library/cc305322.aspx (also showing the source of the error message).
I was told (by Mr. Erik Ejlskov Jensen) That the retention time is configured through the ALTER DATABASE options:
ALTER DATABASE SyncSamplesDb_ChangeTracking
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
I’ll buy this approach, but I was not sure if I could just drop the SDF database (as in erase the file), recreate it and go on. Apparently you can. While he so kindly pointed me to a KB article http://msdn.microsoft.com/en-us/library/bb726043.aspx, the “Utility” function seemed to do quite a few things that I didn’t need. Thankfully, the fix to the problem is the following code.
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
util.SetClientPassword();
util.RecreateClientDatabase();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
The entire Utility class is here: http://msdn.microsoft.com/en-us/library/bb726022.aspx
hth
Bill
Its a really good and helpful post, but I have a simple question; Why are we utilizing a SQL server for data caching, when we already have a few simpler and more reliable solutions present in the market for the same purpose, and which are providing us with more flexibility, scalability, reliability and performance boost? I will personally suggest NCache, a caching solution for both data and object caching, as well as for session caching.
Do try it out. You can find it @ http://www.alachisoft.com/download.html
Because SQL Server is a well-accepted, high-performance, well-documented and secure platform. Coupled with SQL Server Compact Edition (which is not really SQL Server at all), it makes an farily complex job of setting up bi-di sync easy.