none
Microsoft Sync Framework and Integer as Identifier RRS feed

  • Question

  • I've got to implement data synchronization between a local storage that resides on a mobile device and the server DB.

    The thing is that our DB (which is already in production for many years) all uses an integer as the Id of a record. I'm afraid that changing it to be a GUID is currently not an alternative because it has too many repercussion.

    Does someone has a good workaround this issue?

    Tuesday, July 6, 2010 8:08 PM

Answers

  • I think I have found a way to deal with the ID being integers. 

    I'd have to add a new GUID column on the local and server DB that is unique. Upon insert I ignore the integers ID generate a new ID and do the insert. For parent-child relationship, I'd query the parent GUID to get the real parentID. Something along those lines.

    • Marked as answer by GoDaddy Monday, July 12, 2010 1:44 PM
    Monday, July 12, 2010 1:44 PM

All replies

  • if the current integer ID is guaranteed to be unique, you need not change it. Guids are just a preference to enforce uniqueness.

    see "Selecting an Appropriate Primary Key for a Distributed Environment"  in the documentation

    Wednesday, July 7, 2010 11:13 AM
    Moderator
  • Let's say you have 2 Mobile (Mobile A, Mobile B) with a local db on each device. 

    Each Mobile create a record which the ID is unique on their local DB, but when synchronizing it will not be unique on the SERVER DB. 

    The ID columns are not IDENTITIES, unfortunately the architect went with a TABLE that keeps the next id for each table in the DB. 

    Wednesday, July 7, 2010 12:44 PM
  • you have several options:

    partition the id ranges between clients (e.g., Client A gets 1-10000, Client B gets 10001 to 20000, etc.)

    create a compound key that includes the existing id and the ClientID that Sync Fx assigns to each party participating in Sync.

    is the sync bidirectional, download only, upload only?

    Wednesday, July 7, 2010 12:56 PM
    Moderator
  • Thanks for your suggestions, not sure how it will be applied to our problem. Let me try to make it clearer.

    We have this single table in our server DB that keep tracks of the next Id that will be used upon creating a new record. 

    For example. 

    TableName NextId

    Order 5

    We also have a Desktop application that uses this table to set the ID of a new Order. So something like this : new Order().Id = GetNextId(Order). In our example, the new Order ID will be 5. And the nextId for Order table will be incremented to 6. So everytime we create a new record it goes through that Identifier table.

     

    In our Mobile application, the local db has a Order Table structure similar to the Server. So lets say we have Mobile A and Mobile B. Both contains the record Order 5

    Now when Mobile A and Mobile B creates a new Order, both will create an Order with ID = 6, since the high ID they both have is 5 therefore the next order will be of ID = 6. 

    Obviously when uploading to the server, one of them insert will fail with a PK exception.

    So that's our issue with the ID of integer, I would have like to use GUID for ID, but unfortunately is has too many repercussion. 

     

     

    Back to your proposed solution. 

    1) Partitioning seem to be an ok solution, but there will be some maintenance to do over the id ranges. I'd like to avoid that if possible. So i'm just looking with the current structure that we have can we have a straightfoward solution. 

    2) I don't quite understand the compound key solution. I'd have to change the Mobile Local storage schema? to hold a composite key? I was using the tablecreationoption = DropExistingOrCreateNewTable. For this solution, I'd have to use the UseExistingOrFail? 

     

    This is the workaround I thought of using : Modify the InsertCommand to ignore the ID set by the Client and have the SERVER set the ID using the Identities table. But with this solution, records that have a foreign key to the Order Table will also have to be updated to the new ID ... is there a way to perform that? For example: Mobile A created a new Order with ID = 6 and some OrderLines with OrderID = 6. When the SERVER is going to insert the Order record it will change the ID to the nextId according to the Identities table, but then the OrderLines record also needs to change the OrderID .... is this possible?

     

     

    Wednesday, July 7, 2010 3:25 PM
  • is this an upload only scenario? if it is, then your solution of simply ignoring the client id on server insert should work if there are no parent-child relationships.

    the compound key solution approach i was suggesting is for you to insert in the server also the ClientId of the source client.

    e.g. Client A inserts order id 5, Client A has ClientId of ABC

    on your server you add two columns: clientorderid and clientid, you then insert 5 and ABC respectively

    so your table ends up with the original server version of the orderid plus the clientorderid and clientid

    your child table Order details can be inserted as it is and you can link them back to the parent order by using clientorderid and clientid

    the tables will be synced independently, so if the orderid has changed on the server, how do you marry the orderdetail to the new orderid? by storing the client id and the original orderid, you can link the orderdetails and you can grab the new orderid as well

    but that would require some changes on your app as well.

     

     

     

    Wednesday, July 7, 2010 9:24 PM
    Moderator
  • I think I have found a way to deal with the ID being integers. 

    I'd have to add a new GUID column on the local and server DB that is unique. Upon insert I ignore the integers ID generate a new ID and do the insert. For parent-child relationship, I'd query the parent GUID to get the real parentID. Something along those lines.

    • Marked as answer by GoDaddy Monday, July 12, 2010 1:44 PM
    Monday, July 12, 2010 1:44 PM