November 21, 2011 at 11:51 am
Chris Metzger (11/21/2011)
For example, my trans logs grow regularly. I have SQL backup just the trans logs every 2 hours. Then as part of the maintenance plan (another subplan inside - it allows you to have more than one task in a plan on a different schedule) I check for files ending in TRN in a particular folder (mine are separated at the root into folders per instance which is per client) and delete anything older than 24 hours - and it runs right behind the trans log backup so I never have more than 24 hours of trans log backups sitting on my drive in any one folder. Then once a day I have another subplan that runs full backups and a matching subplan that keeps the full backups in check so I only retain 2 days' worth of those (so I have 2 days' worth of what's called "hot" backups because they're on the disk and not on tape and I could restore them at any moment). In addition, I have another subplan that runs weekly to reorganize my indexes - something that should be done on a regular basis too.
I think you might want to rethink some of this...
Having a rolling 24 hours of transaction logs means you have created a broken log chain scenario. Simply put, if you have to recover to the previous full backup - you no longer have a full log chain of transaction log backups to restore from.
Depending on when you actually backup those folders to tape - you may not be able to get those files from tape either.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2011 at 4:28 am
Jeffrey Williams 3188 (11/21/2011)
I think you might want to rethink some of this...
Having a rolling 24 hours of transaction logs means you have created a broken log chain scenario. Simply put, if you have to recover to the previous full backup - you no longer have a full log chain of transaction log backups to restore from.
Depending on when you actually backup those folders to tape - you may not be able to get those files from tape either.
Why would that cause a broken chain? If I have full backups for the previous two nights (they run between midnight and 3A local) and I run trans log backups every 2 hours then at minimum I could restore the last full and roll forward to the point the client needs or maximum roll back to the previous night. I see what you're saying about there being a gap between when the trans logs cut out and when the oldest full backup is being retained but it's not meant to be a DR solution - it is just for regular maintenance and the "just in case" scenario of "we deleted this and need to get it back". There's no reason they should be able to go back to any 2 hr PIT for 48 hours and I've never had that type of request. Being that it is a medical records system it is rare to go backwards for anything - it's actually best to re-enter the data OR correct the issue than try to roll back.
Plus I currently don't have the disk space to retain any more than I do now (because I don't back to tape - it's all hot to disk then warm to secondary disk backups). On the new platform I'm building I'll have enough to keep 48 hours of trans logs and 3 days' full backups if I want but I wouldn't keep any more than that. And our new DR site will be log shipping (for now) and eventually mirror (when 2012 releases) - depends on when I get the DR built and how that times with the release of 2012.
November 22, 2011 at 5:46 am
What would you do if the database was corrupted right before your backup? And you didn't catch it before the next transaction log backup was run?
What would happen id a user cam to you at 8am and stated that a delete was run last night that deleted all the data in one of the tables accidently?
With that gap in the transaction log backups you have no way of recovering.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2011 at 7:30 am
Jeffrey Williams 3188 (11/22/2011)
With that gap in the transaction log backups you have no way of recovering.
What gap do you see?
He's doing a full backup every 24h and a tlog backup every 2h and retaining 24h of tlog backups. No gaps. In your scenario, he'd restore last nights full backup and all of the tlogs necessary.
You don't think he needs tlog backups retained from before the most recent full backup do you?
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 22, 2011 at 7:38 am
Jim Murphy (11/22/2011)
Jeffrey Williams 3188 (11/22/2011)
With that gap in the transaction log backups you have no way of recovering.What gap do you see?
He's doing a full backup every 24h and a tlog backup every 2h and retaining 24h of tlog backups. No gaps. In your scenario, he'd restore last nights full backup and all of the tlogs necessary.
You don't think he needs tlog backups retained from before the most recent full backup do you?
I will always have Plan B & C with me if Plan A fails.
It may not match with your justifications but I would keep minimum 3 FULL backups or equivalent (log / diff) to keep me satisfied for recovery.
November 22, 2011 at 8:44 am
Jim Murphy (11/22/2011)
He's doing a full backup every 24h and a tlog backup every 2h and retaining 24h of tlog backups. No gaps. In your scenario, he'd restore last nights full backup and all of the tlogs necessary.
No, because in Jeffrey's scenario the database was corrupted just before the last full backup. So to restore he'd need the previous full and all log backups, and those log backups don't exist.
You don't think he needs tlog backups retained from before the most recent full backup do you?
I prefer log backups spanning 2 full backups. That way if one is not restorable I'm not up creek, no paddle.
The described backup strategy works, but there's a risk. It assumes that the last full backup is always fine, always restorable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 8:54 am
Ah, I see.
If disk space permits, it is always a great idea to retain more. Moving those backups off to tape/NAS throughout the day is also a good thing (including protecting against controller failures, etc.). Moving to separate storage, recent backups are never lost, but the recovery is delayed by hour or three if older data needs to be restored.
Good point Jeff. Thanks for the clarification Gail.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 22, 2011 at 12:37 pm
Just got back to this - and Gail answered for me, thanks.
I use a custom utility that deletes files based on the number of full backups I want to keep. So, if I want the current and previous full backups, the utility will delete all files (trn, dif and full) that are older than that full backup.
This keeps all related transaction log backups and differential backups available online.
We also copy those off to tape daily and keep those tapes available. In fact, those tapes are then sent offsite for further protection.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2011 at 2:05 pm
Jeffrey Williams 3188 (11/22/2011)
What would you do if the database was corrupted right before your backup? And you didn't catch it before the next transaction log backup was run?What would happen id a user cam to you at 8am and stated that a delete was run last night that deleted all the data in one of the tables accidently?
With that gap in the transaction log backups you have no way of recovering.
In a PHI (med record) system users A) don't have direct access to the databases or even the App Servers for that matter; B) our Support personnel don't have direct SQL access but can run queries however we do not EVER just delete wholesale from a table; C) our application does not allow delete. So loss from delete is not going to happen unless someone deletes a specific record and I have to roll back to get it relatively quickly. And the mirror should cover for corruption however I've never had a database corrupt on me to the point that I couldn't fix it OR our Developers couldn't fix it. And if the corruption somehow made it to the mirror I could always pull from the DR SQL Server where the logs were shipped.
I'm not overly worried about that scenario you propose because in all my time as a sysadmin I've never had it happen. Overly paranoid perhaps?
November 22, 2011 at 2:10 pm
Dev (11/22/2011)
I will always have Plan B & C with me if Plan A fails.
It may not match with your justifications but I would keep minimum 3 FULL backups or equivalent (log / diff) to keep me satisfied for recovery.
Well that's the plan when we move to this new platform/datacenter (once I get it built). I'll be keeping 3 full backups plus 48 hours rolling trans logs - so I should be able to roll back to any point in time in the last 48 hours at minimum and could go back to the last full backup within 72 hours. I think that will be MORE than sufficient for hot availability especially when we also have backups of that file system to other disk (warm) then to tape for offsite (cold) so we're covering at least a week's worth of files for retrieval. PLUS, we'll have a new DR site on the West Coast (geographical sepa from the primary on the East Coast - USA) soon so we could always go to it if needed (that will be a log shipping relationship for now with 2008 but I plan on setting up another mirror under 2012 when it comes out and our Developers confirm I can use it with our app).
November 22, 2011 at 2:22 pm
Jim Murphy (11/22/2011)
Ah, I see.If disk space permits, it is always a great idea to retain more. Moving those backups off to tape/NAS throughout the day is also a good thing (including protecting against controller failures, etc.). Moving to separate storage, recent backups are never lost, but the recovery is delayed by hour or three if older data needs to be restored.
Good point Jeff. Thanks for the clarification Gail.
Jim (and anyone else): there will NEVER be a controller failure. The environment is a true hardware cloud. The platform is: VMware vCloud with a Clariion SAN (all SAS). What happens is if a host fails (they are all blade chassis clustered together) then vCloud sees that and moves the guests from the host to another host automatically - the most you'll see is a burp of about 5 minutes while the move takes place and the VMs come back online (as if they were just shutoff or something). The SAN is one of those giant HA SANs so if a disk array fails another one will take over without issue and with almost no interruption (it has redundancy built into it with multiple disk sets/LUNs/controllers/etc).
So the HW failing is no longer going to be an issue on the new platform currently under construction. The existing platform does have all of those issues because there are multiple single points of failure - hence why I'm going to get away from it.
@Jeffrey: Yeah my maintenance plans do that for me instead of using something external/3rd party (for now - I'm looking at new 3rd party tools to switch to instead of using the native ones). And yes I'll be keeping more when I move this thing but for now I have what I have and don't have any other choice. It helps that we have a week of warm backups I can go to if necessary (done at the VM level) but it's, again, rare to need to do so.
November 22, 2011 at 2:58 pm
Chris Metzger (11/22/2011)
Jim (and anyone else): there will NEVER be a controller failure.
I'd be very hesitant to say 'never'. Very, very unlikely, sure, but I've seen a controller 'failure' in a top-end SAN (redundant controllers, RAID, hot spare disks, etc, etc) that toasted a database completely. Basically the controller glitched and wrote garbage across all the LUNs it controlled. Not pretty
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 3:09 pm
GilaMonster (11/22/2011)
Chris Metzger (11/22/2011)
Jim (and anyone else): there will NEVER be a controller failure.I'd be very hesitant to say 'never'. Very, very unlikely, sure, but I've seen a controller 'failure' in a top-end SAN (redundant controllers, RAID, hot spare disks, etc, etc) that toasted a database completely. Basically the controller glitched and wrote garbage across all the LUNs it controlled. Not pretty
If that happens with this piece of hardware I'm immediately headed to the closest casino or out to buy a lotto ticket. And the only reason I say that is because in 20+ years I've never seen a controller do that not to mention this particular SAN (an EMC Clariion) has minimum 4 4-channel controllers per set of disks (some configs allow for 8 controllers). I mean it's one thing to plan for the unexpected but another to try and compensate for every possible issue that may come up. I believe the choices so far will provide reasonable protection for the platform when we move to it. Am I just being blase about it or are some ppl just overly paranoid?
November 22, 2011 at 3:22 pm
Chris Metzger (11/22/2011)
...in 20+ years I've never seen a controller do that not to mention this particular SAN (an EMC Clariion) .... I believe the choices so far will provide reasonable protection for the platform when we move to it. Am I just being blase about it or are some ppl just overly paranoid?
I like EMCs stuff. Mostly. And Clustering is great. So is vMotion and similar technologies. That said, there is still only a single copy of the data: on RAID disks on the SAN. That, to me, feels like a single point of failure, even if it is supposedly not likely to happen.
So I typically feel better adding Mirroring, Log Shipping or similar to dupe the data to another location. Your existing setup is still vulnerable to a natural disaster, and a few other risks when having a single instance of the data. However, these options are not at all related to shrinking data files. There are many other posts which discuss HADR in more detail.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 22, 2011 at 4:26 pm
Chris Metzger (11/22/2011)
Am I just being blase about it or are some ppl just overly paranoid?
Both.
Am I correct in saying you've never dealt with a disaster scenario?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply