Data warehouse Server Spec

Posted on 2007-04-10
Last Modified: 2007-12-19
Hi Guys,
I'm looking for some assistance on a server spec I should use for a Data warehouse server, the server will be used by around 20 - 40 users. I need something that will be fast when running reports and wont lag.
The data warehouse itself will hold big databases with updates of 40 Million + records a day going into them.

Can someone please recommend a spec for me: hard disk space, scalability to add in additional disks if necessary, processor spec, memory etc.

Jay Eire
Question by:jay_eire
  • 3
  • 3
LVL 95

Accepted Solution

Lee W, MVP earned 250 total points
ID: 18880740
Its difficult to come up with something when we don't know how many transactions per minute will be occurring, either peak or throughout the day.  Not to mention no idea what OS or database system you'll be running.  I would say to be safe, you want to get something that can be expanded.  It would also help to know what you are using NOW and how well it's performing.

I'd probably start off with a 64 bit capable system (most servers are nowadays) that can have AT LEAST 8 GB of RAM (though I'd probably start with 4 GB), and possible more.  As well as at least dual CPU capable (and start with at least a single dual core CPU).   As for hard drives, best performance will be a RAID 10 or 0+1, two actually, one for logs and one for database.  and at least 10K RPM SAS drives... maybe 15K and if your budget allows.  Total MINIMUM of 6 disks...

If you can provide more information, maybe we can get realistically more specific.

Author Comment

ID: 18880817
Thanks for the reply........

The OS for the new server will be Windows 2003 and the databases will be SQL Server 2000, the insertion of the 40million + records each day will be run as DTS packages overnight taking in updates from different data sources text files and other SQL servers.

The current server we have is a windows 2000 server with SQL server 7 running it.

The budget is as much as it costs on this one, the server wanted has to be reliable fast and able to coup with the 40 million plus records being added each day.

LVL 55

Expert Comment

ID: 18881264
It's impossible to guess, your 40 million records may be 10 bytes each or they may be 100MB each. If the current server copes with the load give us the spec of it and we can find a similar spec server that's available now and add a bit of redundancy and extra performance.

>The budget is as much as it costs on this one...
OK, HP Integrity Sperdome plus an XP12000 will do the job. Cost you about $4,000,000.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 18881319
here is the details of the current server.............

OS Name      Microsoft Windows 2000 Server
Version      5.0.2195 Service Pack 4 Build 2195
OS Manufacturer      Microsoft Corporation
System Manufacturer      Compaq
System Model      ProLiant DL380 G2

Processor      x86 Family 6 Model 11 Stepping 1 GenuineIntel ~1258 Mhz
Processor      x86 Family 6 Model 11 Stepping 1 GenuineIntel ~1258 Mhz

Total Physical Memory      1,048,092 KB
Available Physical Memory      18,748 KB
Total Virtual Memory      3,570,148 KB
Available Virtual Memory      1,254,208 KB
Page File Space      2,522,056 KB
LVL 55

Assisted Solution

andyalder earned 250 total points
ID: 18881591
Just replace it with current kit assuming the current DL380 does the job.

DL380 G5 E5150, (base model has 2GB RAM which is twice what you have at the moment) P400 controller (included in base model), 2 * 72GB RAID 1 for OS, pagefile and SQL exes, 2 * 72GB RAID 1 for logs, 4 * 146GB RAID 10 for data. You might need battery backed write enabler for P400 and add redundant PSUs. Disks all 15K SFF SAS except for the 146GB ones which are only 10K.

You might get away with RAID 0 for the data if it's data mining on new data every day rather than storing anything and you may be able to put the logs on the C: drive if you're using bulk import since this doesn't use logs. Then 6 disks available for data searching although you may need to split 2 off for search results.
LVL 55

Expert Comment

ID: 18881596
Above assuming you haven't got any external storage like a MSA 30 attached to the current machine that would give more than the 6 internal disks it would have by default of course.

Author Comment

ID: 18882021
thanks guys.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Patch Management is administrated by page 129 of  the following document: However, there are issues with Patch Management with FTP (for instance, a slow Internet connectio…
this article is a guided solution for most of the common server issues in server hardware tasks we are facing in our routine job works. the topics in the following article covered are, 1) dell hardware raidlevel (Perc) 2) adding HDD 3) how t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question