SSD RAID for SQL server

Hello,
recently my HP server with SAS drives died and I had to create a copy using 3x WD RED in RAID 5, there are two Hyper-V VMs running off of those HDDs, one for Terminal services, other serving as MSSQL server with 10 heavy load databases, I think I don't have to mention that the write speeds are reaaally slow.
Since we have a new server, I think today better choice is using SSDs.

My question is what RAID would you choose (since I need some kind of disk failure protection, besides regular backups of VMs)? Do you think RAID10 would be better or RAID1?

I need about 2TB of storage, so I was thinking about 4x 1TB SSD in RAID10, or 2x 2TB SSD in RAID1, with third as a spare. I know that RAID5 is not recommended anymore, and RAID6 creates two sets of checksums, creating more write cycles on those SSDs. I was thinking about getting some Samsung 850 EVOs or Pros.

But I was also reading through the forum, and I found a post about slow write speeds on RAID10, and since the databases are under heavy load, I need a lot of IOPS. But I am sure anything with SSDs will be faster than SATA HDDs or even SAS HDDs. I just need good reliability and decent IOPS.

Thank you.
18 answers Last reply
More about ssd raid sql server
  1. You may be fine with physical disks for your table space if you use SSD for your transaction logs. Good database architecture would put those on separate physical devices. If you have a write intensive database, then you will need to buy much more expensive SSDs which have a high write endurance.

    RAM is going to be the best friend you can have for a database. The more RAM the better. 64GB or 128GB RAM might make all your I/O problems disappear.
  2. Thanks for your answer kanewolf. I would rather have everything on SSDs, since there are two VMs running on it, with 30 users working at the same time in one of the VMs, so it would be better to have SSDs.
    The server now has 10 core Intel Xeon E5-2630v4 with 32GB of RAM, but when I will be adding those SSDs, I will also be adding second Xeon and 32GB more RAM so that might help a lot.

    But I still can't decide what RAID to use for those SSDs, since I will need some kind of drive failure protection. I need some balance between reliability and write speed.
  3. Just keep in mind, not all RAID (SW or HW) are TRIM aware.
    if they are not, you will have serious perfromance degradation with SSD in RAID.
  4. n0ns3ns3 said:
    Just keep in mind, not all RAID (SW or HW) are TRIM aware.
    if they are not, you will have serious perfromance degradation with SSD in RAID.


    If I understand correctly, the OS has to have direct access to the drive to be able to use TRIM, so that wouldn't be possible with any HW RAID controller, only maybe setting it to HBA mode, which would just pass through the drives without RAID, which is no use for me.
    I have read somewhere else that on modern enterprise SSDs with good Garbage Collection and good firmware/controller combo, I don't need to worry about TRIM, or any performance issues.. is it right?
    Can you please give me some example of what SSDs and RAID controller should I use?

    Thanks.
  5. the garbage collectors are not file system aware. File systems usually just removes the pointer to the data from the records.
    So RAID solution that is aware of TRIM, just allows the relevant info exchange between OS and underlying SSD drives.
  6. n0ns3ns3 said:
    the garbage collectors are not file system aware. File systems usually just removes the pointer to the data from the records.
    So RAID solution that is aware of TRIM, just allows the relevant info exchange between OS and underlying SSD drives.


    So, I was looking at some LSI and Adaptec RAID controllers, and it seems that to this date none of them supports TRIM in RAID mode, I would have to use pass through mode, which would allow the OS to directly access these drives and then make a software RAID inside the OS. But I don't like this idea much, because then I just can't take these drives with controller and put them to different machine.
  7. InToSSH said:
    Thanks for your answer kanewolf. I would rather have everything on SSDs, since there are two VMs running on it, with 30 users working at the same time in one of the VMs, so it would be better to have SSDs.
    The server now has 10 core Intel Xeon E5-2630v4 with 32GB of RAM, but when I will be adding those SSDs, I will also be adding second Xeon and 32GB more RAM so that might help a lot.

    But I still can't decide what RAID to use for those SSDs, since I will need some kind of drive failure protection. I need some balance between reliability and write speed.



    It will ALWAYS be better to have RAM for a database. 32GB with 20 threads is very small on RAM, especially when you have multiple VMs. You should have started with 64GB and if you add another CPU add 64 IMO.
  8. why not to try to use single SSD of required size ?
    even non NVMe drives have ~100K IOPS. and there are up to 4TB consumer (sanely priced) drives out there
  9. kanewolf said:

    It will ALWAYS be better to have RAM for a database. 32GB with 20 threads is very small on RAM, especially when you have multiple VMs. You should have started with 64GB and if you add another CPU add 64 IMO.


    You might be right.. since I haven't bought the second set yet, I will think about getting 64GB for the second CPU, which will total in 96GB RAM, and then if that is not enough I will swap the current set of 32GB.

    Quote:
    why not to try to use single SSD of required size ?
    even non NVMe drives have ~100K IOPS. and there are up to 4TB consumer (sanely priced) drives out there


    I was thinking about the same, that it would be much easier to maintain, with usable TRIM. But I don't like the idea of not having a backup redundant drive, in case this one fails. There will be just two VM HDDs on that drive, making it much easier to backup, so that might help aswell. Or I might just add second single SSD, which will be creating a mirror copy of those VMs every 12 hours.
  10. It's not that bad, I have a mac mini with single SATA SSD drive that is used as ESXi. 3-5 Mac VMs are running simultaneously without problem. And they are backed up once a day (early morning after nightly automation is finished).
  11. n0ns3ns3 said:
    It's not that bad, I have a mac mini with single SATA SSD drive that is used as ESXi. 3-5 Mac VMs are running simultaneously without problem. And they are backed up once a day (early morning after nightly automation is finished).


    Alright, I guess I will go with single 2TB enterprise grade SSD, with regular backups, I think TRIM will be really useful when there will be many database transactions, which are low size, but lots of write cycles.

    Do you think if I used Soft RAID which is directly in Windows Server that I could still use TRIM? Since OS will have direct access to these drives? I would add 2TB SSDs in RAID 1.

    Is there any automated software which could notify me in case the SSD is degrading and is getting close to be replaced?
  12. Can't really answer the SW RAID question.
    as for the monitoring software, yes, there are solutions that do this. It will rely on S.M.A.R.T reports. I'll check if there is something stand alone
  13. n0ns3ns3 said:
    Can't really answer the SW RAID question.
    as for the monitoring software, yes, there are solutions that do this. It will rely on S.M.A.R.T reports. I'll check if there is something stand alone


    Thanks a lot. I would really like to use some kind of RAID (but I don't have any experience with Windows RAID, so I don't know how it works with recreating the array in case of the host OS failure), because even one day old backup of the VMs is pretty old for use, since in one day there are many changes in the DB (accounting, projects, and so on).
  14. you have 2 types of raid in windows:
    1. the one on motherboard that is defined in bios - HW assisted SW raid with classic 0,1,10,5 etc.
    2. logical volumes that windows creates usually to hide the physical discs from user and combine the multiple disks into one
  15. Yes.. I think TRIM wouldn't be possible with motherboard RAID.. But I was talking about the second option. Creating logical volumes in Windows and mirroring them inside the OS.
  16. Have very little experience with that, so can't really help
  17. Hmmmm, this is strange. I just remembered that I have already put two 500GB SSDs for the Host OS to this server, they are in Intel Raid 1, directly from MB, and Windows looks like it notices that there are SSDs connected to it. Is it possible that Intel Raid is able to pass TRIM command for Raid 1?

  18. It is probable .
Ask a new question

Read More

Hard Drives SQL Server Storage Servers NAS / RAID SSD Hewlett Packard