Merging data while keeping relationships in SQL Server

The scenario I have is that multiple PCs running local SQL Server 2008 instances will be generating data using tables with integer identity fields. This data will have related records, linked on those integer ID fields. The data needs to be mergeable from multiple PCs into a single database on a central server with the same structure (so the same reporting code can run against any database) while properly maintaining links between the related records.

As a (fictional) example, let's say the PCs are all recording aspects of the weather. Every 10 minutes, a record is created in the WeatherInspection table. This has an integer identity ID field. A number of records are also created in WeatherInspectionItems containing the temperature at a number of different temperature sensors. These records are related to the WeatherInspection table by the ID field. This is not the real scenario, but illustrates the principle - parent table with an integer ID field, child table linked back on that ID. In practice, there are many more related tables, each with an int ID field.

I need to then be able to merge WeatherInspection and WeatherInspectionItems from all the PCs into a central SQL Server 2008 database. Because each PC has its own identity fields, each PC could have used the same IDs within its own WeatherInspection table.

During the merge, I need to be able to assign a new identity value to the WeatherInspection records so they remain unique in the master database, but the big issue for me is that I also need to be able to alter the value in the child records so they link to the new ID field.

I want to be able to:

1. Keep using int IDs rather than switching to GUIDS
2. Maintain th开发者_开发问答e same database structure in both DBs
3. Keep int IDs as the sole primary key field

I am really interested in whether there is any merge technology within SQL Server or other related products that can reassign ID fields in parent table and maintain the relationship with child records.

I know I could have composite primary keys locally with the machine ID or something like that in it, but due to ORM tools that we may be using that need a single int ID field, I am trying to avoid composite keys and GUIDs.

I've tried searching, but can't find an article anywhere that covers updating related child records with new parent ID values.

Thanks!

---

With the MS Sync Framework, you can intercept the data before it makes it to the database.

I am not sure if this is possible with merge-replication.

---

GUIDs would seem the easiest way to go, but if you insist on using the generated ids then it is possible if your ORM is using an SQL Server SEQQUENCE to get the ids.

All you need to to is set your IDENTITY(start,1) to have a defined start number on for all the tables on a given server -- with a diffrent "start" for the other server. 0 on server1, 1000000 on server two , 2000000 on server 3 etc.

Unfortunately SQLServer does not allow you to define an "end" number for an identity sequence so you will have to keep an eye on your tables to detect an overlap with another server.

This solution is linmited an will only work for a small number of servers expecting < 1000000 rows in any one table.

Like I say GUIDS seem like a better solution.

---

Can't the SQL Data Compare tool from RedGate bring a solution here?