Use SQL Data Compare to keep 2 dbs in sync

smccloud

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.

James B

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.

smccloud

james.billings wrote:

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 B

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

smccloud

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 B

No problem - if you have any more problems, feel free to post, or contact support directly if it's urgent!