Sync framework - choose your primary keys type carefully

Do you know the Sync Framework? This is an amazing framework that enables roaming, sharing of data, and taking data offline !

One usage you can imagine is to sync some local clients databases with a big distant one. Each client can take its data with him and each client was able to edit/create/delete data.

The Scenarios Where the Problem Occurs

So where is the problem with the primary keys? Often, when you design your database, you set the types of the primary keys of each row as Int. This integer is when set as autoincremented, and everything works fine for you.

You can't do that when you want to use the sync framework in bidirectional mode. Let me explain why. Imagine this scenario, where we have a database storing apples:

1. There are 10 apples in your database and each of them is called by its number
2. All clients database are synched and client 1 wants to create an apple. He creates one and the apple is so called "Apple 11" : there are only 10 apples in his local database and the autoincrement rule gives him the next available number which is 11.
3. Simultaneously client 2 wants to also create an apple. He creates one and the apple is so called "Apple 11": there are only 10 apples in his local database and the autoincrement rule gives him the next available number which is also 11.
4. Both the clients try to synch with the main database: there are 2" apples 11" __

What would have Newton said? This: Auto incrementation does not work in asynchronous scenarios.

Actually the data stored would have been really more complex in real life than apples and each creation of object makes a lot of links which make this problem very complicated to solve.

The Solution

As you can see, the guilty part of the scenario is the auto-incrementation of the primary key. As we are in an asynchronous scenario, we can't use this pattern to create unique identifiers. Unique identifier is in fact the key of our problem: as pointed out by the MSDN, the solution is to use this type instead of auto-incremented integers for the primary keys of your rows. A new value can be generated on your SQL by using NEWID() or NEWSEQUENTIALID().

Sync framework with bi-directional sync ==> Use unique identifier (GUID) instead of auto-incremented integers !

Useful Links

• Sync Framework Tutorial – Part #3 – Preparing the server database
• Microsoft Sync Framework on MSDN
• Microsoft Sync Framework Tips

CodeProject