locked
Batching in Peer to Peer scenario RRS feed

  • Question

  • I have a scenario where I'm trying to sync two sql server dbs.  The remote peer is accessed through a WCF web service.  Everything is working fine but I've started to run into problems with large databases, especially on the initial sync.  From what I've read it looks like batching is not supported out of the box in this scenario.  Is there any kind of a work around for this?  I've tried modifying the results returned by the SelectIncrementalChangesCommand by adding some additional parameters to filter the result set but the framework ignores the rows after the intial sync. 

     

    I'm using an SP that includes the two required parameters, @sync_min_timestamp and @sync_metadata_only plus two additional parameters, @max_records and @min_id.  I’m using a decoupled tracking system.  The max_records param is set to 500 and the SP simply executes a select query similar to this:

     

    select top (@max_records)

          <...sometable fields + sometrackingtable fields...>

    from <...sometable joined to sometrackingtable t...>

    where (t.sync_row_timestamp > @sync_min_timestamp) or (t.ID > @MIN_ID)

    order by t.ID asc

     

    I’ve turned on tracing and the output looks something like this:

     

    Change ignored for row with PK: ID="53b40c92-c56f-4da1-a29a-d41e6b14a7cc" on db1

              Mapped knowledge already contains UpdateVersion: (0,104015)

     

    Is there a way to get around this?  Am I going to run into problems with this kind of solution?  Thanks for any help you can provide.

     

    • Moved by Tina_Tian Friday, April 22, 2011 8:00 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, September 23, 2008 4:09 PM

Answers

  • Hi Bar1,

    Batching is not supported in peer to peer scenarios for the v1 release. You solution is on the right track but unfortunately it requires a little bit of support from the core runtime to enable this, which obvioulsy is not available right now. When the source is enumerating changes it sends to the destination a MadeWIthKnowledge which tells the destination the knowledge on the source when the changes were enumerated. Usually this MadeWithKnowledge is computed by reading the current scope from the database and then setting the local tick count to the value returned by the SelectNewTimestampCommand query. The destiantion will combine this MWK with its local knowledge and store it on a successful apply. In the current release, db provider expects changes coming in one batch. So you would have to issue multiple Synchronize() calls on the orchestrator to send all changes. But the knowledge sent is not "batch enabled" and so for the second sync onwards your enum query would return no results or results returned is ignored as the destination knowledge implies that it alread knows about that change.

     

    Here is what is happening in your case.

     

    1. Your database current tickcount is 1000 (SelectNewTimestampCommand)

    2. The destination doesnt know anything about you so all cahnges up to 1000 has to be sent.

    3. Your special sp only selects 100 rows (from 0 to 10) ordered by individual tables PK.

    4. Destination gets only 100 rows but the MadeWithKnowledge says Source:1000.

    5. it applies the 100 changes and updates its local knoledge to local:X,Source:1000.

    6. Sync completes.

    7. You initiate another sync, source gets destination knowledge as source:1000 and will only enumerate changes after 1000 or ids greater than 100.

    8. For every row selected, runtime determines if the destination already knows this  by doing a Knowledge.Contains check. In this case it comes back as true and the row is skipped.

    9. So your knowledge says it knows up to 1000 for *all* items but in reality it ionly knows up to 1000 for the first 100 Id's.

    10. For your above case to work the knowledge should say this source:0;(Ids1 to Id100:1000). i.e knowledge of source is 0 but for ids 1 through 100 its 1000.

     

    Batching is a hard problem and to avoid data loss the runtime inherently should be "batching" aware. If your knowledge is not accurate you would miss rows and in case of peer to peer this data loss is propogated to other peers as well resulting in a horribly out of sync topology.

     

    Then there are other batching related issues such as PK, FK deletes not in the same batch (hence the PK delete on destination failing) and PK-FK inserts not in same batch (so your data is not logically correct) and your knowledge having lot of individual item exceptions due to PK-FK deletes failing and so on.

     

    Batching is already on our high priority todo list and we will share with you the dates of the next release once we have concrete details.

    Tuesday, September 23, 2008 5:26 PM
    Moderator

