SQL server: RAID1 with SSDs or RAID10 with regular HDs?

[AngryTechnician]

I'm currently investigating a storage upgrade to help add a bit of fizz to our SQL server.

 

The server only has a SATA interface, so SCSI or SAS is out as an option. I have 4 drive bays. The server is also a Hyper-V host (not clustered) for other servers that are not disk IO intensive, but I need to maintain a reasonable amount of disk space for those (more than I can afford using just SSDs).

 

These are the two scenarios I'm considering:

 

• 2x SSDs in RAID1 for the SQL guest (probably the Intel 320s) + 2x 10,000RPM hard disks in RAID1 (for the other guests)
  
• 4x 10,000RPM hard disks in RAID10 for all guests.
  

 

My thinking is that in RAID10 the 4x HDs should reach a sustained transfer rate approaching or even exceeding the RAID1 SSDs, due to being able to read off of 4 spindles at once. Writing will be slower, but that's the same for SSDs too. However, I suspect that the reduction in seek times will give the SSDs the advantage.

 

Your thoughts?

Edited October 6, 2011 by AngryTechnician

[DMcCoy]

What is the drive controller? The Intel ICH controllers do not (last time I looked) support trim with SSDs in a RAID array, only when they are attached as individual drives.

[AngryTechnician]

It is a Dell PERC S100.

[SYNACK]

Unless your taking hourly backups/or db replication of the SSDs I'd go HDD, SQL bashes the IO continuously and so without the database being replicated on another server with standard storage I'd be a bit iffy. The SSDs will be quicker but the safer option which also gives your other VMs more horesepower to work with is the four spindles option.

 

RAID controllers can also actually be harmful to the performance of SSDs because all the optimisation and cacheing of calls they do is designed for spindles and so can actually be slower than hitting the SSDs nativly or with a very thin layer of RAID overhead (read cheap RAID controller).

 

Depending on how many other VMs you have I'd give the whole lot as many spindles as possible as SATA is soooo slow (from personal experience) even with four of them.