Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
What's the normal frequency for periodic data sync using Sync Framework 2.1? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    We have a system with three terminals. Each terminal is installed with SQL 2008 Express instance. One of the terminals is acting as Master and the other ones as slave. The master will initiate the sync cycle when the time comes. We need to sync the data among the terminals as real-time as possible. We've divided the tables into three types of scopes with different sync frequencies according to the nature of the data stored in those tables need to be synced. The most frequent ones we're syncing at 60 seconds per cycle. The medium ones we're syncing at 90 seconds cycle. The least frequent once we're syncing at 10 minutes cycle.

    So, my question is are we doing the sync too frequently? How frequent is normal? 

    We also noticed that some of the sync framework tracking tables are fragmented severely. The highest fragmentation rate could be as high as 98%. We've tried to reorganize the index. It would help for a while. But within a day, it will go up again. Currently, we've to do the index reorganization daily. Even with that, it could still go up. Is this related to the high frequency of sync?

    Friday, May 25, 2012 7:31 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    there is no hard and fast rule on the frequency of sync...you will have to find the best one that works for you...

    typically, you would consider the frequency of updates, the volume of updates, how much latency you can afford and the amount of time for the sync to complete...

    the less frequent the updates and less volume, the less frequent you can do the sync...

    with regards to the fragmentation, i suggest you specify a fillfactor for the index...

    • Edited by JuneT Saturday, May 26, 2012 3:56 AM wrong info
    • Marked as answer by bli88 Sunday, May 27, 2012 12:33 PM
    Saturday, May 26, 2012 1:15 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    there is no hard and fast rule on the frequency of sync...you will have to find the best one that works for you...

    typically, you would consider the frequency of updates, the volume of updates, how much latency you can afford and the amount of time for the sync to complete...

    the less frequent the updates and less volume, the less frequent you can do the sync...

    with regards to the fragmentation, i suggest you specify a fillfactor for the index...

    • Edited by JuneT Saturday, May 26, 2012 3:56 AM wrong info
    • Marked as answer by bli88 Sunday, May 27, 2012 12:33 PM
    Saturday, May 26, 2012 1:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    JuneT,

    Thanks so much for the reply.

    The PK for our sync tables are not using identity column. The PK's for those tables are a combination of few columns and none of these columns are unique by themselves. Does this make any difference?

    Saturday, May 26, 2012 2:40 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    yes, it will make a difference...afaik, the PK of your base table becomes the PK of your tracking table... again, specifying a fill factor would help reduce the fragmentation...but you still have to do regular index maintenance to rebuild kit every now and then...

    Saturday, May 26, 2012 3:55 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    How do we know what's our current fillfactor? What's the default fill factor if not specified in the creation of the index?

    Thanks!

    Saturday, May 26, 2012 10:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    see: http://msdn.microsoft.com/en-us/library/ms177459.aspx
    Sunday, May 27, 2012 2:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks!
    Sunday, May 27, 2012 12:33 PM