SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Loss


Data Loss

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: Administrators
Points: 108038 Visits: 19343
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
My Blog: www.voiceofthedba.com
leonmccalla
leonmccalla
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10061 Visits: 11829
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153491 Visits: 41766
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
leonmccalla
leonmccalla
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153491 Visits: 41766
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Martin Bastable
Martin Bastable
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 248
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 Smile

And check for firmware updates before sticking them in.

For those who haven't used SSD's, the speed difference can be astounding.
Dave Poole
Dave Poole
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13039 Visits: 3385
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
www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search