Warehouse data from production db or reporting db?

Hi all, hope I am in the right newsgroup!

I was looking for insight into a data warehouse design decision.

We are going to have multiple sql2000 production servers (Px) and multiple
sql2000 reporting servers (Rx) each paired at a remote location on their own
individual LAN. All pairs will be connected over a WAN to one central
sql2000(5?) (DW) data warehouse server. We will be using replication from the
production to the reporting server over the LAN. The transfer method from the
remote pairs to the DW is not yet decided.

My question is how to get the data from the production server to the central
data warehouse. Transfer directly from the production servers or transfer it
from the replication servers? Do either one of these designs have any
advantage over the other?

P1 -> R1 -> DW
P2 -> R2 -> DW

OR

P1 -> R1
P1 -> DW
P2 -> R2
P2 -> DW

I would think that for system stability you would want to pull the data from
the reporting servers as to lighten the load on the all important production
servers. However, since we will already be replicating the data from the
Production servers, would it be so little overhead to also replicate to the
DW at the same time if we chose replication for that step?

Any insights or references to similar discussions would be greatly
appreciated!
Thank you!
Solar C




.

Relevant Pages

  • Re: Auto-Updates for production servers
    ... I've tried to take this up with management, but it's the old 'buddy system', ... update your servers on a frequent basis but it is not best practice to have ... I prefer to push my updates on a weekly basis. ... on for production servers, but rather they should push them out with admin ...
    (microsoft.public.windows.server.general)
  • Need tip on replication situation
    ... override the data on production, however there are few tables on the ... two production servers that it doesn't overrides; ... and snapshot replication from the staging server ...
    (microsoft.public.sqlserver.replication)
  • Re: Auto-Updates for production servers
    ... His issues are with servers being updated ... Why not propose they schedule their auto updates say like every evening at ... the 'you need to reboot your server ... production servers. ...
    (microsoft.public.windows.server.general)
  • Re: Changes Required for migration from MTS to COM+
    ... Microsoft MVP, MCSD ... Download and install the Core SDK and Internet Development SDK for ... > development and production servers. ... > However, after the production servers was release to go live, we start to ...
    (microsoft.public.vc.atl)
  • Re: Auto-Updates for production servers
    ... the servers up to date. ... the 'you need to reboot your server now' ... to terminal services, we are prompted to reboot because of auto-updates. ... on for production servers, but rather they should push them out with admin ...
    (microsoft.public.windows.server.general)