Merge Data from Multiple Remote SQL Databases into One SQL Master DB

Question

Hi, Well, I'm sure this question has been asked before but I'm yet to find a single solid answer to it. I'm in the process of creating a solution that involves remote offices uploading data to one master db via web service. Basically, each office will have a windows service that runs every 1hr or so, picks up any new data into a dataset, connects to the server via http, and uploads the dataset. The server then imports the data and all is well. In theory it should work. Right.

Not really, so each office has a unique OfficeID, given to it by the server so as to keep them unique on the server. At first I thought I'd cracked it till I realised the problem with auto-increment PKs. You see, all the remote offices already have existing data and all their tables all have Auto-Increment PK's and all associated constraints. Root/Parent Tables that have the OfficeID have no problem since it is already unique, the problem lies with the foreign keys since when they'll reach the server, they'll have a NewID and so the relationship with the child will lost.

At the moment I have only 2 solutions.

1. Remove all auto-increment and unique PK constraints on the server db and use the OfficeID to filter out duplicate foreign 'keys'. or...
2. When importing the dataset, keep track of each row and it's associated parent using stuff like Scope_Identity etc so that each child row is associated with the correct parent row.

Option 1 is looking much easier to implement since its less work for me, but I wonder about sql's performance and also data integrity will be a problem since constraints cannot be enforced. Option 2 will keep things in check but Gosh the amount of code needed is mind boggling.

Are there any other options that I'm not considering? and if I only have the 2 above, which is the lesser of two evils.

Thanks John

Answer 1

Removing the PK constrainst is going to lead to disaster at some point. I'd vote for option 2 - it's more work, but you'll have a more robust and maintainable solution at the end of it. It does mean that the office databases won't match the master any more - they'll need updated too...

Answer 2

Use GUIDs as your primary keys. It's not 100% foolproof (99. and a fairly large number of nines percent), and there is a performance penalty, but it is the easiest way of doing this while remaining sane.

Answer 3

Can you include the Office id in the table and have a composite primary key? If not I would probably go with @tdammers suggestion of using a GUID.