Sync framework - choose your primary keys type carefully5 March 2010
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 taking its data with him and each client was abble to edit/create/delete data.
The scenarii 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 with you wants to use the sync framework in bidirectionnal mode.
- There is 10 apples in your database and each of them is called by it's number
- 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 is only 10 apples in his local database and the autoincrement rule gives him the next available number which is 11,
- simultanely client 2 want alsos to create an apple. He creates one and the apple is so called "Apple 11" : there is only 10 apples in his local database and the autoincrement rule gives him the next available number which is also 11,
- Both the clients tries to synch with the main database : there is 2" apples 11" __
What would have said Newton ? This : Auto incrementation does not work in asynchronous scenarii
Actually the data stored would have been really more complex in the real life than apples and each creation of object makes a lot of links which make this problem very complicated to solve.
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-directionnal sync ==> Use unique identifier (GUID) instead of auto-incremented integers !
- Sync Framework Tutorial – Part #3 – Preparing the server database
- Microsoft Sync Framework on MSDN
- Microsoft Sync Framework Tips