Solved

SSD vs SAS disk for SQL

Posted on 2016-10-24
9
202 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 8

Accepted Solution

by:
Justin Moore earned 500 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 29

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:Justin Moore
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 29

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 29

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 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