I'm glad to see that so many readers have enjoyed my article, called Bare Metal Tuning, in this month's SQL Server Magazine. (Find the article on-line at http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/SQLServer_46492.html.) I’ve also enjoyed answering the many questions that readers have sent in. The most common question I’ve been getting is “What configuration would you recommend for a basic low-, medium-, and high-end SQL Server?”
I’m sure that some of my MVP buddies who spend a lot of time with hardware can make even better recommendations that what I’m going to put here. However, I have spent a long time building and configuring servers for quick deployment into production. If you want to figure out some good, reusable server configurations, I have some recommendations for you.
My first suggestion is to go to http://h71019.www7.hp.com/ActiveAnswers/cache/70729-0-0-225-121.aspx. You might have to go to http://www.hp.com/ActiveAnswers first and create a log in. The registration is free and well worth it. From there you can download the HP SQL Server Transaction Processing Sizer for MS SQL Server 2000. It's a neat tool that will give you very specific details about the best sort of hardware for your application. If you use another hardware brand, you can just extrapolate from what the HP Sizer tells you.
These specs are a little bit old, but here are some specs that I commonly used as a rule of thumb. (Remember that D: drive is always the CD/DVD-ROM.)
Minimum Setup (a small application with a maximum of 100-200 tps)
Fastest CPU with moderate L2 and L4 Cache
2 CPUs (hyperthreading)
1 NIC
3gb RAM (or more if it's cheap)
2 physical, independent RAID volumes:
C: - RAID1 for executables, OS, and databases
E: - RAID5 for t-logs and backups
Standard Edition
Medium Setup (a small-to-medium application with a max of 500 tps)
4 CPUs
2 NICs
4gb RAM
3 physical, independent RAID volumes:
C: - RAID1 for execs
E: - RAID1 for logs/backups
F: - RAID1 for databases
Standard Edition
Strong Setup (a medium-to-large setup with a max of 2000 tps, anything bigger than this should be custom configured)
8 CPUs
2 NICs
8-16gb RAM
4 physical, independent RAID volumes:
C: - RAID1 for execs
E: - RAID1 for logs/backups
F: - RAID1 for databases
G:- RAID1 for tempdb and/or secondary file group for main database to split I/O
I know there are smarter folks out there than me on hardware. How do these specs sound to you?
Cheers!
-Kevin
