Voice of the DBA

Tag: disaster recovery

  • Helpful Advice?

    hard drive
    Flicking power on and off is not recommended for these devices.

    This editorial was originally published on Feb 5, 2007 and is being re-run as Steve is on vacation.

    I have to be honest with you. It wasn’t me. This advice on DRwasn’t given to anyone by me. This post is from a blog entry last year before Tech Ed. I made a note of it, but it wasn’t until recently I got around to actually writing about it.

    I’m kind of surprised to see some of this advice being given to people. Some of these I don’t think are too bad, but would you do any of these on your production database?

    1. “Just run REPAIR_ALLOW_DATA_LOSS and you’ll be fine…”
    2. Just rebuild your transaction log using these steps…”
    3. “Just restore your database and carry on…”
    4. “Run CHECKALLOC, then CHECKDB, then CHECKTABLE on all your tables, then…”
    5. “Just flick the power switch on and off a few times on one of the drives…”

    Actually I’ve done #4 and #3 is something I’ve had to do before as well. I can appreciate the caution in the article about not finding the root cause, but I’ve had more problems than I’d like to think about where we couldn’t find a root cause in a reasonable time and decided to move on. And we never had the issue again. In the interests of getting the business going, there was a time where I explained everything, guesstimated the data loss, and we decided to just restore, lose the data, and have people re-enter it as quickly as possible.

    As for the other items, I don’t think I’d run REPAIR_ALLOW_DATA_LOSS without someone from CSS on the phone. And I don’t think I’ve ever even heard about anyone “rebuilding” a transaction log. That sounds like one of those urban myths where someone heard that someone said that they had a way to rebuild a log.<

    Flicking the power on your drives? I’m not sure what I’d even say to someone who suggested it.

  • Slide Decks for PASS, SQL in the City, and SQL Server Connections

    I’ve sent my slide decks to the organizers, but I know they don’t always get posted. So I’m putting a few links up here for my talks.

    If you check the page for my Preparation for Disaster talk, you’ll find I have links for the two recent events to the decks. I often version the decks after talks, and so I’ve decided to keep all versions live.

    I’ve also uploaded the decks from PASS if you’re interested.

    PASS Summit 2011

    SQL in the City – LA 2011

    SQL Server Connections

  • Buckets for Disaster Recovery

    Disaster recovery can be a huge project at any company. Considering the ways in which you build a plan that covers all the infrastructure, and it can quickly become a full time job for someone. The details, and the scope of the project can be overwhelming when you try to address your environment.

    There’s a way to make this easier: buckets. If you can group your systems into a few gross buckets that define those systems that have similar needs, you can make life simpler.

    In the past, I have typically built four buckets of systems for DR purposes, though I think you could go with three. These are the buckets I’ve had:

    • Critical Systems – high priority systems that typically must be running the majority of the business day. Note that you may have a 24 hour business day in which case downtime must be minimized to minutes.
    • Low priority – systems that we can function without for a day or two if we need to. Often development systems fall into this area.
    • Everything else – Medium levels of priority.
    • Not recovered – This is an optional level, but it’s almost always been a set of test systems, maybe some development systems that aren’t important enough to worry about.

    The fourth bucket, the “Not Recovered” bucket doesn’t mean you abandon those systems. In the event of an issue, you would make an attempt to recover them, but you might not spend time or resources practicing or preparing for the recovery effort.

    Bucketing your systems is the best way to easily manage your preparations for disaster, and also set some gross priorities. You might end up recovering systems within the bucket in different orders, depending on what happened, but this gives you an easy way to allocate resources, both in a disaster, and in preparation.

  • Practicing for DR

    One of the sayings that I’ve heard a few times from professional atheletics is this:

    “Amateurs practice until they get it right. Professionals practice until they don’t get it wrong.”

    Most of us working in technology would like to think that we’re professionals, but do we really practice restores, practice recovering systems, practice reconfiguring things until we don’t get it wrong?

    I don’t think most of us do. We give it a try, and if it doesn’t work, we may examine why, but we often don’t go through the exercise enough to ensure that we really can do it well when there is pressure.

    You can’t practice constantly, that’s not practical, but you ought to take a regular look at different systems, different problems, and try to reset them up every quarter or so in order to be prepared. Even if it’s something you do informally, you ought to ensure that you understand how your scripts work to automate restores, reconfigure systems, restore databases and logs, etc. With the advances in virtualization, you can even do this on workstations, setting up complex, multi-server environments on ordinary hardware.

    It’s good practice for your skills and your career, and it might make you shine when there is an issue.