Need some advice regarding replication

  • 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 300 tables which need to be replicated on each site.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.

  • I havent tried this but i think it can be done if you give tables in database with different names atleast different schema name in different databases

  • Do you mean for each site I should change the schemas of the tables, it will not work if all the tables on the different sites all belong to the dbo schema

  • I believe yes you can do this.

    When you are configuring your publication, do not initialize from backup.

    Setup all your publication in this way and populate data in subscriber manually.

    The other step you have to take is to drop PK constraint on the Subscriber, because there may be

    PK violation errors coming out of multiple publishers.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply