Rules to choose hardware for OLTP systems (sql server)

Question

Ok. We know database size, number of concurrent users, number of transactions per minute; should choose number of processors, RAID, RAM, mirroring and clustering.

There are no exact rule.. but may be there are no rules at all?

In my practice in every case I have "legacy" system, and after some inspections and interview I can form an opinion how hardware and design can be improved.

But every time when I meet "absolutely" new system (I guess there are no new systems, but sometimes are such tasks) I can't say anything trustful. So I'm interesting how people deal with such tasks? They map task on theirs experience or have some base formulas?

Answer 1

It probably depends more on what the types of transactions are and how good your database was designed. Things that are also relevant will be how much of it's read vs write (very important for RAID config), are there complex JOINs, etc. It's really about the quality of database and application design, as much as it is about how many concurrent users and transactions. Thus, I would say that there are no rules for a generic OLTP database, all you can do is make rules (and possible optimizations) for your OLTP database and application. Imagine if you had no indexes - your database is going to perform very poorly.

Start with a test server and put a known load against it and see where the bottlenecks are, with Profiler. Then fix those, and increase the load.

Answer 2

If you have a certain test server, you can make some tests without having installed SQL or deployed any database.

I heard good stuff about sqliosim.exe tool used to simulate the SQL activity on brand new Operating System without anything running on it. I never tested it because I didn't have such an environment but it is not so hard to configure.

The results will guide you in figuring out whether CPU, Memory or Disk are enough for you system.

You need to have some benchmarks to know how much CPU, Memory or disk you need. However, there are some best practices on OLTP databases for Disk config: RAID 10 and RAID 5 seem to be best choice.

Raid 10 suited for increased write activity - Log files (100 % is write activity) and Data files (if write activity is more than 10 % of I/O operations).

Raid 5 suited for increased read activity - Data Files if write activity is less than 10 % of I/O operations.

Also Paul Randal's blog can help you because he had done many Hardware testings and came to reasonable conclusions: http://www.sqlskills.com/BLOGS/PAUL/category/Performance.aspx