Voice of the DBA

Tag: disaster recovery

  • #2 Skill – Performing a Restore

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    You Will Restore a Database

    At some point you’ll need to restore a database. It might be a database on your local instance of SQL Server to correct a problem with a query or a patch, but you’ll need to restore data.

    This goes along with the first skill of backing up a database as the counterpart. A backup saves the data (and objects) and a restore brings that data back.

    Restores are fairly easy, but there are a couple of things you need to learn right away:

    Always use NORECOVERY

    By default the RESTORE command brings a database online by going through the recovery (redo and undo) processes. For a full database restore, this means you cannot restore additional logs. You might not to this time, but at some point you will.

    So always use WITH NORECOVERY.

    You need this in database mirroring, log shipping, and more scenarios. Always include this in your restore commands. To bring the database online when you are sure you are done restoring (even if this is only one restore), use the RESTORE command and WITH RECOVERY, as in:

    RESTORE DATABASE db1 WITH RECOVERY

    Learn to move files

    I find that many restores take place for practice, or on servers other than the original ones. In that case, the paths might not exist. Often the production servers, or the main servers you use, will have more drives than the test servers. In that case, having a file stored on the z: drive for a server doesn’t match up with a development server containing only a C: drive.

    The WITH MOVE option is used to move the existing logical files in your restore to a new location. Here is the sample command from Books Online.

    Script Restores

    It’s easy to make mistakes with the GUI in SSMS. Learn to script restores and run the scripts. Even if you use SSMS to setup the restore, don’t click OK. Instead click this:

    scriptrestore

  • The Impact of Outages

    This is not what any of your clients want to see. So prepare for issues

    Are you going to start seeing more pressure for outages in applications? I suspect many outages are caused more by application issues than database ones, but those two are becoming very tightly linked as we look to more rapidly deploy features and enhancements in our applications, which often include database changes

    This outage from United shows that there can be a huge impact, not only financially, but also an inconvenience to clients and potential lost future business. A company might struggle to with future business after a large outage, especially when there are so many other choices easily available to consumers across the Internet.

    That brings to mind a very interesting problem as companies grow and look to build scalable systems. Large groups of servers require some level of standardization, mostly for the ease of management by IT workers as well as the ability to train future workers to understand the systems. However that standardization becomes a point of failure when there is a problem during an upgrade, or even a hack from some type of malware.

    I saw an interesting piece on how Netflix has tried to expect, and handle failures in the cloud, and a comment from Jeff Atwood that you ought have your own chaos monkey to regularly test your systems. Interesting advice, and in many cases, it’s probably good advice to ensure that both your systems and your people know how to deal with outages.

    You probably cannot eliminate outages, as Netflix and many other companies have learned. However you can work to ensure your people and systems know how to respond. I also wonder if having (at least) two versions of your systems out there at all times that work in a similar way might be a way to provide some tolerance against a single point of failure. I don’t know how you might implement this, but it might provide some protection against a failure in a completely homogeneous environment.

    Steve Jones

    The Voice of the DBA Podcasts

  • Multiple Failures

    Imagine this right after a failed RAID array.

    When we have a large scale disaster from a man made system, usually we have had multiple things go wrong. Chernobyl had multiple failures, so did the recent issues in Japan at their nuclear facility. It seems to me that in many cases when Mr. Murphy has struck in my career, he has done so multiple times.

    Recently I was studying some high availability (HA) scenarios. In a mirroring situation the implication of a long mirroring queue was shown dramatically as a the primary server failed and the company experienced data loss in a failover to the secondary server. The data was eventually recovered by bringing up the primary server, extracting the missing data, and then manually merging it back into the system.

    Incidents like that are never fun to deal with, but are they common? It seems that the old adage about “when it rains it pours” has happened more than a few times in my career, but for this Friday I wanted to see if that has been the case for the rest of you.

    How many of you have had multiple failures at the same time?

    A RAID drive fails and shortly thereafter a network card dies. You find corruption and while working on that, the server hardware crashes. Have any of you had two or more failures occur on the same system at almost the same time?

    Let us know this Friday, along with any lessons learned. Have you changed the way that you prepare for disasters or plan for issues in your career? I’m sure there are many people that would love to learn those lessons from a post rather than experiencing them first hand.

    Steve Jones

    The Voice of the DBA Podcasts

  • BCPs – The Disaster Recovery Plan

    I came up with a short acronym for my Preparation for Disaster presentation: BCPs. It describes what I think you need to do in order to be prepared for issues with your database server. The initials stand for:

    • Backups
    • Checks
    • Practice and Preparation
    • Script and Schedule

    With a utility in SQL Server going by BCP, this ought to be easy to remember. I’ll describe what I mean below by each of these:

    Backups

    You need to make sure that you have backups. They are insurance for the issues that will befall you at some point. They are also required for restores, which do matter.

    You need a backup plan that meets the need for the particular system you are protecting. Not your entire infrastructure, but what’s appropriate for each system. Once size does not fit all here.

    Checks

    You can’t prevent corruption and it can occur at any time. The best you can do is detect it early, and fix it as soon as you can. Make sure you have checks in place that look for corruption.

    You also want checks for things that are out of the ordinary. Make sure you have some monitoring/base-lining set up so that you can proactively be aware of what a disaster might occur.

    Practice and Preparation

    All of the backups, checks, etc. in the world are useless if you don’t know how to apply them. Or if you don’t have the keys, passwords, account numbers, etc. Practice the skills you need to recover your systems, and make sure that you are prepared to do so in a variety of situations.

    Explaining to your CEO that you don’t have the keys for that TDE encrypted restore will make for a very, very bad day.

    Script and Schedule

    You must automate most of your job to be successful and efficient, and you ought to have lots of your DR insurance (backups/scripts) scheduled so that it get performed in a timely manner, and a regular manner. Humans forget things, and make mistakes constantly. You need to ensure that you are scripting your tasks (to speed up processes) and scheduling them (to ensure they occur regularly).