11

I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.

I am going to make new RAID configuration. I have 6 SCSI 73 GB drives inside the machine and an IBM ServerRAID 8K controler. What would be a good way to set the RAID levels? Should I have two, three or one field on my controler?

I am considering to make one of following solutions:

  1. Use all the disk and make a RAID 10 pool.
  2. Use 4 disks for a RAID 1e pool and use it to store the database data and OS, and use the other 2 disks in a RAID 0 pool and use that to store the database logs.
  3. Some other combination.

Is a larger stripe unit size better?

This server will be a subscriber to a replicated database. Its primary task is going to be reporting and data retrieval, with only the replication agent making writes. The size of the database is around 90 GB.

Improve this question
4
  • are you using the terms RAID1E and RAID10 interchangebly?
    – Jack Douglas
    Commented Nov 11, 2011 at 16:07
  • also, 3x73~220GB - how much are you expecting your 90GB DB to grow each year?
    – Jack Douglas
    Commented Nov 11, 2011 at 16:08
  • @JackDouglas This database with 90 GB is from 2006 year till now, I think that it can grow up in next two years max around 30%
    – adopilot
    Commented Nov 11, 2011 at 16:10
  • I like to over-provision storage as much as possible when building a server from scratch. Do you have any second-tier storage as well (eg SATA)? It can be very handy to a decent amount of breathing room, especially when you end up in a DR situation.
    – Jack Douglas
    Commented Nov 11, 2011 at 16:16

4 Answers 4

Reset to default
6

I vote for option 1. Bear in mind that RAID 0 means "no protection" - do your logs matter? (yes they do).

It also has the benefit of simplicity

The SQL Server docs say:

For optimized I/O parallelism, use 64 KB or 256 KB stripe size.

But it is usually good to go with the controller default IMO

Improve this answer
5

Notes:

  • The OS + binaries will be 40GB
  • 6 disks doesn't leave you many options
  • Where do you want your backups?
  • Let the RAID controller choose the stripe size (depends on RAID level). This is micro optimisation and you'll see conflicting data
  • Log file write speed determines your throughput
  • You have to allow for free space = 1.2 times biggest table inside your MDF for index rebuilds
  • RAID 0 = idiotic in a server that has data of any value

I'd go for one RAID 10 array

Improve this answer
4

I'd go for either two RAID1 volumes with three mirrors each, or a RAID10 with striping across two sets of three mirrors.

Rationale: two mirrors is too little, period. Your server will not get to do proper consistency checks more often than once a month, so it is quite likely that you have a few bad blocks. These do not matter as long as the other mirror is still intact, but when one disk fails, it is likely that you won't be able to recover from the other one. Hence, three mirrors.

RAID6 is a bad idea in a database setup as writes tend to be small, which turns them into read-modify-write operations in the background.

Whether two RAID1 volumes or one RAID10 is better for you depends on your application. If it is likely that you need the entire space, go for RAID10, otherwise I'd suggest one volume for system and indexes, and one for data pages. If you go for two volumes, check that your controller allows extending a RAID1 to a RAID10 later on in case you need to expand.

Improve this answer
8
  • +1 for mentioning the problem mirroring does not solve (at least on its own). It is possible to configure scrubbing to take place more regularly on Linux, is the same true for Windows? However you repeat the myth that indexes and data should ideally be separated - there is an argument for keeping logs separate but indexes and data are best striped together (over as many spindles as possible)
    – Jack Douglas
    Commented Nov 12, 2011 at 12:54
  • I think it should be possible to configure more frequent checks -- however database performance generally drops massively, so I'd rather use extra drives and fewer checks.
    – Simon Richter
    Commented Nov 14, 2011 at 9:18
  • I usually keep indexes separate so I can use different cache and readahead behaviour -- a full table scan typically benefits from readahead, while index scans do so less often.
    – Simon Richter
    Commented Nov 14, 2011 at 9:25
  • On my Debian system, /usr/share/mdadm/checkarray runs with the --idle flag by default so I would not expect a noticeable performance hit. In the OPs case this will be handled by the RAID controller and I'd guess it will do it regardless of whether you have 2 or 3 mirrors so the the hit (if any) will be there anyway.
    – Jack Douglas
    Commented Nov 14, 2011 at 9:53
  • re the index scans, fair point but I'd suggest this is micro-optimisation not general tuning advice :) I'm going to ask around in chat to see what others think - do feel free to chip in...
    – Jack Douglas
    Commented Nov 14, 2011 at 9:55
3

I agree with Douglas. One common raid-10 is a good start as long as you don't run out of diskspace. A smaller stripe size would better for DB access. Using different partitions only make you run out of space on one partition faster.

The thing with raid-0 is not just that the data can be brought from backups or is deletable, but it also means downtime everytime your disk breaks. Is your machine doing some data crunching where a few day's downtime won't matter? Even on NBD support it might take some six days to get a new disk.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.