locked
Using Identity Column as Primary Key.... RRS feed

  • Question

  • We are using Sync framework in a Silverlight based LOB application. The tables that we want to synchronize have identity column as primary key. In offline mode, if the client creates a new record, should the value for primary key specified by client? If the client does not specify the value for primary key, when the data is synced, will SQL automatically populate the value for the primary key (being an identity column)?

    Tuesday, October 12, 2010 5:31 PM

Answers

  • Here is how a crude range management system works.

    1. When a new client is ready to connect (the app must know when a client is brand new) it pings the server and asks for a new range of ids.

    2. The server issues it a new range and makes sure that no other client gets it. This is normally done by persisting the last issued range to a client and the server issues new range from that point on. You want to make sure that no two clients can get the same new range by serializing all calls for get_new_range on server.

    3. The clients makes inserts and uses ids from that range.

    4. When the client exhausts its range the app prevents any new inserts from being made to the app until a new range is acquired.

    Hope this helps.


    Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
    • Marked as answer by arasheed Tuesday, October 19, 2010 6:36 PM
    Tuesday, October 19, 2010 4:55 PM

All replies

  • Yes, SyncFx does support identity columns as primary keys. What ever values the client specifies will be inserted in the backend with Identity_Insert mode set to ON. However this does raise the scenario of identity range managements if you have multiple clients uploading new inserts. Without an explicit range management multiple clients may raise insert-insert conflicts. You can add a simple solution yourself to assign ranges of ids to clients when they bootstrap so they dont collide.

     


    Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
    Tuesday, October 12, 2010 9:25 PM
  • Thanks for your feedback. Can you please elaborate on the range management techniq in Silverlight scenairo? Will this be user specific? In our scenario, there could be multiple (may be millions!) users accesing the application over web.
    Tuesday, October 12, 2010 10:29 PM
  • Here is how a crude range management system works.

    1. When a new client is ready to connect (the app must know when a client is brand new) it pings the server and asks for a new range of ids.

    2. The server issues it a new range and makes sure that no other client gets it. This is normally done by persisting the last issued range to a client and the server issues new range from that point on. You want to make sure that no two clients can get the same new range by serializing all calls for get_new_range on server.

    3. The clients makes inserts and uses ids from that range.

    4. When the client exhausts its range the app prevents any new inserts from being made to the app until a new range is acquired.

    Hope this helps.


    Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
    • Marked as answer by arasheed Tuesday, October 19, 2010 6:36 PM
    Tuesday, October 19, 2010 4:55 PM
  • Thank you very much
    Tuesday, October 19, 2010 6:36 PM
  • Hi,

    I have the same issue with html5 offline client using sync framework 4.0. Is the tool (SyncSvcUtilHelper.exe) provides that facility of specifying the range for identity column insertions? how to set that? If you have any implemented scenario can you please give me the details?

    Friday, May 6, 2011 4:34 AM
  • Identity key management is not addressed  by Sync framework. The "range" management is a custom solution that you can build per Maheshwar's response. Btw, can't you use GUID as the primary key?

    Thanks

    Rasheed

    Monday, May 9, 2011 2:12 AM
  • Thanks for the response.

    We have the system already implemented I have to do offline application for one module so it is not possible to use GUID.I am trying by changing the stored procedure generated by (SyncSvcUtilHelper.exe) tool but some where else it is failing I am checking that. And one more thing I am thinking it may be quick and easy solution for my problem.If I succeed I'll post the changes I did.

    Mean while if you get any information on range management system can you please update me I have to complete it in 2 days.

    Thanks

    Savitri

    Monday, May 9, 2011 4:48 AM
  • Hi,

    Without changing the Identity column type and without using the CRUD range management system.

    I changed the generated stored procedures like BulkInsert, BulkUpdate, Insert, Update of corresponding table.I modiifed the procedure by not passing the PKId value and made it auto insert as identity value and in client side I am syncing once again with the updated database.

    Please give me your feed back If there a chance to get any issues.

    I knew that the stored procedures need to be altered for each time whenever we provision the database.

     

    Thanks

    Savitri

    Monday, May 16, 2011 11:45 AM
  • you manage to get the client to upload but you're client and server are now out of sync since the client  and server PKs no longer correspond to each other. how do you trace back now a record in your server to the corresponding client record that was uploaded? if later on you decide to do bidirectional sync, how do you cascade an update on the server to the corresponding row in the client?
    Monday, May 16, 2011 12:25 PM
  • you could implement on the client something like isNew, and when you send such an entry to the server, delete it on the client and get the inserted one back... could work, but i dont know if you would actually get the inserted one back. maybe, you have to sync the whole db again with a clean blob and a clead db. that could work if you are dealing with small amounts of data, but for large date i think that kills it.

    Wednesday, May 18, 2011 10:09 AM