MS SQL Data Warehousing - Drawing up the spec for our Datawarehose Server

Author

Drawing up the spec for our Datawarehose Server

Russell

2005-05-11, 7:23 am

I am in the process of drawing up the spec for our data warehouse server. The
setup that I am having to cater for is a server that will hold:

1) a replicated copy of the tables that are going to be used to extract that
data from as we have 22 offices this will be 22 databases. (ranging in size
from 500 Mb to 20 Gb)

2) A staging database

3) The Data warehouse database

4) Cognos OLAP Cubes

In terms of the hardware I am looking to get a dual processor box (with the
capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to limit
sql to a portion of this leaving a good amount available for the OS and the
OLAP Cubes.

The part that I am stuck on is the RAID config. It is envisaged that the
cubes will be refreshed once a week.

So far the RAID Solution I have come up with is below:

RAID 1 - For the OS, SQL Binaries etc.

RAID 5 - For the Database files

RAID 5 - For the Transaction Logs

RAID 5 - For the Cognos OLAP Cubes

What are your thoughts on the above?

pdxJaxon

2005-05-11, 8:23 pm

Id stay away from RAID 5 (you're gonna hear this a lot I suspect).

If you want performance, you should try to go with RAID 1+0 AT Least for the
Data and Logs.

Get RAID Controller(s) with Battery Backed Cache as well.

Hope this helps

Greg Jackson
PDX, Oregon

Danny

2005-05-12, 7:23 am

So you appear to be looking for a one size fits all solution. This might
work for a short while during the ramp up phase but most DW installations
will split the functions apart as they run into contension.

From the notes:
You are planning on having the following on one server.
22 source databases with a total space used between 11GB and 440GB
SQL replication running against all 22 source databases
1 stagging DB
1 DW DB
Cognos PowerPlay
I assume IIS and some of the other Cognos products unless you are planning
on using the network to copy the cubes or direct LAN connect from clients.

What ETL tool will you be using DTS, straight TSQL, or third party product?
If third party product where will it be installed?
With this much source data, is there an estimate as to how large the DW
database will be?

Unless you are only popluating a very small amout of data this configuration
will be overloaded inside six months and the first two to three months is
usually used for initial development and limited user testing.

Sorry for the bad news but better to know up front...

"Russell" <Russell@discussions.microsoft.com> wrote in message
news:BA7C6084-ACD6-4B55-9125- 0165108B7D9B@microso
ft.com...
>I am in the process of drawing up the spec for our data warehouse server.
>The
> setup that I am having to cater for is a server that will hold:
>
> 1) a replicated copy of the tables that are going to be used to extract
> that
> data from as we have 22 offices this will be 22 databases. (ranging in
> size
> from 500 Mb to 20 Gb)
>
> 2) A staging database
>
> 3) The Data warehouse database
>
> 4) Cognos OLAP Cubes
>
> In terms of the hardware I am looking to get a dual processor box (with
> the
> capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to
> limit
> sql to a portion of this leaving a good amount available for the OS and
> the
> OLAP Cubes.
>
> The part that I am stuck on is the RAID config. It is envisaged that the
> cubes will be refreshed once a week.
>
> So far the RAID Solution I have come up with is below:
>
> RAID 1 - For the OS, SQL Binaries etc.
>
> RAID 5 - For the Database files
>
> RAID 5 - For the Transaction Logs
>
> RAID 5 - For the Cognos OLAP Cubes
>
> What are your thoughts on the above?
>

Sponsored Links