SSD RAID for SQL server

Toggle sidebar Toggle sidebar
I

InToSSH

Reputable
May 30, 2014
12
0
4,510
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.
 
Sort by date Sort by votes
kanewolf

kanewolf

Titan
Moderator
May 29, 2013
37,278
3,264
156,790
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.
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510
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.

 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510


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.
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510


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.
 
Upvote 0 Downvote
kanewolf

kanewolf

Titan
Moderator
May 29, 2013
37,278
3,264
156,790


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.
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510


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.

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
Click to expand...

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.
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510


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?
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510


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).
 
Upvote 0 Downvote
n0ns3ns3

n0ns3ns3

Illustrious
May 25, 2016
8,065
4
42,715
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
 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510
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.

 
Upvote 0 Downvote
I

InToSSH

Reputable
May 30, 2014
12
0
4,510
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?

5b291721ed4f45856683303596b8e0f7.png
 
Upvote 0 Downvote
You must log in or register to reply here.

TRENDING THREADS

Latest posts

Moderators online

Share this page

COMPANY
RESOURCES
FOLLOW
Top Bottom