locked
how to handle identity columns and uniqueidentifiers when synching. RRS feed

  • Question

  • I have an application that has utilized identity column as the primary key in several tables. Now, we are writing the application to allow synching from remote clients. I have read several articles describing the use of uniqueidentifier columns to uniquely identify a row amoung all the instances of an application. So, I am trying to implement that. Many people have advised against using the uniqueidentifier column as the primary key, and my application needs to display a human-friendly (integer) ID for the items it displays so, I want to keep the primary keys I have on the server, and add a unique identified column to handle identification of each row amoung the server and all clients. So far, so good.

    Here is my question: When a record has been added by a client, the client will create the primary key (int) and the uniqueidentifier. When the client synchs to the server, I will "upload" all the columns in the record, except for the integer ID column. The server will create an integer ID (identity, primary key) value for the record when it is inserted into the table on the server. How do I update the client version of the record to reflect the newly assigned ID column, overwriting the original value assigned on the client?

    Am I going about this process in the right way?

    Thanks for any advice you can give.

    Friday, August 5, 2011 5:58 PM

Answers

  • the link youre following uses the older offline provider (SqlCeClientSyncProvider/DbServerSyncProvider).

    the one in my post uses the newer SqlCeClientSyncProvider/SqlSyncProvider.

    the former is for hub-spoke type of sync where multiple clients sync to a central server, the latter can be work in hub-spoke and peer-to-peer scenario.

    the former uses anchor to store the timestamp when it last synched, the latter uses sync knowledge to store what has been synched.

    if your client is not Sql Ce, the older offline provider only comes OOTB with Sql Ce as the client.

    • Marked as answer by rknowles Wednesday, August 10, 2011 12:55 PM
    Tuesday, August 9, 2011 1:02 PM

All replies

  • you can get Sync Framework to think that the PK is the uniqueidentifier, see: Part 3 – Synchronizing tables where Client and Server Primary Keys are different

    but the problem in your scenario is that Identity columns cannot be updated. So, if in your client, ID is an identity column, you wont be able to update it with the server-side generated ID not unless you remove it as an identity property.

    Second, your client will not get the updated ID column from the server not unless the server row is updated. So after uploading the row and having the server generate an ID during the sync process, the rows has to be updated for it to be detected as a change on the next download.

    i would suggest you remove the identity property on the client ID (you dont need the client side generated ID anyway) and set the PK to the uniqueidentifier. then you need to have another step that does a dummy update on the newly inserted rows on the server to reflect that they've been changed so that the next download picks up the rows with the server generated ID.

    Monday, August 8, 2011 1:44 AM
  • JuneT,

    Thank you for your response. The article you referenced looks like it is the right solution. I will need to figure out how to handle the human-friendly ID for the items that are added via a disconnected client...maybe I can just display a "pending" message for those items that do not have an integer ID, until they are sync'ed to the server, and are assigned ID...or something. I will figure that out.

    My problem is this...since you posted your answer yesterday, I have been trying to figure out how to incorporate the objects used in the code example you referenced (http://jtabadero.wordpress.com/2011/07/20/part-3-synchronizing-tables-where-client-and-server-primary-keys-are-different/)  into the code I already have. I have been working from this example:

    http://msdn.microsoft.com/en-us/library/bb726007.aspx

    and the two approaches don't seem to have any objects in common. 

    Do you have any advice as to how I can incorporate the SqlSyncScopeProvisioning object and the SqlSyncDescriptionBuilder into the methodology described in the example article I am using on msdn?

    Thanks for any advice you can give. 

     

    Tuesday, August 9, 2011 12:54 PM
  • the link youre following uses the older offline provider (SqlCeClientSyncProvider/DbServerSyncProvider).

    the one in my post uses the newer SqlCeClientSyncProvider/SqlSyncProvider.

    the former is for hub-spoke type of sync where multiple clients sync to a central server, the latter can be work in hub-spoke and peer-to-peer scenario.

    the former uses anchor to store the timestamp when it last synched, the latter uses sync knowledge to store what has been synched.

    if your client is not Sql Ce, the older offline provider only comes OOTB with Sql Ce as the client.

    • Marked as answer by rknowles Wednesday, August 10, 2011 12:55 PM
    Tuesday, August 9, 2011 1:02 PM
  • So, it sounds like I will be completely re-writing by sync code.

    Good grief.

    Thank you for the responses you have given me. They have been a great help, and have pointed me in the right direction.

    I have a lot of work to do, and a lot that I still need to figure out. This feels like I am in a house of mirrors. I have been working on this for a couple of weeks now, and I am really no farther along than when I started. But, I would be in even worse shape without your help. 

    Thanks again.

    Wednesday, August 10, 2011 12:55 PM