Solved

SSD vs SAS disk for SQL

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

Accepted Solution

by:
jmac44 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 28

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 16

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 7

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 16

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 28

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 16

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 28

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 16

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
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.
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…

770 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