January 13, 2008 at 4:30 pm
I currently have three copies of the same database running at remote locations. All three get updated separately with different various data, which I would like to consolidate every month or so.
At this point, I have exported each database to a csv file and merged them (eliminating dupes) using perl. I assumed that I would be able to import the new data back into each server but that has not been working well (conflict on duplicated primary key, etc) using DTS.
Am I going in the right direction?
Thanks for any help.
-Lance
January 13, 2008 at 8:25 pm
I'd probably use some type of replication to move data around, but I'm more comfortable with that. If you're comfortable with Perl or DTS, use that.
You need to track down why you are getting primary key violations. Is that ok? Is the data changed at various locations according to the rules of the application? any way you use to merge the data will require you to determine the business rules for the data and figure out how to handle conflicts.
January 14, 2008 at 4:17 am
Is it possible that the data from each site uses identities? in which case the same identity might be used at each site, creating this problem. This could be rectified by either adding a new primary key identity keeping the original one so that the record would be unique, drawback would be that you may get real duplicates slipping through.
Or
Alter the identity key to a varchar and adding a new prefix to the original number say A00001 that makes the number unique.
Or
Incrementing the numbers at each site so that they are a unique range for the location.
Watch out if you alter any key fields that you also alter any related linked table in the same way so as not to break the referential integrity.
If it was this prevent this from happening by using ranges of number at each site.
January 14, 2008 at 9:02 pm
The situation is, 3 sites are using identical servers (VMware copies of original) to maintain a database of hash values (primary key = hash, secondary value = integer). Each site adds hashes on their own, and now I need to amalgamate the database.
The original issue I assumed would be the problem was the need to guard against conflicts (two hash keys added with different secondary values). So I coded a perl script to merge the databases and clean up the conflicts.
But now the issue is getting the merged data back into each server. I have even tried the DTS with the delete all rows function running first, so I don't understand how there can be a conflict.
The idea of merge replication sounds like the type of function I need. After the post yesterday, I have been reading up on it, but am not quite ready to try it out.
-Lance
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply