Replicating multiple databases into one database

Question

I would like to replicate multiple databases on different servers into one master database.

I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all the same but the data is different. I need to replicate all the data into one database on a single instance. What would the best way going forward?

There's about 132 tables which need to be replicated. I tried creating publishers on each server and subscribing them to a single database on one instance but with no success: data is being over written and some servers are not replicating.

I probably have the wrong approach or messed up something along the way. Your assistance will be highly appreciated.

Answer 1

How do you manage the primary keys on those 5 instances? Unless you carefully planned this ahead so that each instance generate non-overlapping keys on each instance, on each table (sounds like you didn't) there is no way aggregate the data. See Managing Identity Columns for identity keys. For application generated key it gets more complicated as is the application responsibility to generate non-overlapping keys.

After you set up proper key management on each of the 132 table involved on each of the 5 instances, you can set up replication, making sure you apply the initial snapshot each time carefully not to overwrite existing data.

• SQL_Student: There's dual primary keys on each of the tables, example the identity insert column and the serverID column, I tried setting up the replication and did not allow the snapshot to initialize but then the replication just hangs nothing happens. The subscriber has a message the initial snapshot for publication is not yet available.
• Remus Rusanu: You need to generate initial snapshot from each site, because you need to copy over the exiting data. But you need to make sure the published articles (tables) are not dropped and recreated on subscriber when applying the snapshot. Is it merge or transactional?
• SQL_Student: I'm using transactional, but when the replication occurs it recreates the tables and the data is overwritten
• Remus Rusanu: Change pre_creation_cmd to none. see msdn.microsoft.com/en-us/library/ms151740.aspx around specify that objects should not be dropped when the subscription is reinitialized.