locked
Advice needed on Syncing to a RRS feed

  • Question

  • Hi,

    I have a large database that our main application uses; there are some tables in this database that I would like to Sync across to a remote online/offline database where it will be for reference only and won't be updateable. At the same time, the remote tables will not need all of the columns that are in the main table.

    Ideally, I would like not to have to add any change tracking columns/tables to the main database - I only want to sync the records across, preferably syncing only those records that have changed, been created or need deleting.

    I'm trying to set up a test project to do this using Sync Services for ADO.NET 2.0, but run straight into the issue that the provider needs references for DbCommands to query and update the metadata both on the main and remote database - this is not a problem on the remote database, but I would preferably not have to add tracking columns or tables in the main database. The tables in the main database all have primary keys and a "Last_Updated" datetime field for concurrency.

    Can anyone offer some advice as to how to use Sync Services for ADO.NET 2.0 to achieve this please?
    • Edited by KrishnasPad Friday, June 26, 2009 7:00 PM Fixing thread title bug
    • Moved by Hengzhe Li Friday, April 22, 2011 2:53 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, June 23, 2009 3:21 PM

All replies

  • Hi -

    Your title really should read as "syncing from a "readonly" database". We cannot figure out what has changed on the main database without adding change tracking columns/tables ( how else would be know what changed). The direction of sync will take care of the fact that no changes made on remote databases will be sent back to the main database. So there is no way around adding change tracking information to the main database.

    Thanks
    Deepa
    Deepa ( Microsoft Sync Framework)
    Tuesday, June 23, 2009 7:39 PM
    Answerer
  • Hi Deepa - Thanks for the fast reply.

    But what about the fact the we have concurrency columns on the tables? Sure, we don't know that a record has been deleted (as there is no tombstone table) but we do know when a record was changed.

    My thought was to change the select metadata commands to output a timestamp based on the Last_Updated column value on each row, converted to Ticks (bigint). That way the knowledge would change when records were updated.

    Do you think this will work?

    I could always write a "cleanup" routine to make sure that any records in the remote table that are not in the main table are suitably deleted.
    Tuesday, June 23, 2009 10:03 PM