Server Configuration 1

[vkode78]

Hi we are a small business and we are at the point where we are writing the specs for a new server. This server will primaliy be dedicated to SQL server 2000. It will be running on SBS2003.

AS of right now, we have few databases - some very small, and some of them are 20Gb,30GB and the biggest we have is a 70GB database. And they all add upto 200GB.
We dont really use them all at the same time. We get raw data and we populate the databases initially and run queries on them and manipulate tables or read couple of million rows. This work is really random. IT doesnt happen all the time, and sometimes the server can be sitting idle doing nothing for days. And some days there are big queries running for days. And more importantly, we will have ONE user, if not two at the most. And indexing is done often and then dropped again once we insert more data. So we do need good indexing.

Where should we be looking to spend more money on?

- RAM?
- Processor Speed ( Dual Vs Single )
- SCSI drives or SATA/ATA
- which RAID configuration?


Any suggestions will be very appreciated.

Thanks
Kode

 

[mrdenny]

The thing to look at is peak usage. You could really care less what kind of horse power is sitting there when no one is using it. If you've got long running queries you'll want to look into the query to see why they are taking so long. Is your cost IO based, or is it CPU based. I'll give you my recommendations for a SQL Server based on a perfect world, and you can adjust to meet your needs.
CPU - Dual chip or higher Xeon processors with hyper-threading active.
Memory - At least 2.5 Gigs (2 gig for SQL, 512 MB for OS) If 2 GB isn't going to be enough, then more RAM and SQL Enterprise Edition. Most projects standard is good enough.
I always go with SCSI drives. I've found that they are a little more robust, and are usually hot swapable. You also have a lot more options for hardware RAID controllers.
RAID - OS on a RAID 1, Transaction Logs on another RAID 1, mdf data files on a RAID 5, and tempdb on a RAID 1. Here are my reasons.
OS on a RAID 1 - The OS does most of it's reading on boot, so it does most of it's reading once only. The rest of the time it's writting to the swap file. RAID 1 or RAID 0+1 are best for writting. Since we don't need the extra space of a RAID 0+1 for this array, we'll stick with a RAID 1.
Transaction Logs on a RAID 1 - 99% of what is done with transaction logs is writting. They are read at 2 times. SQL Startup, and transaction log backup. Because of this we need a drive that is configured for fast writting. RAID 1 and RAID 0+1 are the best for fast writting as there is no parity to compute. I go with RAID 1 unless I can't get enough space on a RAID 1 array, then I switch out to a RAID 0+1 array.
mdf data files on RAID 5 - RAID 5 is a fast read, slower write array. Most traffic to the data files in a database will be read data, so you'll want to place these files on an array that is configured for fast read. Write to a RAID 5 array is slow because the RAID controller must calculate parity.
TempDB on RAID 1 - Same basic reasons as Transaction Logs. If your tempdb will be used to house a lot of temporary tables and you are going to do a lot of reading from them, a RAID 5 array would be good for TempDB (See my reason for the data files).

Nothing should share your OS drive except for the exe files.

Software RAID controllers are the devils play things. On a high load system having a software RAID controller will kill the server because each request to the drive takes additional CPU power to figure out which drive the data is on. A hardware RAID controller doesn't have this problem as the parity and drive config is handled by the CPU on the RAID card which is dedicated to and optimized for disk access.

You can also have problems with a software RAID controller when formatting and reinstalling the OS if something dies, as you now have to worry about the drive being reimported. I've seen several software RAID arrays be lost because the reinstalled version of the OS didn't know that the array was an array. This isn't much of a problem any more, but it can still happen.

Denny

--Anything is possible. All it takes is a little research. (Me)