| Author |
Topic |
|
Jim Beam
Posting Yak Master
137 Posts |
 Posted - 2011-01-05 : 09:31:43
|
| Hi all,Is there a checklist for these? I'm aware of the wisdom of disabling autogrowth and sizing according to expected loading, as well as for placing it on it's own storage device (and preferably controller), but that's about all I can think of, and I'm sure there are more.Cheers,Jim |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-05 : 09:36:23
|
| Take regular log backups, make sure to restore them to test their validity (on a separate server preferably). Make sure your backup archiving strategy is compatible with any audit or certification requirements your company subscribes to (SAS70, SOX, PCI, etc.) and are securely copied to at least one on-site and an off-site storage location. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 09:45:14
|
| I start from point of view of how to recover a disaster.Someone deleted one/many records by accidentSame, but program bug caused it and it has been going on for some/many days/weeks/months ... years?!!Disks died, or database is corrupted (this scenario is often recoverable with zero data loss using "tail" log backups)Server died (this is related to how quickly a stand-by needs to be operational. In some circumstances this will need to be "instant and with no data loss" - that cots $$$. In many cases minutes or hours is fine. In very few is there enough time for "First buy, configure, and soak-test a new server ..." even when money is not made available for a standby machine)Building burnt to the ground. That needs a geographically separate server (and backups that were not in the fire - or backups that were copied to the remote server ready to be restored independently of the tape backups)Building burnt to the ground and all the people are no longer around. Had to do one of those once. Pretty much everybody on the exercise put in reports of "who cares" - not really what the company were looking for! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 09:48:43
|
| Might be worth noting that:Validating a backup by restoring on A.N.Other machine:Restore backup file (and log backups etc.)then DBCC CHECKDB to check that the backup is not corruptedmeans that you do NOT also have to use DBCC CHECKDB on the Live database (doing the test on a restored copy is equivalent).Finding maintenance window to do DBCC CHECKDB on a live server can be an issue, so this can be a useful fringe benefit. |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-01-05 : 09:58:40
|
quote:
Originally posted by Kristen
I start from point of view of how to recover a disaster.Disks died, or database is corrupted (this scenario is often recoverable with zero data loss using "tail" log backups)
Intriguing, what are these?? quote:
Building burnt to the ground and all the people are no longer around. Had to do one of those once. Pretty much everybody on the exercise put in reports of "who cares" - not really what the company were looking for!
:) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 11:53:13
|
"Tail log backup" ?If the database fails, but the hardware is basically intact, it is usually possible to make a "Tail" log backup.The disks the Logs files are on must be operational (but the disks the data files are on can be dead - however, step 1: put the Data and Log files on separate drives otherwise all-bets-are-off!!)You can then start SQL with special startup attributes and it will allow you to backup the Log file, despite the fact that the database cannot be mounted.So you now have your Full backup, all the "normal" log backups you made, and one final "tail" log backup (its no different to the other log backups, it just has this fancy name  )You then fix the hardware (but you have no database as the data files went with the disk), and you restore from backup (the full backup, all Tlog backups (including the "tail" backup)) and you are back to exactly where you were - including all committed transactions up to the point of catastrophic failure.Mind you ... I've never had to do it, so armed with this info you might want to Google a bit 'coz there may be some gaps in my story! |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-01-05 : 12:26:36
|
| What a great function!! Hopefully I'll never have to use it of course, but I appreciate the clear and interesting description.Just a thought, given that the hardware is indeed in working order, why would it matter whether the .ldf and .mdf files are on the same drive (apart from obvious perf issues) ? Or should I just go Google it?!?!? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-05 : 12:37:26
|
quote:
Originally posted by Jim Beam
I'm aware of the wisdom of disabling autogrowth
Whoaa! Who told you that? Do not disable autogrowth. Do size the files so that hopefully they never need to autogrow. Actively monitor the file sizes (and disk space). Make sure you get alerts when files reach a certain threshold, or when disk usage reaches a certain threshold.An autogrow event is far more palatable than failed transactions. And what is the likely fix when the file is full? That's right, manually grow it.Autogrowth is not a substitute for properly sizing your files, but it is a failsafe for when unexpected growth occurs. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 12:47:04
|
| "Just a thought, given that the hardware is indeed in working order, why would it matter whether the .ldf and .mdf files are on the same drive (apart from obvious perf issues) ?"Two things.Performance, as you say. Our Data Drive RAID is optimised for Random Access, the LOg one is optimised for sequential access.But more importantly it allows the DATA drive to break, and provided the Log drive does not also break we can do the TAIL Backup thingie.If BOTH are on the same drive you have a single-point-of-failure and no chance of a Tail Log Backup. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-05 : 12:55:59
|
quote:
Originally posted by Kristen
You can then start SQL with special startup attributes and it will allow you to backup the Log file, despite the fact that the database cannot be mounted.
Errr, no.Special startup switches are only needed if one of the system databases is damaged. A tail log backup can (and should) be taken without restarting the instance (as it may not restart) and just requires one additional option on the backup log statement.See the msdn articles that the search I posted returns--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 13:00:16
|
"See the msdn articles that the search I posted returns"Sorry, missed that (was posting whilst you were - I wish this forum would alert me when that happens  )Glad to hear no fancy-business needed if the system databases are still intact. I'm sure that's come up before, I'll try to remember it this time! and that backup just "requires one additional option on the backup log statement" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-05 : 13:04:14
|
quote:
Originally posted by Kristen
Building burnt to the ground and all the people are no longer around. Had to do one of those once. Pretty much everybody on the exercise put in reports of "who cares" - not really what the company were looking for!
At a previous company some bright-spark external consultant produced a disaster recovery plan that included the possibility that the entire building that houses the server room being destroyed. It didn't look much, if any, different than the case of a simple server room failure. He presents it to management proudly. Management asks DBA team for feedback. DBA team leader notes that the plan makes no provision for the DBA team not being available and, since they all sat 3 floors above the server room, should the building be destroyed there likely won't be any DBAs around to implement the plan.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 13:09:18
|
Air bags on intervening floors?  |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-01-05 : 15:40:09
|
| Yep, when it comes to disaster recovery, remember the 6 D's...Dead Dba's Don't Do Diddly Dr.(awful, I know, but I'm like that!)Just another thought, is there a way within SQL to raise an alert when log usage hits 80% or something? I presume there's a DMV equivalent for DBCC SQLPerf, but that's monitoring, not alerting. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
T/log smartest Practices