Local Data Cache - How do I force an full sync with the database?

Question

I've got a Local Data Cache in my asp.net 3.5 app. I've noticed that once in a while, especially while developing that my local database will get out of sync with the server. I understand why and I can reproduce it with these steps:

1- Start the app
2- Make a change
3- Sync changes with server
4- Start the app again, thus forcing the app to use a new copy of 
the local .SDF file.

The changes I made previously are gone obviously, but when I sync with the server, they are not pulled down. My guess is that somewhere the app isn't detecting that the DB is out of sync.

What I'm wonder is if there is a way to programmatically tell my app to do a full sync with the server. Anyone know?

Answer 1

An answer that I got in the Microsoft Forums may provide a solution to your problem.

If you update the tables in question by setting the primary key to itself, the versions of the records will be incremented and they will be included in the next synchronization. You could use an SQL statement similar to the following to do this.

update MyTable set Id = Id;

Note that there are a couple of differences in your scenario.

1. You will be incrementing the version and not initializing it as in my case.
2. You will be manipulating change tracking on the server and not in the Compact Edition database.

Without having tested it, I think that this solution should work for you - let us know how you get on!

Answer 2

As I noted in the comment, I'm not sure why your local database would be getting overwritten so frequently, but if it does get overwritten, and does not re-sync changes that were previously sent to the server, then the problem is most likely that the anchors are stored on the server. Even though the client database has been "rolled back" to a previous version, the server is not aware of this and really has no way to find out.

Of course it's entirely up to the individual how to actually implement the sync and anchor logic, so I can only guess at the specifics of the problem, but in order solve it, you'll need to do one of two things:

• Have a script that you run on the server that resets the client anchor for the development Client ID that you use; or
• Change the build copy properties ("Copy To Output Directory") of the SDF in your project to "Do Not Copy", so that the local database never gets overwritten.

Hope that helps.

Answer 3

IF the Track Changes Interval option in SQL Services is too small you will also miss updates.

e.g. if its set for 2 days, then a changes happens, then theres 3 days before a sync attempt, you will miss those changes.

While making sure theres an update by changes a record as above, I've found that the best method for me is to store the date last synced for each sync table in a seperate sync table and have that table downloaded completely each time.

Then allow the sync to happen.

Then, for each table that wasnt updated by the sync, check to see if the date is more than the Track Changes interval ago. If it is, change the sync option for that table to complete download and sync again.

A bit faffy, I know...