Competition: What’s your favorite Redgate tool? Enter now.

Use SQL Data Compare to keep 2 dbs in sync

smccloudsmccloud Posts: 7
Is it possible to have SQL Data Compare keep two databases in sync where either one can be written to. Normally only one will be written to, but for software updates it will be taken offline and the second server will become the active one. When the primary server comes back up I need it to be able to get the changes made to the secondary server's db and not erase the rows like has happened to me in my current testing.

Comments

  • James BJames B Posts: 1,124 Silver 4
    This can work to an extent - however you'll have trouble if records are added to both databases with the same primary key. When you run Data Compare against them, the records will be seen as matching, so the target will be updated to match the source. What it won't do is insert new records and alter the key so you can keep all records.

    For example:

    DB1 has records 1, 2, 3, 4, 5 added. DB2 already had 1, 2, 3. DB1 goes offline, and in the meantime, records 4 and 5 are added to DB2.
    When you come to sync, if you go from DB1 > DB2, then the 4 and 5 in DB2 will be UPDATE'd to match DB1. It won't create records 6 and 7 for you.

    As long as the keys differ though, it should work - you just need to remember to un-tick the "Target Only" column, and that will stop it deleting records in the target that don't exist in the source... for example:

    DB has records 1, 2, 3, 4, 5. DB1 goes offline, so DB2 then has 6, 7, 8 added. When you come to sync later on, and go from DB1 > DB2, if you don't untick the "Target Only" column then records 6, 7, and 8 will be deleted from the target (DB2) as they don't exist in DB1.

    Hope all that makes sense.
    Systems Software Engineer

    Redgate Software

  • This can work to an extent - however you'll have trouble if records are added to both databases with the same primary key. When you run Data Compare against them, the records will be seen as matching, so the target will be updated to match the source. What it won't do is insert new records and alter the key so you can keep all records.

    For example:

    DB1 has records 1, 2, 3, 4, 5 added. DB2 already had 1, 2, 3. DB1 goes offline, and in the meantime, records 4 and 5 are added to DB2.
    When you come to sync, if you go from DB1 > DB2, then the 4 and 5 in DB2 will be UPDATE'd to match DB1. It won't create records 6 and 7 for you.

    As long as the keys differ though, it should work - you just need to remember to un-tick the "Target Only" column, and that will stop it deleting records in the target that don't exist in the source... for example:

    DB has records 1, 2, 3, 4, 5. DB1 goes offline, so DB2 then has 6, 7, 8 added. When you come to sync later on, and go from DB1 > DB2, if you don't untick the "Target Only" column then records 6, 7, and 8 will be deleted from the target (DB2) as they don't exist in DB1.

    Hope all that makes sense.

    Where is the "Target Only" column?
  • James BJames B Posts: 1,124 Silver 4
    Once you've run the comparison, you'll be presented with the results. Each table is listed in a grid, and in the centre are three columns:

    Source Only
    Different
    Target Only

    These refer to records that:

    Only exist in DB1 (on the left)
    Exist in both but are different in some way
    Only exist in DB2 (on the right)

    Because the sync makes the right match the left, records only in DB2 (i.e. "Target Only") would be deleted. By un-ticking that column for the tables in question, you stop that happening.

    Please refer to this topic for more assistance: clicky
    Systems Software Engineer

    Redgate Software

  • Ahh, I see it now. I made my own workaround yesterday. I have a scheduled task running every 5 minutes on server a to sync it to server b then a task that runs on computer startup to sync from server b to server a. It "should" work.....
  • James BJames B Posts: 1,124 Silver 4
    No problem - if you have any more problems, feel free to post, or contact support directly if it's urgent!
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.