SQL Server: One 12-drive RAID-10 array or 2 arrays of 8-drives and 4-drives

Question

Setting up a box for SQL Server 2008, which would give the best performance (heavy OLTP)? The more drives in a RAID-10 array the better performance, but will losing 4 drives to dedicate them to the transaction logs give us more performance.

12-drives in RAID-10 plus one hot spare.

OR

8-drives in RAID-10 for database and 4-drives RAID-10 for transaction logs plus 2 hot spares (one for each array).

We have 14-drive slots to work with and it's an older PowerVault that doesn't support global hot spares.

Answer 1

I would go 10/2. Logs are usally small and sequential, and your RAID controller should be able to queue them efficiently enough to write them onto 2 disks in raid 1 without disturbing the rest. If your RAID controller can't do this then forget about any split and just go 12 if you are in a hurry. If you've got time run some tests and see what works. Shame about the lack of global hot spares, but anyway it sounds like you've got a decent box to play around with.

Answer 2

As far as my limited knowledge goes there are two main factors to consider

1. The database file operations will generally be random so you would want to maximise your IOPS for that disk where as your transaction logs file operations will generally be sequential so you want good raw throughput

2. You want to store your main database and transaction logs separately that way - if you lose your main database disks you can regen the DB from a backup + transaction logs, and if you just lose your transaction logs, you still have the main DB.

Of course its a lot more complicated that the summary above but thats the basics. Therefore I would say use two separate arrays and if you have the capacity to use RAID10 for both then great.

Answer 3

You should do pretty well with 12 drives in RAID-10 but make sure the server has decent amount of memory. The reasoning is if you have enough memory most of the data writes will be cached and many of writes will be consolidated in a single IO transaction after all when it comes to writing data to disk. This is especially true for OLTP database. The caching give you enough bandwidth for transaction log writes to the same drive. You can improve write performance even further by using hardware RAID controller with built-in cache protected by BBU in write-back mode.

Also note, that using 8+4 or 10+2 is good option as well, the only drawback that I can see with this is inefficient use of storage. Otherwise it is solid.

Answer 4

You must determine your workload first. And test, test, test (Can't stress that enough):

Firstly, the statement "the more disks in my RAID 10, the faster it is" isn't correct. This depends greatly on how your RAID system implements this RAID level (distribution of writes/reads/striping/buffers...I could go on).

Secondly, while it's always a great idea to store transaction logs in a different place (disk) than the DB, the effective gain depends on your workload, are there many, but small, writes, or are there few, but huge writes, what's the write/read ratio, how big are reads...etc. pp.

You say you're doing a lot of OLTP. From my understanding of this term, this means a lot of reads, not many writes (...does it? Confirm this first). In this case, it's fine to store the transaction logs in the same place - so I'd go for 12 drive.