Hi,
This is a very common issue that arises when people take an existing database schema and take it as-is in an offline mode. The biggest issue that I expect you will come against is the problem of data conflicts. For example let say the client adds a new row and that master (primary) key gets set to ‘10’ then at the same time the server adds a new row and it also gets set to ‘10’. Then when you execute sync you get a data conflict (because you can’t have more than one row with the same primary key value). Although the Sync Framework allows you to handle data conflicts it is best to avoid them wherever possible.
There are a number of ways that you might choose to ensure uniqueness of these keys. Here are a few:
UUID/GUIDs: These are data types that can be used in databases like SQL Server to ensure a unique value for your keys.
Primary Key Pools: In the even you have to use integers as your key sometimes people create primary key pool tables where you allocate a range of integers for each client that get synchronized down to the client. Whenever a new row is added it takes one of the integers from this key pool table and uses that. The downside with this method is that you need a method for replensishing the keys for the users when the pool gets low and you also need to ensure there are enough keys assigned to the client that they do not run out until the next time it can be replenished during sync.
Compound Keys: Rather than just using the integer as your key perhaps use that column along with another. Perhaps a key that indicates the machine that was used to insert the row. That way if the same id is used you can ensure it is unique because of the machine name.
I am sure there are numerous other methods, but hopefully that will help to get you started…
Liam
Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
