Real world hardware requirements for SQL Server 2000

[HeroOfPellinor]

My experience with SQL has been that it's fairly light. It's installed on my P2 400 at work as just a little extra and has been performing well for some small time web work. Since my company recently moved to a new vendor that uses Access as a front end and SQL Server as a backend, I ordered a server from HP with 2.4 Ghz Xeon 256 RAM and two 80 GB IDE HDs in RAID1. The application performs like an absolute dog. My intention was to add 1 GB Ram, but the server just got here a day before they did and they installed while I was off--I needed to get the exact specs of the RAM before ordering anyway. I assumed we just needed to get the ram, but the vendor is insisting that we need a dual 2.4 Ghz Xeon with 6 SCSI drives for three volumes and 2 GB RAM. Is that typically how much horsepower a SQL Server running under Server 2003 needs to server a couple dozen people with an Access app? They were by far the most emphatic about the SCSI drives.

Anyway, it's out of my hands because they sufficiently spooked my supervisors into the need for this behemoth and my suggestion that a little more RAM would solve things went virtually ignored. I'm just looking for some validation or clarification so I can rest easy about this. Thanks.

 

[BoberFett]

It all depends on what you're using it for. Complex queries will use boatloads of RAM. The more users the more RAM. But 256 definitely isn't enough.

Also, a 2 drive RAID1 IDE setup pretty much blows for SQL Server. That means that in the current situation not only do you have your data going the same drive but your swap file (which with 256 MB is being hammered) and your logs are going to that drive as well. And not only that but it's software RAIDed (I assume it's software).

A 2.4 Xeon should be plenty for a small SQL server, but the RAM and drives are pretty bad. I don't know if you need 6 drives for your small number of users, that might be overkill. But I'd say 3 drives would be a start. One for the system, one for the logs, and one for the data. You might get away with IDE, but SCSI would be preferable. The very random access nature of multiuser databases loves SCSI.

 

[HeroOfPellinor]

The six is for redundancy...each volume will have RAID 1. We may upgrade the data volume to RAID 5.

Thanks for the info. The three volumes makes sense since it's how I have Exchange configured, but I thought SCSI was overkill. *shrug*

 

[DaveSimmons]

It's just the RAM. On one box for QA here we were running it with 320 MB RAM and performance was awful. On a similar box with 512 MB RAM performance was quite decent (cpu was just a Tualatin 1.3a). At 256 MB a box would be doing an insane amount of paging to disk for virtual memory.