June 26, 2012 at 3:56 pm
I don't consider any backup unnecessary, regardless of data changes or not. As a DBA at a previous employer, both the model and master databases were backed up daily. I didn't make changes to the system every day, and I definately didn't update the model database. We had nightly backups to tape and I made sure I had current backups of the databases on those tables should I need them.
The same would be true of user databases that may only be updated periodically. I'd make sure I took a backup of those every day. Of course, I'm a bit paranoid that a disk file could become corrupt or be damaged due to a hardware failure, just a s easily as a tape can suddenly become damaged and unreadable.
June 26, 2012 at 4:07 pm
As I understand it having database files stored across two arrays (data on one array, logs on the other) gives us a better chance of recovering to the point immediately before an array failure than having the files on only one array. If we lose the array containing the data files but the log file array is OK we have a chance.
And vice versa, btw, assuming that a tail-log backup could be taken if the data file is damaged, which is usually the case.
In either scenario however (one array or two), if we lose an array I think we still need to be in full recovery mode and have valid backups to have a chance of recovering data, which is not the scenario here.
I wouldn't dismiss the idea of not having valid backups in this case so quickly. Even a full db backup that is several days old is still a valid backup, of course. You apply the [log backups and] tail-log backup to it.
If the log drive is lost/damaged, you recover using the existing .mdf and/or by restoring the backup and applying periodic log backups, if any.
With a single RAID10, you would have to have ALL your recovery pieces on a separate disk/disk set and would only be able to recover to the last point where you had copied the relevant backups to that other disk.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2012 at 4:12 pm
Scott i don't disagree with you, we know that multiple arrays are ideal but all we're saying is in this case you have to go for something a little less flexible but still highly fault tolerant (1 x RAID10 offers excellent read and write over RAID1 or RAID5 arrays).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 26, 2012 at 4:26 pm
Perry Whittle (6/26/2012)
Scott i don't disagree with you, we know that multiple arrays are ideal but all we're saying is in this case you have to go for something a little less flexible but still highly fault tolerant (1 x RAID10 offers excellent read and write over RAID1 or RAID5 arrays).
I don't know that a 4-drive RAID10 will offer that much read/write advantage over two 2-drive RAID1s.
All I said was, given that I factor in recoverability as well as performance, I would opt for two RAID1s in this case.
I don't think the OP is in a situation where there is a lot of great RAID support. What would scare me in this case is that in the event the RAID10 did somehow fail -- say, no one say right away that a drive had failed and then the other paired drive fails -- everything would be lost.
Rather than risk that, I'd take a little performance hit if necessary, esp. given that slighly less performance would not seem to be an issue in this case at all.
In fact, in pure performance terms, I'd expect RAID5 to perform better than a RAID10 here, particularly given the high rate of READs to WRITEs. But I'd still not use RAID5 for the same general reason -- it requires all 4 drives and so forces everything to one disk set.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2012 at 4:48 pm
I think this series of articles is pretty good, and yielded some interesting results about RAID:
http://www.kendalvandyke.com/2009/02/disk-performance-hands-on-part-6-raid.html
That's not to say this article is the last word on RAID or anything. I just thought it was interesting and thought-provoking.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2012 at 4:50 pm
ScottPletcher (6/26/2012)
As I understand it having database files stored across two arrays (data on one array, logs on the other) gives us a better chance of recovering to the point immediately before an array failure than having the files on only one array. If we lose the array containing the data files but the log file array is OK we have a chance.
I see your point given the disposition towards your idea to use two arrays. I assume everyone gets the recoverability part. The contention (I think) is where some folks latched onto something you said that implied you knew how to recover from a lost array in a 2xRAID1 setup with no backups based solely on how you laid out the database files on those arrays.
And vice versa, btw, assuming that a tail-log backup could be taken if the data file is damaged, which is usually the case.
In either scenario however (one array or two), if we lose an array I think we still need to be in full recovery mode and have valid backups to have a chance of recovering data, which is not the scenario here.
I wouldn't dismiss the idea of not having valid backups in this case so quickly. Even a full db backup that is several days old is still a valid backup, of course. You apply the [log backups and] tail-log backup to it.
If the log drive is lost/damaged, you recover using the existing .mdf and/or by restoring the backup and applying periodic log backups, if any. [Having placed the log backups on the same drive as the data files.]
I do not think anyone was dismissing the idea of having valid backups. Valid is valid, regardless of how many days since the last FULL. As long as you have valid tran log backups (or combo of diff and log) leading back to the last FULL you can recover. The problem is that I do not think tran log backups are baing taken regularly and offloaded from the server in question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 4:58 pm
The contention (I think) is where some folks latched onto something you said that implied you knew how to recover from a lost array in a 2xRAID1 setup with no backups based solely on how you laid out the database files on those arrays.
I didn't realized I'd implied that, nor did I intend to. If the data files are lost/damaged, only a db backup will allow them to be restored.
The problem is that I do not think tran log backups are being taken regularly and offloaded from the server in question.
Very much agreed. That's the other reason I strongly prefer two different disk sets here -- the backups don't have to be offloaded from the server to allow a recovery, they are present on the other drive set.
Typically after the first "disaster" -- i.e. lost drive set in this case -- backups do start getting offloaded from the server ... at least for a while anyway :-), before people get complacent again.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 27, 2012 at 10:34 am
You could have tempdb data files on both RAID1s for performance reasons, simulating something of a faux RAID 10, recoverability obviously not being a factor for tempdb.
Depending on what features of SQL you're using, you might consider the same for msdb, if you really needed the slight performance gain you might get from that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply