At the end of a presentation regarding SQL Server and hardware, I’m inevitably asked a question along the lines of:

“What specification of server should I use for running x?”

My answer inevitably starts like this:

“Well, it all depends…”

If people thought about the question a little more, they would probably realize how difficult it is to give any sort of sensible response. It’s akin to asking a stranger how he or she thinks you should get to work. To offer you any advice would require answers to all of the following questions:

1. Where do you work?
2. Do you only work in once place?
3. Do you have any preferences to forms of travel?
4. When do you have to get to work?
5. Do you have to go with someone else?
6. Do you have to be able to get to work whatever the situation?
7. What is your budget for travel?
8. Do you have a limit on the amount of time you can travel?
9. Do you like to use your travel time for other means? i.e. reading
10. How long are you going to be working at this location?
11. What forms of transport can you use?

In other words…it all depends.

In IT terms, all the above can be translated into the equivalent questions about hardware:

1. What are you using your hardware for?
2. Do you have different applications requiring hardware?
3. What hardware can you use, do you have a preferred supplier, can you only use 1U servers?
4. What response time do you want to get?
5. Do you already have hardware you need to fit in with?
6. Do you need to have redundancy in your infrastructure?
7. What is your budget for hardware?
8. How many users do you have?
9. What different types of users do you have?
10. What applications do you plan on using in the future?
11. What skills do you have in your organisation?

Each question has a multitude of possible answers, resulting in a huge number of permutations. Even if a question had only four options, that’s still one million permutations that you are expecting someone to process in a Q&A session or a presentation.

Of course, there will be some questions that you can’t answer, so you’ll need to make an educated guess. If you need a formula for specing out a database system, I would use the following:

Hardware spec = ((GB+ U) x S + DR)  x  1  
                                  DIQ                     BIQ 

BIQ = Business Knowledge of what they are doing in the next 3 years
DIQ = Knowledge and skill in the development of a performant/scalable system
GB = Volume of the Data
U = Number of users
S = Responsiveness of the system
DR = Requirement for DR

This equation dictates that,

• The bigger the data, the bigger the system
• The less skill the developers have, the bigger the system
• The more users, the bigger the system
• The faster the users want the system, the bigger the system
• The less knowledgeable the business about what they want to do, the bigger the system

Furthermore, the following equation will help you estimate the amount you’re likely to spend supporting the hardware,

Support Cost = 2(Calc/Act)

where:

Calc = Calculated cost of hardware
Act  = Actual spend on hardware

If you don’t spend what you should up front, then you will end up spending more on support in the long run.

Put simply; it all depends. However, “it all depends” leave you no further down the line to an answer. So where do you start? Given that hardware is cheap (£3000 for a 8-way server with 8Gb of RAM), and employee time is expensive, here are my recommendations:

1. Get as much memory as possible; you want as much of your database in memory as possible.
2. Make sure you have fast enough disk, one transaction is at least one IO, so if you want 1000 transactions/sec you need 1000 IOs on your log.
3. Make sure you have fast enough disk (I know I’ve already said it) so that when you can’t do 1. you don’t suffer too much
4. If you are running a multi-user system, get as many processors as possible so you can process more queries at once.
5. If you are using a SAN make sure you can monitor the performance of it; all the disks, the switches, everything. And don’t share it with Exchange
6. Make sure you write good code or points 1-5 won’t make a difference if every query reads too much data, causes too much blocking and results in your multi-user system scaling to 1 user.
7. Performance-test your application so you know how it breathes: How does it use CPU, memory and disk? Wembley Stadium might be great because it can hold 90,000 people but still takes ages to get out, because the roads and tube railway to it have hardly changed since 1930.

We have invested in HP DL585s in my current place, for ~£5k you get a 64 bit 4 way box with 8Gb of ram (storage not included). I’m currently looking at a workstation with 2 x Dual core with 16Gb, 5 SATA 146Gb drives for ~£2,500. I personally am going for the AMD dual core chips because you will be able to upgrade to quad cores when they come out. That provides a very easy and cheap upgrade path if CPU is a bottleneck.

I haven’t discussed the issues of redundancy, high availability or supportability, which is touched on elsewhere on Simple-Talk in Database High-Availability: Soup to Nuts. Hopefully, I’ll cover these points in another article.