My company is currently setting up an application for a client that requires a Database server on the backend. We have selected SQL Server 2000 Enterprise as our DB but we are not sure how to size the hardware of the server. I have searched around and it appears that DB sizing is more experience/trial & error based than formula-based. I hope someone here can provide me with some advice if I provide the performance requirements of the server.
5 GB of data currently (will double in size every year) Approx. 10 million records (will double in size as well) 85,000 Transactions / hour 200 Concurrent users Client requires no greater than 1/2 second response time.
How many CPU's should be needed? Why? How much RAM should be needed? Why?
RAID 10 was the recommended fault tolerance. Agree? Disagree?
As you guessed there is no magical answer but here are some things to think about.
The actual size of the db is not too critical as long as you plan for growth and take into account how the drive arrays should be set up to achieve good performance. 85K per hour is less than 30 a second and although I wouldn't try that on a single processor box it is not too bad. (I routinely do over 800 a second with 4 Processors).
But since you need to handle Peak amounts and want less than 1 second response time you should be particularly aware of minimums. Ram will depend on how much of the data you actually use on a regular basis. You want to aim for enough ram to have all the data that you access on a regular basis in cache at one time leaving room for the procedure cache etc. Just because you have 5GB of data doesn't mean you will need 5GB of ram. You can always add ram if needed later just make sure you plan for growth ahead of time and save some ram slots so you don't have to throw away ram later to add more. It's always better to have more ram than not enough (don't forget to leave some for the OS too).
As for the hardwareI would shoot for the following:
RAID 1 (or 10) for the Log file(s).
RAID 5 or 10 for the data files (smaller and more disks vs less larger ones).
Raid 1 for the OS and SQL System files.
Depending on how much you will use tempdb (sorting etc) you may or may not want a separate Raid for tempdb.
If you do disk backups you may want to think about another RAID or 5 for the backups. In all cases make sure the arrays are expandable for future growth.
As for processors you will probably want to start with a 4 or 8 processor box with less than all the procs to begin with. The number of procs will depend on so many things but if you don't have enough you will probably see less than 1/2 second response time in peak loads while the rest of the time it will be fine. Poor code or schema is usually the reasons for needing more processors.