none
Synchronizing database with custom master key RRS feed

  • Question

  • Hi there, I was wondering if database synchronization can help me in this scenario:

    I've a SQL Server 2008 database in my server, all my tables are related by its master key. The master key is numeric, but it doesn't have auto increment, so, in my code I increment my index by a SQL query like this:

    SELECT ISNULL(MAX(Id)+1,1) FROM TableA

    So, in this way I get the next index. When I go to offline mode, I can get my next index in the same way (at my client) but what's going to happen when my local application goes back to the server, how can I handle my index, or SQL Server does?

    Any help will be great!!

    Greetings!!
    Tuesday, January 26, 2010 12:01 AM

Answers

  • 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/
    Tuesday, January 26, 2010 3:47 AM