Voice of the DBA

Tag: disaster recovery

  • What’s a Disaster?

    In working on my Preparation for Disaster presentation and doing research, one of the questions that came up early for me was “what is a disaster?” After all, if you can’t answer that question, how do you prepare for it.

    I’ve got a list of stuff here, which ones of these qualify as disasters?

    • Hard Drive crash
    • Hurricane wrecks data center
    • Fire in the server room
    • Corruption in a clustered index
    • DBA deletes a table by running DELETE without a WHERE clause
    • User clicks submit on a web page and the database crashed before they get the acknowledgement
    • Admin uses Access to update and lock a table, and forgets to save their work before leaving for lunch
    • DBA deploys code to production instead of development by accident.

    The answer is all of these are disasters. Most people seem to plan for the first 3, and forget to plan for the rest.

    Anything that interrupts your system’s ability to serve clients and get work done is a disaster. It’s worth keeping that in mind and planning for all sorts of potential problems. It doesn’t mean you have to be ultra paranoid, but you ought to have thought about the various potential problems and have some ideas about how to deal with them.

  • Preparation for Disaster Talk

    This talk looks at the reasons and ways that you might prepare for disaster. I cover a number of areas, some of which might seem obvious, but are often overlooked:

    • What is a disaster?
    • Why Prepare for Disaster?
    • RTO/RPO
    • Overview of SLAs
    • Backups as insurance for issues
    • Checks for corruption
    • A checklist of skills to practice
    • Scripting and scheduling

    This talk was built at the request of Red Gate Software, my employer, so I have also included a few demos in the talk:

    • Object Level Restore with Data Compare
    • Quick overview of SQL Backup Pro
    • Running DBCC using Virtual Restore

    I can also do this talk without the Red Gate demos at a 75 minute pace.

    Length: 60 Minutes

    General Slides are available on SkyDrive.

    Specific Decks for Events:

    SQL in the City – LA 2011

    SQL Server Connections

    Related Blogs

    I have a number of blogs that are related to this particular talk:

  • Learn to Use Filegroups

    In SQL Server, filegroups are a management technique that I don’t see many people using. It’s amazing how many people ask questions about filegroups on the discussion forums, often unsure of how they fit into a well architected SQL Server. I have tended to use filegroups mostly as a space management technique, when I need to add more disks to my server, but they can be used in many more places.

    We continue to grow our data sizes all the time. While many databases are still measured in the single digits of gigabytes (or smaller), it is fairly common to find many database servers with over a terabyte of disk space. Our disks grow larger and larger, but it seems that data grows faster than disks, requiring larger storage subsystems all the time.

    While our storage grows larger, the tolerance for delays shrinks and demands for better performance increase. That means that data professionals need to be more cognizant of not only how their code is written, but also how they design storage. Tiering storage is one idea that I think has merit, but one that requires some planning.

    In SQL Server, we can’t split a table across filegroups. Or can we? We can partition a table (Enterprise Edition and higher), which can allow us to manage performance and storage appropriately. There is also the recommended practice of only having system objects in the primary partition and using separate filegroups for user data. That allows you to bring a partial database online, again, in Enterprise Edition only, while you restore different filegroups.

    This isn’t the first thing I would recommend you learn about SQL Server, but as you advance your knowledge, you should better understand when and how filegroups can help you. You will use them at some point and being comfortable with a filegroup restore is one of the skills that separates the accidental DBA from the data professional.

    Steve Jones

    The Voice of the DBA Podcasts

  • The Chance of Failure

    I’ve always worked on the notion that my hardware rarely fails, and while I protect it with backups RAID, etc., I don’t expect failures. That seems to be how most of the RDBMSes are structured, with this idea that things will work well and we have protection mechanisms when they don’t.

    Contrast that with the way Google viewa the world. To them,failure is inevitable at scale, as noted by  Jeremiah Peschaka in The Promise and Failure of Federated Data. At large scales of deployment, Google and other companies assume that there will be a percentage of failures and they have to account for them. This is the same type of accounting that restaurants use (spoilage) and retailers (shrinkage) use for allow for some amount of loss.

    In technology, we account for potential failures with RAID, with HA or DR technologies, and hopefully with substantial testing to ensure that we have properly accounted for potential failures. However it seems that most technology people account for failure as a possibility and not a probability. Many people seem to assume that a serious disaster is not likely in their career.

    I think that a catastrophic, we lost the whole data center, event is unlikely for most of us. As Hurricane Katrina and the recent earthquake in Japan have shown, that is possible. However it’s unlikely for most locations, which is a good thing.

    Failures are inevitable and whether it’s a disk corruption, a server crash, or a building power failure, we have to assume we will experience one and plan for the event. We also have to expect hardware will fail, which means regular checks and monitoring to detect these failures as soon as possible.

    Hope for the best, but plan for the worst.

    Steve Jones

    The Voice of the DBA Podcasts