|
Home > Archive > MS SQL Data Warehousing > July 2005 > Warehouse data from production db or reporting db?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Warehouse data from production db or reporting db?
|
|
| SolarCoder 2005-06-20, 1:23 pm |
| 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
| |
| Peter Nolan 2005-06-21, 11:23 am |
| Hi Solar C,
I'm not sure what you mean by the 'reporting server' because 'report
services' servers do not really store your data....they just present
the data out of the DW so I cannot imagine why you you are thing Pz-Rx
in any case...
That aside....transfering data from operational systems (what you are
calling production server I guess) to a DW server can be achieved in
many ways and replication is one of the more expensive ways I would
imagine. I have never used database replication as it is usually quite
expensive because it usually insists on having a full copy of your data
elsewhere and sometimes required the update to the remote server to be
inside the same unit of work.....I am not sure if SQL Server can
perform delayed replication to a target database......so usually if you
use replication the volume of data must be small and the volume of
transactions must be small...
Much more normal is to extract based on timestamps as well as
developing a mechanism to catch deletes or, if you can, make sure the
application writes a log so that all records that are being updated are
recorded in the log and use the log to extract the updated
records....meaning not the database log...
If that fails you can read the database log....I do not know if there
are tools to do this on sql server....
If that failes you can dump data and do delta file generation but this
is a last resort because of the CPU you will consume..
>From your production sever (one ore more) I'd suggest you send data to
a staging area on your target DW box and then move it again into a
dimensional model...You can use MSFT DTS or third party ETL tools to
code all the data movement and transformation...
Best Regards
Peter Nolan
www.peternolan.com
| |
|
| A reporting server is typically a copy (replicated or restore from backup)
of the operational server with a few more indexes thrown in and is used for
the purpose of reporting at the transactional level or perhaps on occasion
QA testing.
"Peter Nolan" <peter@peternolan.com> wrote in message
news:1119366058.331139.12600@g49g2000cwa.googlegroups.com...
> Hi Solar C,
> I'm not sure what you mean by the 'reporting server' because 'report
> services' servers do not really store your data....they just present
> the data out of the DW so I cannot imagine why you you are thing Pz-Rx
> in any case...
>
> That aside....transfering data from operational systems (what you are
> calling production server I guess) to a DW server can be achieved in
> many ways and replication is one of the more expensive ways I would
> imagine. I have never used database replication as it is usually quite
> expensive because it usually insists on having a full copy of your data
> elsewhere and sometimes required the update to the remote server to be
> inside the same unit of work.....I am not sure if SQL Server can
> perform delayed replication to a target database......so usually if you
> use replication the volume of data must be small and the volume of
> transactions must be small...
>
> Much more normal is to extract based on timestamps as well as
> developing a mechanism to catch deletes or, if you can, make sure the
> application writes a log so that all records that are being updated are
> recorded in the log and use the log to extract the updated
> records....meaning not the database log...
>
> If that fails you can read the database log....I do not know if there
> are tools to do this on sql server....
>
> If that failes you can dump data and do delta file generation but this
> is a last resort because of the CPU you will consume..
>
> a staging area on your target DW box and then move it again into a
> dimensional model...You can use MSFT DTS or third party ETL tools to
> code all the data movement and transformation...
>
>
> Best Regards
>
> Peter Nolan
> www.peternolan.com
>
| |
| Peter Nolan 2005-07-12, 7:23 am |
| Hi JT,
sounds reasonable.....and a little confusing when someone is already
talking about servers for 'Reporting Services' as a product from
MSFT..;-)
Best Regards
Peter
|
|
|
|
|