Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need some advice regarding replication Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:49 AM
Points: 102, Visits: 333
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.
Post #1453379
Posted Thursday, May 16, 2013 3:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 5:42 AM
Points: 2,634, Visits: 3,980
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
Post #1453382
Posted Thursday, May 16, 2013 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:49 AM
Points: 102, Visits: 333
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
Post #1453413
Posted Thursday, May 16, 2013 6:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:56 PM
Points: 835, Visits: 1,191
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.
Post #1453484
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse