We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Configuration of RAID for MS SQL Server 2019/VM

Medium Priority
85 Views
Last Modified: 2020-06-10
Company had an aging 2012 sql server.  No applications-- they are just using it for querying data and at any given time are working with ~10 DBs about of varying sizes 5gb to 500gb.  They bought
SQL Server 2019 / Windows Server 2019 VM on Server 2019 Hyper-V host.  HPE DL380 Gen10 with P408i-a > SAS expander > 2x8 cages. So 16 spots to work with.  They've got 5x 1.9tb SATA SSDs and 10x SAS SSDs.

How would y'all configure the RAID?  Given the two different interfaces, I believe they need to do at least two arrays.  So I'm thinking
1. 2x SATA in RAID 1 for OS
2. 2x SATA in RAID 1
3. 1x SATA spare
4. RAID 1+0 for the SAS drives (says I'll end up with 8.7TiB)

Other options are Raid 10 (ADM) if I drop down to 9 SAS drives and it drops the size to 5.2TiB, but I'm not sure what the difference is there.  Also RAID 60 is an option, and bumps the size to 10.4TiB.  I haven't set up a SQL server since we were using spinning drives and it was supposedly better to have a separate array for tempdb / log / data.

I'm also not sure how the VM comes into play.  I assume I'll set up a VHD at least for each logical drive I set up, possibly more if it is kosher to do so these days.
Comment
Watch Question

Andrew Hancock (VMware vExpert PRO / EE Fellow)VMware and Virtualization Consultant
CERTIFIED EXPERT
Fellow
Expert of the Year 2017

Commented:
Are using Hyper-V?

Underlying datastore the VM is store on for performance workloads should be RAID10

The more disks you have in the datastore the more IOPS your datastore has.

You can still if you wish divide OS DB LOGS across different virtual disks do not use partitions on a single disk
Matt Dsysadmin

Author

Commented:
Thank you.  Yes, Hyper-V -- I apologize for that omission.  If you had to break off logs or tempdb into a separate array, which do you think would be more beneficial?  I just don't want them to waste the extra SATA drives.  I guess I could just add to the OS array, but 2tb was already overkill imo.

Andrew Hancock (VMware vExpert PRO / EE Fellow)VMware and Virtualization Consultant
CERTIFIED EXPERT
Fellow
Expert of the Year 2017

Commented:
logs

But please have a read of these EE Artilces by fellow Hyper-V Expert Philip Elder

Some Hyper-V Hardware and Software Best Practices

Best Practices - Network - Building Hyper-V Infrastructure

Practical Hyper-V Performance Expectations

this is how your Hyper-V server should be setup if you are doing VMS!
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
rraid 10 fault tolerance for disks  n/2 where n is the number of drives in a raid. Disk capacity n/2
RAID 6 has a fault tolerance of two disks no matter how many drives are used storage capacity n-2
Raid60 is a mirror of two tRAID 6s you'd have fault tolerance of 4 disks 2 per raid 6 the cost
The disk capacity is n/2-2

How active is your SQL ,misse of DB, number of user?
Network Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Matt Dsysadmin

Author

Commented:
Thanks, Andrew!  I will definitely read those now.

Hi Arnold, it's pretty low IMO, my base is coming from using SQL for applications.  Here they just dump datasets into it and run queries against them.  Right now (as we speak) there's two users using two databases.  Often only one person will use a database at a time, sometimes two, and the total simultaneous users across all databases never goes above 8 or so. To give you an idea, right now their 2012 is running on 2x e5-2403's with 96gb ram and 10k drives in a VRTX, and they don't really complain about the speed much.  They will probably think something is wrong when they run their first queries against it and they come back immediately.

Thanks kevinhsieh.  So you are saying 
  • SATA array
    • Hyper-V/Host OS partition
    • Partition containing VHDX for VM OS
  • SAS array
    • Partition containing
      • VHDX for DB data
      • VHDX for logs
      • VHDX for tempdb
I'm curious why RAID 5?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Raid 5 single point of failure, single disk.
If you are using SSD, ....access, recovery on failure.... Does not risk a secondary failure...capacity n-1
andyaldersaggar maker's framemaker
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
RAID 10 ADM would cost you a pretty penny, it's a 3 way mirror so you lose two thirds of your capacity. It's good for random reads though since all of those 3 disks in the mirror can be doing I/O.
Andrew Hancock (VMware vExpert PRO / EE Fellow)VMware and Virtualization Consultant
CERTIFIED EXPERT
Fellow
Expert of the Year 2017

Commented:
do they use a physical server at present, and they are migrating to a virtual machine ?
kevinhsiehNetwork Engineer
CERTIFIED EXPERT

Commented:
Since when is RAID 5 an issue with SSD? You are allowed to lose a drive and rebuild successfully.

Put all of the data on a single SSD and it should perform better than all the existing SAS disks. More SSDs is mostly about increased capacity and fault tolerance, not performance.


Matt Dsysadmin

Author

Commented:
Andrew-- they currently use a VM.  Starting from scratch, not migrating.  They don't seem to think it's necessary to move the old databases over and will just start new databases on the new server as they come up.

kevinhsieh-- Thanks for the clarification.  I was under the impression that 5 takes a big write speed hit compared to 10.
David Johnson, CDSimple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
RAID5/6 do have longer writes than raid 10 but you are doing 90% reading and 10% writing.  This is why we have RAID CACHING
As long as you stay with a maximum virtual disk < 10TB Raid 5 is fine otherwise because of the way drives do error correction and URE's go raid 6
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
just  to correct, raid 10 is a stripe of mirrors most of the time versus a mirror of stripes,
Distinction Ina mirror of stripes a loss of a single drive,results in a loss of a strip where itis a member. A loss of a single drive on the other stripes brings downtime volume,losing data.
In a stripe of mirrors each mirror pair can lose a drive without a performance impact volume will crash if both members of a single mirrored pair fail.


You lose 50%!of capacity not 66% in a RAID 10.
andyaldersaggar maker's framemaker
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
arnold> You lose 50% !of capacity not 66% in a RAID 10

Correct but you lose 66% if you use RAID 10 ADM.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Andy, please clarify. Not sure I follow.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
disks are cheap. raid 10.is the only raid I’d ever do unless it’s a backup storage server or something where I need a ton of space and can’t shove any more disks in.  don’t mix different performing drives together so a raid10 of the sas and a raid 10 of the Sata. always have current backups in the case of a failure during a rebuild. 3 way mirror isn’t worth it IMO.
kevinhsiehNetwork Engineer
CERTIFIED EXPERT

Commented:
@Aaron, OP is dealing with five SATA SSD and ten SAS SSD. No spinning disks here.
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
@kevin and? even if I didn’t see that already it still wouldn’t change my recommendation, I wouldn’t mix sas and sata in a raid, spinning or ssd doesn’t matter.
kevinhsiehNetwork Engineer
CERTIFIED EXPERT

Commented:
@aaron I wouldn't mix the SAS and SATA either, and I recommended that OP put SAS SSD into a RAID group, and SATA SSD into separate RAID group.

I disagree with the point that (SSD) disk is cheap and should only be used in RAID 1/10, or RAID 6 for large backup storage. Those are valid recommendations for spinning disk, but IMHO not for SSD.

I believe that RAID 1/5 are fine for most use cases with SSD. It is RAID 10 and 6 that are unusual use cases. RAID 10 would be if you needed throughput greater than what you can get from parity RAID on your controller, or you have so many writes that you're really trying to reduce write amplification. RAID 6 with SSD would be for very large data sets that you would really not want to try to restore. For example, my backup server uses RAID 6 with 184 TB raw storage.
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
@kevin ok, sounds like we are on the same page with sas/sata. the rest just comes down to size vs performance requirements. my days of waiting a week for a raid rebuild managing users complaining about performance are over. it all depends on what other gear you have and how you can manage all sorts of failures.
Matt Dsysadmin

Author

Commented:
Oh geez, there's 50 and 60 too.

Just looking at the calculations, I am leaning a little more towards recommending 5.  Will rebuild times be significantly longer when going with 5 as opposed to 10?
kevinhsiehNetwork Engineer
CERTIFIED EXPERT

Commented:
Rebuild times for PAID 1/10 is determined by the size of the drive and how quickly the entire drive can be read and written. Larger drives take longer.
Parity RAID rebuild speed is determined by the size of the drive and the total size of the RAID group, as all data needs to be read in order to do the rebuild for RAID 5, and I believe that N-2 drives need to be read for a RAID 6 rebuild, but it could be N-1 drives need to be read.
With SSD, you generally don't worry about it too much, and rebuilding from a failed RAID 5 or 6 is much gfaster using SSD vs HDD, and the performance hit is much less.
Considering that this is for essentially a low usage, low load reporting system, this is way over engineered. As I said before, a pair of QLC enterprise SATA SSD should meet the requirements if total data is <7 TB. If you need 14 TB storage, use three 7.68 drives in RAID 5 and call it a day.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
https://www.pcmag.com/news/raid-levels-explained might help in RAID information.


You have to know there are RAID 50, 51 60 61

stripe of RAID 5s or 6s and a mirror of RAID 5s or 6s.

The point is to build redundancy and to manage a tolerance for points of failures.

i.e. if you have a 20 minute trip to fix an issue should it occur, a tolerance for a single point of failure is enough.
If it takes time and preparation and can take you a day or longer to get on site to fix an issue, a single point of failure is not a good risk planing.

Consider the recent launch of the US Nasa Crew.
They build in redundancies all over the place from communications, to computers, to systems such that they can tolerate a loss of significant sets of controls without risking the life of the crew.

note you are only looking at storage. You likely have dual power supply in the systems to manage power.

I only focused on the SQL side, as Kevin pointed out, you want this system for reporting only,


If you are in an upgrade cycle, you could/should consider the use of an Always On setup for the sql as building in a redundancy on the SQL server level as well as facilitating the access to current data on the read-only node.

i.e. in the current environment should the existing SQL server experiences a failure. what happens?
Matt Dsysadmin

Author

Commented:
Turns out they have 14 of the 2tb ssd sas disks, and I do want them to have more capacity, so if that many drives in RAID 5 is fine, I guess I'll go that route.  Thanks everyone.
Based on what David Johnson said
As long as you stay with a maximum virtual disk < 10TB Raid 5 is fine otherwise because of the way drives do error correction and URE's go raid 6
I guess I'll actually do raid 6, since they will most likely have VHDX > 10TB.

I've suggested larger QLC but they want to work with what they have.  Maybe I can talk them into switching over eventually, because yes this seems convoluted.  Or get them off physical and into azure.

Thanks everyone!
andyaldersaggar maker's framemaker
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
There are 2 M.2 slots on the primary riser that you could install the OS/hypervisor on, mind you HPE's prices are a bit steep.
Matt Dsysadmin

Author

Commented:
I saw those, thanks.  I actually was looking at a 3rd cage for u.2, but there are multiple components to purchase to get it to work, and as you said, HPE isn't cheap.  I'll have to pick up a DL380 Gen10 to play with and see what the performance is compared to this sas setup.

So I'm going with

  • SATA array
    • Hyper-V/Host OS partition
    • Partition containing VHDX for VM OS
  • SAS array
    • Partition containing
      • VHDX for DB data
      • VHDX for logs
      • VHDX for tempdb

Is there any benefit to using ReFS in any of these?  I guess I'll post that separately.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.