Drawing up the spec for our Datawarehose Server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Russell - 11 May 2005 12:31 GMT

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?

Reply to this Message

pdxJaxon - 12 May 2005 01:11 GMT

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

Reply to this Message

Danny - 12 May 2005 12:56 GMT

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...

>I am in the process of drawing up the spec for our data warehouse server.
>The
[quoted text clipped - 34 lines]
>
> What are your thoughts on the above?

Reply to this Message