Solved

SSD vs SAS disk for SQL

Posted on 2016-10-24
9
74 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 4

Accepted Solution

by:
jmac44 earned 500 total points
Comment Utility
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 27

Expert Comment

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

Expert Comment

by:Gerald Connolly
Comment Utility
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
 
LVL 4

Expert Comment

by:jmac44
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 16

Expert Comment

by:Gerald Connolly
Comment Utility
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 27

Expert Comment

by:serialband
Comment Utility
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 16

Expert Comment

by:Gerald Connolly
Comment Utility
@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 27

Expert Comment

by:serialband
Comment Utility
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 16

Expert Comment

by:Gerald Connolly
Comment Utility
@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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now