Our work with the MS Sync Framework is going very well and the v2 release could not have been better timed! We are syncing data from a web-based application (which runs under SQL Server 2008) to a local SQL Server Compact Edition (3.5 sp1) cache. It has taken us a couple of weeks to get up to speed with everything. There are a lot of steps involved in our scenario:
- Create a Sync Framework project
- Get the sync working between databases on a local machine
- Switch to using a WCF web service call for the sync
- Hack away at the original db schema until it works (primary key issues, clustered indexes etc)
- Choose the right security mode for the WCF call (nightmare)
- Create a new web service on the test server for the WCF call
- Create a Windows app to Sync to
- etc etc etc
One of the biggest challenges is retrofitting the Sync Framework on to an existing database schema. My general advice (having been through the pain) would be:
USE GUIDS FOR ALL YOUR PRIMARY KEYS!
We did not do this in our production database (we didn't know about the need to Sync way back then) and it adds some interesting technical issues! We use Integers with Identity set for primary keys. I should also mention that are attempting bi-directional synchronisation... The main problem is that after the initial sync, the seeds of identity fields are all set to 1. This means that all of the clients and the server will insert records using the same primary key values! The Sync framework cannot handle this, so we need to make a manual intervention.
The solution is to seed the server at a high value and the clients at lower values. We are using 32 bit integers for PKs, so we simply allocate a client id to each client (we actually allocate a low number and shift it left 24 bits to give the new seed). We tries some other schemes such as having the server start at 1, but we had problems with syncing.
The other problem is that the documentation for the Sync Framework is not great at the moment. We spent a lot of time working some basic stuff out for ourselves. If I ever get some free time I will do a tutorial based on our product going from nothing to have a complete and working Sync Framework application... It might happen...
Overall, working with the Sync framework is good, but you need to be prepared to get technical and work through problems yourself as the documentation is still a little light!