1

I'm trying to figure out the best way to populated the staging database in a data warehouse. I will have a number of databases (identical schema, SQL Server 2005 Standard). Ideally I'd set up each as a publisher, with the same publication. There will be a single subscriber database (SQL Server 2005 Enterprise) that will subscribe to each of the publisher databases. Data in the publisher databases will be modified. The subscriber database will only be updated by its subscriptions, and therefore does not need to send changes back to any of the publishers. Publisher databases don't need to update each other. Replication will be occurring over the internet (although VPN could be used).

I'm not clear on what kind of replication I should be using for this. Can I do it with replication? what about Incremental fields?

Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93
  • Oh I'm sorry. I'm not clear on what kind of replication I should be using for this. Can I do it with replication? what about Incremental fields? – Raymond Morphy Apr 20 '11 at 07:50

2 Answers2

2

Replication can definitely handle this. You don't have to do anything aside from the bog-standard setup unless there's any overlap between the different publishers' tables. That is, if you have pub_a and pub_b as publishers that both have a table tbl_a, then you either have to publish them to different tables at the subscriber (the destination table is defined in your call to sp_addarticle) or you have to guarantee that the data between disparate publishers will never collide. In the latter case, you also need to be careful about what you supply for the @pre_creation_cmd parameter in your call to sp_addarticle. The default is to drop the table at the subscriber which means that the last publisher added to the mix would win and the rest would be broken. You'll need to specify 'drop' for the first added publisher and 'none' for the rest. Good luck!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
1

I believe that this would be possible, but you'd set it up the opposite way around than you've specified. You'd set the central database as the publisher, and you'd use Merge Replication.

Merge Replication includes an option to allow dynamic filters - so what you'd want to do is set the filters up so that each subscriber only receives the rows that it originated - probably by adding a column to some of your tables to include the HOST_NAME() of the server where the row originated. You shouldn't need to do this to every table, because once you've filtered one table, you can have cascading filters that filter out rows from additional tables using joins.

As to "incremental fields" - I assume you're talking here about IDENTITY columns? Luckily, these have also been thought about - basically, the publisher manages the IDENTITY range, and hands out smaller ranges (of 1000 values, by default) to each subscriber.

Caveat - these are the general principles, but I haven't tried this kind of setup myself before. I'd recommend that you try it in a "toy" database first, and try to get it working.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Hi, thanks for your response. but I think the central database should be a publisher because it wants gather information inserted in the other database(subscribers) just in one database. Am I thinking wrong? Please give me your idea – Raymond Morphy Apr 20 '11 at 10:04
  • @Raymond - the problem is, there's no replication model built into SQL Server that matches what you want - however, you can have a central publisher with multiple subscribers, and there are two forms of replication that support having the subscribers make changes (transactional/merge). Of these, only merge supports dynamic filters, which is what I'm using to prevent re-publishing this data back out to the other subscribers. – Damien_The_Unbeliever Apr 20 '11 at 10:08
  • sorry but I just confused. you say there is not built in replication for my purpose. so what should I do to gather information inserted in different remote databases in just one central database? I'll be happy to give your idea. – Raymond Morphy Apr 20 '11 at 10:34