Server Configuration

[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

 

[johnv20]

The most important consideration is how important uptime of the server is & how long you are prepared to wait for the biggest jobs to run

howerver there are a few things which can considerably increase performance without breaking the bank

1: Use a raid 1 / raid 5 config with the os on the the raid 1 & the database on the raid 5

2: Pick the fastest speed, not necessarily the biggest hard drives avaliable - since you are doing a lot of data crunching the bottleneck will be in read / writes to hard drive rather than system cache so using 15K rather than 10K drives should give you a 50% performance improvement

3: Always use a minimum of 2 processors

4: Always use a minimum of 2 power supplies

So to summarise spend your money as follows to maximise performance
Raid
15K scsi drives
Processors
Memory

 

[technome]

Mucho ram, SQL eats ram, figure at least 1.5 gig just for SQL minimum, bare min for a database server 2 gigs

Fast cpu(s), been using Xeons, but there is little to gained with the processors with large caches. Avoid the absolute newest processor, well over priced.

A good case which keeps drives cool, nothing kills array drives like high temperatures.

A low cpu usage raid adapter, adapters vary, SATA adapters particularly. All raid adapters vary widely in performance. I prefer ultra 320 scsi setups with raid adapters from Lsilogic which are used by Dell, IBM, Intel and others in oem models. On a u320 raid, maximum performance is hit with 5 drives per channel, any more is useless because the channels are saturated, each drive output 70 meg/s, u320 has the max at 360 meg/sec, 5 drives output about 350 meg/sec ( scsi bus throughput, not disk throughput). Divide the raid array drives over multiple channels. Lastly, run arrays with a hotspare.

Just done some extensive testing on a new setup of a DK8n dual 246, Lsilogic u320-2x, 8 Seagate scsi 15k drives, raid 5, raid1. Some observations with this setup...

Having raid1 and raid 5 on the same raid adapter, is a negative as far as throughput on the raid 1, as I had also observed on previous raids, overall raid 5 crushes it unless you have multiple disks per channel(raid10). You are better off having a raid 5, with two partitions, one for the OS and the other for data. Perhaps having the log files, temp files and a raid1 on the same raid adapter might have better performance, but highly unlikely due to the poor benchmark results of raid1; most high end raid adapters are not optimized for raid1. If the raid1 is on a separate dual channel u320 scsi adapter, you will get better performance. Raid 10 has the best throughput, but gets very expensive.

As far as 10k vs 15k drives, their is a small gain in throughput as far as general file server performance. Though I have not benched 10k vs 15k with databases benchmarking programs, this should be an area the 15k drives pull ahead, but the gain will be no where near 50% as the manufacturers would like you to believe; a 10-20% increase would be more realistic. That being said I would go for the 15k drives with SQL databases. If you search 15k vs 10k, you should come up with more info. Skip the manufacturers web pages, which are super hype.

http://www.techreport.com/reviews/2003q3/atlas-15k/index.x?pg=4

 

[vkode78]

Thank you for all your comments. This is the system that we finally ordered yesterday:

DELL poweredge 2650
Dual Xeons 2.8Ghz/512K
2 GB RAM
On board PERC3D/I controller
3 - 73GB 10K SCSI disks.

Unfortunately, we cant afford to have a seperate RAID card and 2 more disks rightnow, but we might grow into that later.

So if I decide to do RAID5 array across those 3 73GB disks,
I have a disk space of approximately 170GB. From what I have been reading, for my server hardware configuration and needs, this is what I am thinking of doing.

2 partitions:

C: 12 GB - OS and SQL server installations
D: DATA drive for databases & some storage of 25GB.

Ideally I should have 2 more disks with RAID 1 and have the OS on it,but that may not happen right now.

So with this 1 RAID5 array w/2 partition configuration, are there any issues that you guys see - primarily,anything that I can do right now to make this system good. I want to do it right the first time, so I am not caught with my pants down later when we need to accomodate more databases. Right now, I have atleast 120GB of space for databases. That is good enough right now, but I wouldnt be surprised if we need more space. For that, I amthinking if we could add another 73Gb disk in maybe 6 months, I can accomodate more databases.

Am I doing it right planning for future growth in terms of hard disk space? Please remember that the users we are gona have on this system at themost are 2 people running lots of long queries and manipulating tables at random times. SO at times, the system could be doing nothing.

And we have a Compaq DLT backup tape machine that we intend to use for backing up databases. Can I hook this up directly with the server and use Veritas to do the backup?
Or is there a better way?

Thanks again for all your advice!

 

[technome]

Correction....

"On a u320 raid, maximum performance is hit with 5 drives per channel, any more is useless because the channels are saturated, each drive output 60-70 meg/s, u320 has a maximum at 320 meg/sec, 5 drives output about 300-350 meg/sec ( scsi bus throughput, not disk throughput)."