Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSD vs SAS disk for SQL

Posted on 2016-10-24
9
Medium Priority
?
873 Views
Last Modified: 2016-10-27
We are going to create a new virtual machine for an SQL 2012 server on ESXi 5.5.  The host data stores consist of an 800Gb RAID 10 with SSD's and 900Gb RAID 10 with 10k SAS disks from local storage and a SAN connection with 2 RAID 5 sets of 9 900Gb 10K SAS disks(LUN 1 and 2).  What drives should I consider installing the temp db, log files, and SQL db on?  Our db is 60/40 read write.
0
Comment
Question by:NytroZ
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Accepted Solution

by:
jmac44 earned 2000 total points
ID: 41857725
the consensuses is SSD there's a good article here that you can read. http://sqlmag.com/storage/using-solid-state-disks-sql-server-storage-solutions

SSD's rock. I've been using a raid 5 SSD array for the last 3 years for our DB. It's not SQL but IBM's old Universe U2 db but it is lightning fast and made a world of difference and I have not had any problems. If you have SQL Std and already have 64Mb RAM using SSD will definitely be the way to upgrade without upgrading the software and installing more RAM
0
 
LVL 31

Expert Comment

by:serialband
ID: 41857746
Just FYI. They do have SAS SSDs too.  They should be more reliable and higher in quality than SATA SSDs.
1
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 41858520
Please Note that RAID-5 is now not recomended for business use.

Modern large disks have extended the rebuild time to such an extent that the RISK of a second disk failing during the rebuild of the first has now reached an unacceptable level. Use RAID-6 or RAID10 instead.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 9

Expert Comment

by:jmac44
ID: 41861199
Gerald,

What is the size limit? I have an 8 disk Raid 5 with 2Tb SATA 7.2k. I haven't had to rebuild yet. Did I shoot myself in the foot? Dell built it and new of and approved the raid 5 configuration at the time.
0
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 41861567
The general recommendation is for disk over 750GB

NB Some dell documentation describes RAID10 incorrectly - RAID10 is generally accepted to be Mirrorsets that are then striped. ie Multiple Mirrorsets, one Stripe
0
 
LVL 31

Expert Comment

by:serialband
ID: 41862184
RAID 10 is not too much more redundant than RAID 6, and you do lose quite a lot of storage space.  If you have the ability to do RAID 6, then go for that.

There is nothing inherently wrong with RAID 5.  RAID 5 was the standard until RAID 6 was created.  RAID 5 can suffer 1 disk failure.  What people did back then was put 7 disks in RAID 5 and had 1 hot spare or put all 8 disks in RAID 5 and kept a cold spare and immediately swapped out that failed disk as soon as it failed.  The problem was that a 2nd disk would fail within a week or 2 of the first disk, sometimes during the rebuild, because all the disks were bought from the exact same batch.  That's why they had to invent RAID 6.  Some consultants started buying disks in multiple separate batches and mixed them to reduce that effect.  My old company did that.

Nowadays, people use RAID 6 if they have it, so they don't have to worry about a 2nd disk failing so soon after the 1st disk, before the RAID rebuilds.  RAID 6 makes people lazier about buying all the disks from the same batch.  Part of it is for uptime, but part of it is that people don't really do backups or if they do, they never actually test recovery from backups.

About half the places that I consulted for, long ago, that said they have backups, didn't have backups because they never tested them.  It was a major failure at many shops when they try to recover and found that they never actually backed up any data.  Make sure you also test your backups on a regular basis no matter which RAID level you use.
0
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 41862242
@serialband - RAID-6 has been around along time (15+ years) so its not something new. What is new is that RAID-5 with modern disks is now too risky to recomend for use.

RAID10 does have the disadvantage of only 50% utilisation, but it is generally faster than RAID-6 for reads and a lot faster for writes.

i am with you on your backup comments. We see so many questions on here where no backup is available.


I havent seen these calculations verified, but this web-site purports to be able to calculate the risk - http://www.raid-failure.com/raid5-failure.aspx

This is an paper on disk reliability - https://www.microsoft.com/en-us/research/wp-content/uploads/2005/12/tr-2005-166.pdf

and this is from somebody who uses just one or two disks - https://www.backblaze.com/blog/hard-drive-smart-stats/
0
 
LVL 31

Expert Comment

by:serialband
ID: 41862319
RAID-6 has been around along time (15+ years)
Yes, and RAID 5 has been around much longer.  What I describe is accurate.  While RAID 6 has been around, it really was mainly utilized on higher end RAID units.  Most small biz still use RAID 5 arrays.  Most home and small biz NAS units still don't support RAID 6, and they don't really have enough drive bays to make it cost effective.

SSDs should rebuild much faster than spinning disks, so RAID 5 should still work well enough for SSDs.  Get some SAS SSDs for the reliability factor, or buy extra spares if you go with SATA SSDs.  Again, always test your backups.
http://www.theregister.co.uk/2016/05/13/disak_versus_ssd_raid_rebuild_times/
1
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 41863397
@serialband - dont need to debate when RAID-6 came along and why, but its well known for its slow write speed.
I do agree on the SSD front, but the message needs to out to those home and business users that if they are using spinning rust then they should avoid using RAID-5 with anything approaching 1TB or larger as they are putting their data at RISK.

Not only should people test their backups, just have some backups in the first place!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The business world is becoming increasingly integrated with tech. It’s not just for a select few anymore — but what about if you have a small business? It may be easier than you think to integrate technology into your small business, and it’s likely…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question