Solved

SSD vs SAS disk for SQL

Posted on 2016-10-24
9
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

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
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!

 
LVL 9

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

Independent Software Vendors: 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

Suggested Solutions

In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
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.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

751 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