Sync Question - SQL Data Compare
timbushby
Posts: 3
Hi,
We are currently evaluating the product and so far are very impressed - However, before we commit to buy there is one technical question I hope you can help with, I have had a quick search through the docs and the forums but haven't had any joy.
Scenario - 2 databases, one 'offline' and one 'live'; Many customer sites. If one customer site loses connection to the 'live' db, our application uses the 'offline' version at each site. Assume this offline version is an exact copy of the online db (tested, using your software, works excellently). We then need to synchronise this offline db with the live db when the connection has been restored. This is fine, but the live db will have been updated by the other sites in the meantime. The application accepts new entries and churns out auto numbers as unique identifiers - herein lies the problem.
When a site goes offline, and a record is added to the local db and another site adds a record to the live db, the unique index given will be the same. When SQL Data Compare looks at the 2 dbs, it realises that there are 2 'different' records but that's it; if the 2 are synchronised, data in one of the dbs will be lost.
Does your product offer any solution to this out of the box, without resorting to code?
If that terrible scenario explanation made no sense, let me know and I'll ring!!
Many thanks in advance.
Tim.
We are currently evaluating the product and so far are very impressed - However, before we commit to buy there is one technical question I hope you can help with, I have had a quick search through the docs and the forums but haven't had any joy.
Scenario - 2 databases, one 'offline' and one 'live'; Many customer sites. If one customer site loses connection to the 'live' db, our application uses the 'offline' version at each site. Assume this offline version is an exact copy of the online db (tested, using your software, works excellently). We then need to synchronise this offline db with the live db when the connection has been restored. This is fine, but the live db will have been updated by the other sites in the meantime. The application accepts new entries and churns out auto numbers as unique identifiers - herein lies the problem.
When a site goes offline, and a record is added to the local db and another site adds a record to the live db, the unique index given will be the same. When SQL Data Compare looks at the 2 dbs, it realises that there are 2 'different' records but that's it; if the 2 are synchronised, data in one of the dbs will be lost.
Does your product offer any solution to this out of the box, without resorting to code?
If that terrible scenario explanation made no sense, let me know and I'll ring!!
Many thanks in advance.
Tim.
This discussion has been closed.
Comments
Don’t worry about the explanation. I understand what you mean.
However what you want to do isn’t going to be possible with SQL Data Compare. This is because, as you found out, the records will be updated rather than inserted.
You could use SQL Data Compare to find out which records need to be added to your live database, then manually export and insert into the live database.
Dan
Red Gate Software Ltd
Thanks for the quick reply - that was the conclusion arrived at yesterday, mores the pity. This was the route we were trying to avoid, as the db has many related tables, pivoting around the unique identifier - we'll have to add a record to the live db, get the generated identifier, and use this to insert records into the related tables etc etc etc
Thanks again,
Tim.