February 17, 2005 at 8:07 am
What kind of hardware requirements would I need for sql server if Im developing a datawarehouse that will have a fact tables of about 10 000 000 rows with a dimension table of about 50 000 rows.
What kind of setup would you need on a server example Raid 1 2 3
February 17, 2005 at 10:36 am
The answer for a dw design is that it depends...on desired performance and reliability. I would read up on RAID in BOL and make a decision from there. You'll also have to factor cost for disk space for certainn solutions.
RAID 0 - best performace, but zero protection for disk failure
RAID 1 - requires 2X the disk (mirrored), great read performance but can degrade write perfromance
RAID 5 - basically RAID 0 with parity, less disk that RAID 1, good overall performance, less costly than 1
RAID 10- gives the power of 0 and 1 but at a high cost.
..hope that helps.
February 17, 2005 at 11:49 am
Thats helps alot, but what about disk size.
Ig you expect to have for example 3 fact tables each having 10 000 000 rows. What size of hard drive would you consider.
I was thinking 200gb
February 17, 2005 at 1:23 pm
Fact tables tend to be a lot bigger than that but actually it all depend on the application so your case may be ok. To estimate the size you need to have an estimate of the expected growth and keep that in mind for future needs. For an estimate of the space you need to know also how many dimentions are also involved
a quick calculation of your storage needs for just the fact tables will be:
assuming 10 dimentions (10 FK int) and 5 meassures (money) 5 meassures(int) qtys and 5 meassures (decimal (9,5)) will give you an aproximate of (4*10 +5*8 +5*4+5*5) per row therfore 125 bytes/row 125 *30 mil ~ 3.6GB
so really 200GB seems overkill just for fact tables. You can compute stuff like this with the actual data and get a better picture.
HTH
* Noel
February 17, 2005 at 1:35 pm
I looked through some stats I keep and came up with a number (based on 3 fact tables 20 columns/12 million rows and a couple dim tables) =5.75 GB.
Don't forget Growth % and db bkp file space.
200GB would be plenty. I wouldn't under sell myself on space. Seems like dw projects grow as the audience grows and rapidly.
February 17, 2005 at 1:36 pm
thanks that helps alot.
February 18, 2005 at 5:12 am
Processors - 4 (min 2)
RAM - 4GB (min 2)
NIC's - min 100 Mb, prefer Gig - teamed NICs
RAID contollers - min 2 eliminate SPOF (the more the better)
Disks - OS & SQL install on RAID 1
SQL tempdb on RAID 0
Data and logs on RAID10 or prefer RAID 5
Backups on RAID 1 array
Get a couple of spare hard disks for hot spares.
And lots of $$$$'s
February 18, 2005 at 10:27 am
Tossing about all of the disk numbers, nobody mentioned SAN disk. The reliability and performance of a SAN is unmatched by almost any internal disk configuration for database or dw performance.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply