Matching unique ID in local storage to server ID

Aug 17, 2011 at 2:41 PM
Edited Aug 17, 2011 at 2:43 PM

If I follow the logic in the sample solution correctly, the EntityGuid is used as the key by which to identify a offline stored record in the ObjectStore and match it up to changes being received for the same record from the server.  From what I can see this will work fine for existing records where the data is retrieved from the server and the EntityGuid is thus already assigned.  However, when creating new records on the clients this is problematic.   Fortunately you can work around the issue if the data type is a Guid as it is guaranteed to be unique and you can therefore generate it on the client for the new record, pass it through to the server and the server will use this EntityGuid as the key for the record in the DB.  Subsequent fetches for the record will therefore be matched up with the record store offline and the synching can happen without any problems.

However, this ONLY works fine if the your id can be generated on the client.  Unfortunately for us, our unique ID's are integer based and only assigned on the server for new records when they are uploaded. This causes a few problems:

  1. Because the ID is only assigned on the server, we do not have a key to store new, offline created records in the offline store
  2. Because the ID is only assigned on the server, we don't have a mechanism to match the server record with the local store record for sync purposes
  3. Because these ID's typically overlap for different kinds of records, we cannot use an integer value as the mechanism for identifying a record in the object store

I've been contemplating different solutions to the problem, but keep getting stuck with having to use an identifier like a Guid that can be assigned on the client, send through to the server and persisted in the DB for retrieval at later points in the time thus making it possible to sync up the record in the local, offline store with the data from the server.

Does anybody know of an alternative mechanism to accomplish the same?  Changing the server side DB to now include a SyncId in the different tables is quite an intrusive change.  If this the only way to accomplish it?

Thx

Coordinator
Aug 18, 2011 at 6:53 AM

You just ran into the first big dilemma for Occasionally Connected scenarios.  This is a larger discussion that has many solutions, many of which are far from perfect.  Even while coding this demo, my co-author, Steve Lasker, and I talked about this issue every other day or so.  Here is a breakdown of the common options you may want to consider:

  • The Sequence Allocation method.  You give each client a 'range' of identity values that they can use and you can assign these ID values on the client without worrying about duplicates
    • Pros
      • Ability to assign easy to read ID values on the client
      • No need to change your database except one or two tables to track what clients have what ranges allocated
      • Users get to see a final value while disconnected, such as "Order Number: 105"
    • Cons
      • You have to code the logic to allocate ranges of IDs to clients (can be simple or complex based on the number of clients)
      • Your IDs are no longer in sequential order.  For example Order 105 could be placed AFTER Order 201
      • Hard to scale if you talking about large amounts of clients (1000+)
  • The RowGuid method.  You add a 'RowGuid' column to your SQL table (like you mentioned above as a SyncId)
    • Pros
      • Easy to code, entities get a RowGuid on the client and get assigned their 'real' ID when they sync
      • IDs stay sequential
    • Cons
      • Requires a database change
      • Users don't get to see a final value until they connect, meaning you will likely cannot use your ID value as your 'Order Number' values
  • The GUID method.  You use Guids as your ID value
    • Pros
      • Easy to code
      • Awesome scaling
    • Cons
      • Hard to read
      • Hard to convert existing databases over
      • Requires some SQL skill to keep performance up (fragmented indexes, slow non-sequential inserting, clustered-index size slowing joins) 

As a note, the 'RowGuid' method is the main method used for Merge Replication for SQL Server, and there is a column attribute to define formal RowGuids in SQL 2000+.

Also, here is a short article from MSFT that I found about this dilemma:  http://msdn.microsoft.com/en-us/library/bb726011.aspx

Jason R. Shaver

Aug 18, 2011 at 7:14 AM

Thanks Jason

The Sequential Allocation method is not going to work for us.  ID's are assigned server side only and we really have no idea of how many disconnected users we will have.  We also don't want to introduce the additional complexity w.r.t ID management that this approach requires and prefer the ID's to stay sequential as well.  Our users also don't worry about the ID fields as these are typically not visible to them.  We make use of a Code field to "visually identify" the record for the user.

So we'll investigate the RowGuid vs GUID approach a bit more.  I'll let you know which option we decide to use.

Thx again

Sep 6, 2011 at 6:16 PM

Another alternative is to use temporary client generated keys that are replaced when new records are synched to the server with the actual key. Typically server assigned keys start at 1 and count up from there. What I do is assign keys client side starting at -1 and counting down. Since the temporary key will never collide with a server side key this seems to be a pretty safe thing to do. The hard part is synching with the server. If you are using WCF RIA Services and Entity Framework then getting the correct server side keys is handled automatically, I am not sure about solutions that are not using Entity Framework.