All replies

  • Hi Bar1,

    Batching is not supported in peer to peer scenarios for the v1 release. You solution is on the right track but unfortunately it requires a little bit of support from the core runtime to enable this, which obvioulsy is not available right now. When the source is enumerating changes it sends to the destination a MadeWIthKnowledge which tells the destination the knowledge on the source when the changes were enumerated. Usually this MadeWithKnowledge is computed by reading the current scope from the database and then setting the local tick count to the value returned by the SelectNewTimestampCommand query. The destiantion will combine this MWK with its local knowledge and store it on a successful apply. In the current release, db provider expects changes coming in one batch. So you would have to issue multiple Synchronize() calls on the orchestrator to send all changes. But the knowledge sent is not "batch enabled" and so for the second sync onwards your enum query would return no results or results returned is ignored as the destination knowledge implies that it alread knows about that change.

     

    Here is what is happening in your case.

     

    1. Your database current tickcount is 1000 (SelectNewTimestampCommand)

    2. The destination doesnt know anything about you so all cahnges up to 1000 has to be sent.

    3. Your special sp only selects 100 rows (from 0 to 10) ordered by individual tables PK.

    4. Destination gets only 100 rows but the MadeWithKnowledge says Source:1000.

    5. it applies the 100 changes and updates its local knoledge to local:X,Source:1000.

    6. Sync completes.

    7. You initiate another sync, source gets destination knowledge as source:1000 and will only enumerate changes after 1000 or ids greater than 100.

    8. For every row selected, runtime determines if the destination already knows this  by doing a Knowledge.Contains check. In this case it comes back as true and the row is skipped.

    9. So your knowledge says it knows up to 1000 for *all* items but in reality it ionly knows up to 1000 for the first 100 Id's.

    10. For your above case to work the knowledge should say this source:0;(Ids1 to Id100:1000). i.e knowledge of source is 0 but for ids 1 through 100 its 1000.

     

    Batching is a hard problem and to avoid data loss the runtime inherently should be "batching" aware. If your knowledge is not accurate you would miss rows and in case of peer to peer this data loss is propogated to other peers as well resulting in a horribly out of sync topology.

     

    Then there are other batching related issues such as PK, FK deletes not in the same batch (hence the PK delete on destination failing) and PK-FK inserts not in same batch (so your data is not logically correct) and your knowledge having lot of individual item exceptions due to PK-FK deletes failing and so on.

     

    Batching is already on our high priority todo list and we will share with you the dates of the next release once we have concrete details.

    Tuesday, September 23, 2008 5:26 PM
    Moderator
  • I usually remove the dataset from the DbSyncContext and chunk the tables/rows then re-create the DbSyncContext after you received all the tables/rows from the wcf.  I am using this approach since CTP2.

     

    If you dont chunk the dataset you will likely get Memory Insufficient error for over 100K change rows.

     

     

    Tuesday, September 23, 2008 8:27 PM
  • Udai,

    You would hit the memory error way before WCF service gets hold of the actual DbSyncContext object. If there is too much changes the runtime will run out of memory when its trying to load it.

    Wednesday, September 24, 2008 3:40 AM
    Moderator
  • Hi mahjayar,

    Actually i did see the runtime error if i transfer too much of changes.  This approach helps me to transfer around 700K changes otherwise i ran into memory insufficient error even for 40K rows.  Most of the time it fails on serialization.  My sync service syncs around 200 Tables per database.

     

    I am looking forward to BATCHING solutions.

     

    Current sync engine runs first all the deletes then inserts and updates...how about change this process to run in the order of rowversion.  Do you see any drawback on this approach?

    Wednesday, September 24, 2008 1:50 PM
  • Thanks to both of you for the help.  It sounds like we really need the batching support built into the Framework.  The sooner Microsoft gets this done the better.  I'm marking the original reply as the answer even though I still haven't really solved the problem.

     

    Wednesday, September 24, 2008 7:31 PM
  • hello Udai

    i am not much familiar with  sync services of microsoft. how to start developing of this. can u please help me. its very urgent.

    i developed P2P sync with CTP1. but i have not very depth knowledge of CTP2.
    please you give reference/URL where i will get help.
    Help me  its urgent.thanks

    thanks and regards
    Luxmi
    Thursday, October 23, 2008 5:09 AM
  • hello

    i am not much familiar with  sync services of microsoft. how to start developing of this. can u please help me. its very urgent.

    i developed P2P sync with CTP1. but i have not very depth knowledge of CTP2.
    please you give reference/URL where i will get help.

    thanks and regards
    Luxmi
    Thursday, October 23, 2008 12:14 PM