locked
Will Sync Framework fit in my scenario? RRS feed

  • Question

  • Hi Experts,

    I have a server and two clients. All the machines are having the same database schema. Let us take only two tables for example. Order and OrderDetails. Both are having IDENTITY columns as OrderID and OrderDetailsID. By the end of the day, I want to upload Order and OrderDetails information to server from both the client machines. I want to keep the IDs to be created newly in Orders table and the same OrderID should reflect in OrderDetails table as well.

    Ex.
    Client1                                            Client2
    Orders Table
    OrderID                                            OrderID
    1                                                        1
    2                                                        2

    Order Details Table
    OrderDetailID    OrderID                OrderDetailID        OrderID
             1                   1                            1                        1
             2                   1                            2                        2
             3                   2                            3                        2
    ------------------------------------------------------------------------------
    After Sync, I want
    Server
    Orders Table
    OrderID
    1            -From Client1
    2
    3            -From Client2
    4
    OrderDetails Table
    OrderDetailID                OrderID
            1                                1
            2                                1
            3                                2
            4                                3
            5                                4
            6                                4
    OrderIDs got rearranged based on the new IDENTITY.

    Can I achieve this by using Sync Framework?
    • Moved by Hengzhe Li Friday, April 22, 2011 5:24 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, March 12, 2009 4:14 AM

Answers

  • this is a typical case for Identity range management in sync. unfortunately the sync services doesn't support ID range management out-of-box. however, it is capable to allow users to implement the support on this case:

    here are some guidlines:
    1. set a ID range table on the server and also keep track the next Range to send out to clients.
    2. each clients coming to get its own set of ID range
    3. on the sync adapter, make sure the IDENTITY_INSERT is set to ON for the insert and set back to OOF after the insert query.
    4. if you need to take care of cases where client ID range need to be re-assigned ( i.e. it is full ), you need put extra tracking logic on the server to achieve this.

    hope this helps.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 12, 2009 8:31 PM
    Moderator

All replies

  • this is a typical case for Identity range management in sync. unfortunately the sync services doesn't support ID range management out-of-box. however, it is capable to allow users to implement the support on this case:

    here are some guidlines:
    1. set a ID range table on the server and also keep track the next Range to send out to clients.
    2. each clients coming to get its own set of ID range
    3. on the sync adapter, make sure the IDENTITY_INSERT is set to ON for the insert and set back to OOF after the insert query.
    4. if you need to take care of cases where client ID range need to be re-assigned ( i.e. it is full ), you need put extra tracking logic on the server to achieve this.

    hope this helps.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 12, 2009 8:31 PM
    Moderator
  • Thanks YunWen.  Though it will cause problem when I go for Download. Let me think another way to achieve this. Thanks a lot.
    Friday, March 13, 2009 4:12 AM