Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Loss Expand / Collapse
Author
Message
Posted Saturday, March 2, 2013 12:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Comments posted to this topic are about the item Data Loss






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1425887
Posted Saturday, March 2, 2013 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 5:23 AM
Points: 5, Visits: 35
SSD drives have a terribly high failure rate. However even with mission critical financial data they are still worthy upgrades. I've replaced SCSI arrays with 14 drives that had my dB partitioned with various tables here, log files there and all sorts or performance configurations with a mirrored pair of SATA SSDs and gained a 20x-60x increase in speed. I don't even remember how much that huge array cost when it was purchased but $1000 on a pair of SSDs and my system is TWENTY times as fast. For data integrity my log files are stored on a traditional mirrored pair of HDs and everything is backed up every 4 hours to a SAN. Even in the unlikely event that both SSDs die at the same time, because the Tlog is on a traditional HD, I think MS-SQL's built in resilience will protect me.

I will say that when a traditional HD fails usually blocks start going bad and you start to see data corruption gradually over a period of time but when an SSD fails, its like you unplugged a USB stick.

I run a micro business and after weighing the pros and cons I think you would be a fool not to at least test the SSDs. Every time you hear your HD click, and they click constantly, its because they stopped transferring data for 10ms to reposition the head in a different location. SSDs have no moving parts and so the read/write queues almost never go above 0!!!!

The only place i would be concerned about having SSDs is if they told me after boarding an aircraft for a transatlantic flight that it was now running on SSDs. But even then fly-by-wire aircrafts have quadruple redundancy on their computer and power systems so you would still be more likely to die from pilot error.

Leon
Post #1425904
Posted Saturday, March 2, 2013 11:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 3,109, Visits: 11,516
In the tests in the article they tested only two HDDs compared to 15 SSDs and they subjected the HDDs to far fewer power loss tests and one of the HDDs failed, so problems with power faults are not limited to SSDs. It is possible that SSDs do not handle power problems as well as HDDs, but I saw nothing in that article to convince me of that.

I know from experience that HDDs are not immune to failure due to power problems. I was involved in a problem a few years ago where a data center was subjected to a power surge from the power company and experienced failures of 50 HDDs in high end SAN units.



Post #1425918
Posted Sunday, March 3, 2013 1:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
I guess my take on SSDs would be that they're likely perfect for TempDB. As previously mentioned, they may also be perfect for a database other than TempDB if and only if you have the log files on permanent storage and you're doing some proper point-in-time backups.

I do, however, want to remind everyone that everything has a limit. While you might get a 20x improvement in the performance of current crap code, there is only so much CPU to go'round. The use of SSDs should never become a "good enough" reason to promote crap code to production. To wit, Development, Test, and Staging boxes should never have the databases on SSDs IMHO.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426000
Posted Sunday, March 3, 2013 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 5:23 AM
Points: 5, Visits: 35
you can attack my code building skills if you want but the fact remains that if you have a high physical disk queue on a drive thats only used for the dB file then you will benefit tremendously from upgrading to SSDs. in my scenario my CPU remained at 2-5% while my physical disk Queue droped almost to 0.

today they may not have the reliability you have come to expect from traditional HDs but they will replace traditional HDs completely probably within 10 years.

Leon
Post #1426020
Posted Sunday, March 3, 2013 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
leonmccalla (3/3/2013)
you can attack my code building skills if you want but the fact remains that if you have a high physical disk queue on a drive thats only used for the dB file then you will benefit tremendously from upgrading to SSDs. in my scenario my CPU remained at 2-5% while my physical disk Queue droped almost to 0.

today they may not have the reliability you have come to expect from traditional HDs but they will replace traditional HDs completely probably within 10 years.

Leon


I wasn't attacking anyone's code building skills in particular, let alone yours. I was simply stating that people tend to get even lazier when such miracles as SSD come into play and that people have to remember there are other problems other than hefty disk queues. Hefty disk queues are normally accompanied by hefty CPU loads and you need to consider the CPUs, as well.

BWAAA-HAAAA!!!! I just thought of something. Wouldn't it be ironic if the SSDs had such a high failure rate because of the fat disk queues caused by crap code that people were trying to solve?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426035
Posted Monday, March 4, 2013 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:54 AM
Points: 171, Visits: 229
Part of the trick is doing your research before you buy any SSD's. They are not all the same! There are some that have very bad reputations with reliability - and at the same time, some a great. I've read articles regarding datacentres running on nothing but SSD's who couldn't give you failure rates as they have yet to have any fail. So buy decent ones :)

And check for firmware updates before sticking them in.

For those who haven't used SSD's, the speed difference can be astounding.

Post #1426145
Posted Monday, March 4, 2013 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 2,916, Visits: 1,854
I'd say architect your solution for failure. If you design for cloud then you know you have to design for failure because it is a fact of life.

SSDs are good for random reads/writes and here they offer massive performance advantages. For sequential reads/writes the advantage is still there but it isn't at such an order of magnitude. I'm not sure the answer is SSD now what's the question?

If SSDs were a car they would be a Ferrari. Fast, sleek and sexy but ultimately not much use for carting about masses of stuff and you have to expect to burn out tyres, clutch and brakes more often than you would with a Ford.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1426164
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse