That being said, if you do not want to go that way....
Here is a decent setup. You can scale it down, though.
* Decent computer.
* Get a proper RAID card - Adaptec are not bad. Makre sure you have a bbu on it (Battery Backup Unit), so you can use write back caching without risk. I have a 5805 in my database server.
* Get a SAS expander. I personally have a 24 disc 2HE computer cage for the database.
From there on... I use 300gb Velociraptor discs for now. They area lot better price wise than the 15k SAS discs, still delivering quite a punch. I have them in general in RAID 10 configurations with a 64 kb stripe size. I am not sure SAS discs ever make sense... given that the price of a SSD is not that much higher and you can basically use a RAID 5 of SSD's to get better IO than a RAID 10 of SAS discs. This really needs to be calculated properly. Note that you do not buy "space" or even "bandwidth", but "random independant IO per second". SSD's often score 100 times as fast as SAS in this area.
* Make sure you properly format the discs. 95% of all servers are badly installed, costing them up to 40% io performance. To proper install align the partitions (automatic on 2008, manual on 2003) and FORMAT THE FILE SYSTEM WITH 64kb BLOCK SIZE. This is important because it means a 64kb block is a 64kb block on the RAID, and SQL Server likes to write out pages in 64kb clusters (8 pages of 8kb).
* Split Logs, Temp and Data. This is key - Logs may do a lot of writing, but it is mostly "linear". Sadly it is it sime sensitive, so if you make updates the head movement kills you... unless the logs are on separate discs.
* Do NOT AUTOGROW FILE SPACE. Period. That is evil. Sadly most people are too ignorant to read up those parts of the documentation, or do not care.
My layout is:
* 4 Velociratopros in a RAID 10 config. 64kb partition at the start for the OS, the rest is for the SQL Server logs.
* 8 Velociraptors in a RAID 10 config for the data. I also store interim backups there (i.e. backups taken before data cleanup etc.).
* 2 separate discs for Tempdb and temp logs. tempdb should never be written to (as per sql standard behavior - it only writes out when memory overflows), but the disc counter serves me as a warning. Basically if I get IO wait times here.... I can look for the reason.
I still have space... the cage has 24 slots for SAS / SATA Discs
As I start storing tick data.... I may add a RAID 5 of Velociraptors and move the binary data there (note: sql 2008, storing binary fields as files in a file system - nice feature).
Other imporant things:
* Make at least as many files for data and log and as well for temp data and log as you have processor cores. Another often overlooked item - certain operations require exclusive file locks (allocating file space for an object), so if you want parallelism... have as many files are you can have parallel operations. which is the number fof parallel threads executing. I.e. cores, or cores*2 if hyperthreading is in use.
* Use 64 bit. Point.
* Preallocate memory to about physical - 512mb (to give the OS some room). 64 bit may autoallocate a little wrong... so do it manually (mostly 2005 does allocate too much). If you have tons of memory, go hardware-1gb. Possibly around the 16gb limit.
To scale that down...
* Use a minimum of 2 discs (data, logs + os). I can not overstretch it. 2 discs give you easily 3-4 times the IO than one, simply because the head does not have to move between logs and data all the time.
* Get some RAM. Seriously, 8GB do not cost a lot anyway.
Depending on your type of usage... you may be IO or processor bound. Depends on whether you have lots of updates (io bound) or enough ram and complex queries (processor bound). Watch the performance counters. Getting fast discs is more or less a must in any case.
For performance analysis. Lots of people will point you to perf counters like "average disc quueue length" etc. - that is bullocks. It is valid, but it is too open for interpretation. What you care is how fast IO is handled...
...which is "seconds per IO", given in milliseconds. I do not care how long my average queue length is... if I get results in 1ms. Problem with queue length is that it REALLY depends on the hardware. For example, SATA discs can optimize access with up to 32 commands (reordering them), so there is nothing bad in that. SAN systems often have queue lengths of 64 upward - I have seen IO getting etter on some systems with queue lengths in excess of 100. So, this is really open to interpretation and hardware. But what is not interpretative is how many ms it takes to get an ANSWER from the system, and that is the counter I indicated. Once that goes higher... look for the issue. During my time at MS (SQL Server ENterprise Level 3 support - did performance stuff all the time), I saw people having response times in SECONDS and wondering their server was slow.
