none
MS Sync Framework 2.1 Custom batching for Download only

    Question

  • I am using MS Sync Framework 2.1 and impletemented custom batching using SQL Change Tracking

    Database Configuration Configuration

    1. Download only Synchronization

    2. Change Tracking Enabled

    3. Rention period set to 10 days

    Issue Description :

    when a new client connect to server with in 10 days , all the data is downloaded correctly.   Assume, when a client connect after 10th day, as the change tracking is cleaned up, the records for which change tracking clean up is not downloaded.  After debugging, we found that it is due to the custom batching implementation through stored procedure. 

    Troubleshooting we have done:

    1. Added the if block to check whether it is first sync and selected the records from the base table by applying left outer join.

    issue with this approach is , it is not using batching and it brings back all the records.

    2. to address the above issue, added top 1000 records, but it brings backs only 1000 records and if change tracking is cleaned up for more than 1000 records will not work.

    Please suggest the better way to implement custom batching to get all the records downloaded with the batching approach. 

    alternate solution is to increase the rention to longer period, but this approach will have performance impact.

    Sample Code Snippet :

      -- 1.1 IF FIRST TIME SYNCHRONIZATION , THE SCHEMA AND ALL ROWS ARE SELECTED DIRECTLY FROM BASE TABLE
      IF @sync_initialized = 0
      BEGIN
       SELECT TOP 1000 CLHL.[ID],CLHL.[BlockingReason],CLHL.[BlockingSeqNumber],CLHL.[CardSerialNumber]
       FROM dbo.ClosedLoopHotList CLHL LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.ClosedLoopHotList, @sync_last_received_anchor) CT
       ON CT.[ID] = CLHL.[ID]
       ORDER BY CLHL.ID ASC
      END
      ELSE
      BEGIN
      -- LATER SYNCHRONIZATIONS, NEW INSERTED ROWS ARE SELECTED BY PERFORMING AN INNER JOIN BETWEEN BASE TABLE AND ITS CHANGE TRACING TABLE.
       SELECT CLHL.[ID],CLHL.[BlockingReason],CLHL.[BlockingSeqNumber],CLHL.[CardSerialNumber]
       FROM dbo.ClosedLoopHotList CLHL JOIN CHANGETABLE(CHANGES dbo.ClosedLoopHotList, @sync_last_received_anchor) CT
       ON CT.[ID] = CLHL.[ID]
       WHERE (CT.SYS_CHANGE_OPERATION = 'I'
       AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)
      END

    Thursday, March 27, 2014 9:46 PM