none
Using related data in custom sync provider RRS feed

  • Question

  • Hi.

    I've been asked to build a custom sync provider but I'm not convinced that the sync framework is the best approach for the requirement. The source of data is a SQL Azure database for which we'd use an out of the box db provider. The other end is an Azure Queue for which we'd write a custom provider. As data in the DB changes messages would be dropped on the queue. Sounds good and interesting so far. My concern is that the data needed to construct queue messages does not reside in one table. In fact, about four tables have to joined in order to constuct a message (that includes nested XML elements). I'm new to the sync framework but assume the framework/sync app is going to pass the custom provider only the changed data. It's custom code so it seems possible but wrong to make subsequent calls from custom queue sync provider back to SQL DB in order to get the other records needed to construct the message.

    The reason the sync framework was suggested is simply that multiple system might change data and this approach would detect changes regardless of how they are made. But I'm wondering if timestamp columns combined with a worker role might be more appropriate.

    Any advice for a newbie on how hard, feasible and appropriate a custom sync provider would be?

    Thanks,

    Dion

    Friday, October 21, 2011 5:41 AM

Answers

  • the database providers (including SqlSyncProvider that works with Sql Azure) tracks, selects and applies changes by table. so if a change is detected for a row in a table, the changes retrieved are only for that table's row.

    i've seen many instances where custom code applies further business rules/logic on the changed rows by retrieving other information from other sources so i dont think its wrong.

    writing a custom provider is not for beginners if i may say.

    if its just a one way sync, i'd say you're better off just using timestamps.

    using a similar approach as the offline providers, every time you sync, you'd grab the changes, send to queue and then record the last timestamp you processed. you can then use this same timestamp to detect new changes by finding timestamps greater than what you previously stored.

    if you go the path of building your own provider, i suggest you look at Sync Framework Toolkit and build a custom offline provider instead.

    • Marked as answer by DionG Saturday, October 22, 2011 11:05 AM
    Friday, October 21, 2011 6:45 AM
    Moderator

All replies

  • the database providers (including SqlSyncProvider that works with Sql Azure) tracks, selects and applies changes by table. so if a change is detected for a row in a table, the changes retrieved are only for that table's row.

    i've seen many instances where custom code applies further business rules/logic on the changed rows by retrieving other information from other sources so i dont think its wrong.

    writing a custom provider is not for beginners if i may say.

    if its just a one way sync, i'd say you're better off just using timestamps.

    using a similar approach as the offline providers, every time you sync, you'd grab the changes, send to queue and then record the last timestamp you processed. you can then use this same timestamp to detect new changes by finding timestamps greater than what you previously stored.

    if you go the path of building your own provider, i suggest you look at Sync Framework Toolkit and build a custom offline provider instead.

    • Marked as answer by DionG Saturday, October 22, 2011 11:05 AM
    Friday, October 21, 2011 6:45 AM
    Moderator
  • Thanks. It is indeed a one-way sync. The only other unusual thing is that being a queue data is only there until consumed and so it's not trully a replica. I'm curious why you suggest offline provider. (Edits never happen at the queue end but neither end is offline.)
    Saturday, October 22, 2011 11:26 AM
  • that's just if you go decide and build your own. it's easier writing an offline provider using the Sync Framework Toolkit than a full custom provider. not necessarily because you need an offline cache.

    another approach you might want to look at is Sql Service Broker.

    Sunday, October 23, 2011 12:45 AM
    Moderator