Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question

1 Answer

up vote 3 down vote

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.

share|improve this answer
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. – SQL_Student Sep 13 '12 at 13:01
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? – Remus Rusanu Sep 13 '12 at 13:48
I'm using transactional, but when the replication occurs it recreates the tables and the data is overwritten – SQL_Student Sep 14 '12 at 7:19
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. – Remus Rusanu Sep 14 '12 at 7:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.