none
How do i sync/merge 2 databases with data already there ?

    Question

  • 1. We've setup a local database which has been used for 2 months

    2. We've then made a Backup of the database, then copied it to a foreign destination server

    3. the local database now has new data


    Is it now possible to setup synchronization which will merge the differences between the two databases ?

    (and maybe continue synchronizing with those settings ?)


    i know we could simply redo the backup-restore procedure again... but it would be convenient to use MSF to merge the two databases.

    thanks.
    Friday, January 29, 2010 10:16 AM

Answers

  • Montago,

    Here are a few more thoughts:

    - Can you take the database backup after provisioning at the first site, then restore it, fixup metadata and sync. This could solve some of the issues you are hitting. You can refer to these articles for more info on backup/restore: How to: Backup and Restore a Database (SQL Server) and Backing Up and Restoring a Database

    - you are essentially backing up the database and restoring on the other site and then setting up sync partnership. What this will end up doing is that it will eventually send all data across (site1 to site2 and site2 to site1). Instead of that, why dont you set the sync partnership first and then let the data flow through sync. This way, the data will really flow only once on the wire.

    Now for the specific issue you are seeing, some questions:
    1. Are there any errors that you are seeing during the sync process?
    2. Are the rows being applied at all - a SQL profiler trace would help tell you that
    3. The the rows getting conflicts with something on the site and automatically getting resolved in an undesired manner - hook up to the changesApplied ApplyingChangesFailed events
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 12, 2010 6:27 AM
    Owner

All replies

  • yes you can, you just have to tell SyncFx how to handle the conflicts (e.g., row updated on both copies, row update on one copy and same row deleted in the other copy, etc...)
    Friday, January 29, 2010 6:02 PM
    Moderator
  • how should my settings be in this scenario:


    DB1: Table1 = DATA1 (unique to this database)
    DB2: Table1 = DATA2 (unique to this database)


    i've tried several times where all my conflicts are set to "continue".

    no matter which syncdirection i choose (DownloadAndUpload or the UploadAndDownload) i end up like this:

    DB1: Table1 = DATA1 x DATA2 (merged)
    DB2: Table1 = DATA2


    which i find a bit strange :-(
    Saturday, January 30, 2010 10:58 AM
  • yeah, that's strange.

    care to share what DBs are involved, the providers you used and how you set up the sync?
    Saturday, January 30, 2010 7:00 PM
    Moderator
  • im using the SyncOrchestrator and 2x SqlSyncProvider

    connecting to SQL2008 Express and SQL2005 Express

    Saturday, January 30, 2010 10:57 PM
  • you may want to check if the queries for enumerating changes on your DB1 is actually returning any rows.
    Sunday, January 31, 2010 4:57 AM
    Moderator
  • Just to understand your scenario better, you have unique data that at each site that you want to flow to the other DB.
    Now is this unique data added after you setup the sync partnership or before?
    If it was after, then run the enum queries as June says to see why they are not being enumerated.
    If they were added before provisioning the database then you may need to dummy update the rows (update table set colum1=column1) so that they get a newer timestamp and will get enumerated.
    This posting is provided AS IS with no warranties, and confers no rights
    Sunday, February 07, 2010 11:34 PM
    Owner
  • The data was inserted BEFORE doing the provisioning...

    I guess that doing an update on all rows would be ok... 


    i just thought that MSF would merge the two databases if data was present before provisioning... 
    Monday, February 08, 2010 8:11 AM
  • Montago,

    Here are a few more thoughts:

    - Can you take the database backup after provisioning at the first site, then restore it, fixup metadata and sync. This could solve some of the issues you are hitting. You can refer to these articles for more info on backup/restore: How to: Backup and Restore a Database (SQL Server) and Backing Up and Restoring a Database

    - you are essentially backing up the database and restoring on the other site and then setting up sync partnership. What this will end up doing is that it will eventually send all data across (site1 to site2 and site2 to site1). Instead of that, why dont you set the sync partnership first and then let the data flow through sync. This way, the data will really flow only once on the wire.

    Now for the specific issue you are seeing, some questions:
    1. Are there any errors that you are seeing during the sync process?
    2. Are the rows being applied at all - a SQL profiler trace would help tell you that
    3. The the rows getting conflicts with something on the site and automatically getting resolved in an undesired manner - hook up to the changesApplied ApplyingChangesFailed events
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 12, 2010 6:27 AM
    Owner