Voice of the DBA

Tag: disaster recovery

  • You Need Two SLAs for Disaster Recovery

    Continuing on with my MCM prep, I was listening to the High Availability/DR prep module today and I was once again surprised by something. Typically I have heard all kinds of talk for SLAs, usually in terms of network traffic. For databases, I have had SLA conversations that were for downtime that usually go like this.

    Me: How much uptime do we need?

    Manager: 100%

    Me: We can’t really do that in a cost effective manner.

    Manager: Why not, the telephone companies are always up?

    Me: Well, even the telcos measure their uptime in terms of 9s?

    Manager: (blank look)

    Me: They talk about 99% reliability, 99.9%, 99.2%, each of those being a “9” of availability. The high water mark seems to be companies aiming for five nines or 99.999%.

    Usually at this point I need to write this down so they can understand why five nines are 99.999 and not 99.99999

    Manager: Let’s go for five nines.

    Me: That’s only 5 minutes of downtime a year. We can’t apply patches in 5 minutes. A better number is usually 99.9 for us, which means across the entire year we get a 8 hours of downtime. That’s a good number to aim for across a year.

    Manager: We can’t be down for 8 hours!

    Me: (blank look)

    At this point I usually give up and go in search of someone that will better understand things.

    However an SLA for downtime/uptime isn’t enough for SQL Server. You also have to think in terms of data loss. If we lose a server, what about transactions in flight? What about things not transferred to the mirror server or log shipped server? What about losing disks and no tail of the log backup?

    An SLA for data loss is important as well. And like the conversation above, your business people will say zero data loss. Quiz them to find out what can be recovered and to what extent lost data costs the company. The compute the cost of your various HA solutions to decide how to handle things.

  • Data Loss or Downtime

    I was watching Kimberly Tripp of SQL Skills talk recently about VLDB disasters and how to recover from them. One of the first things she said in the session was getting a damaged database back online, even without all of the data, was important. Often her clients need to keep working, and it is important that they get the system back online, even without all the data. This allows business applications and business people to get back to work.

    That is interesting. I had always thought of my production OLTP databases as needing to be online, but also needing all the critical data. After Mrs. Tripp’s talk, I had to rethink that a bit and consider that a little data loss might be acceptable.

    To me this is a topic that is worth understanding. At the very least, it will help you make decisions in the event of some disaster for how you will proceed. So for this Friday’s poll:

    So what is more important to you: downtime or data loss?

    My feeling is that most of the people would really rather have the database online, even without all the data so they can continue to work. I realized that most of the time, getting the site back up, having lookup and other types of ancillary data (like products, prices, etc), was the most important thing. Recovering other data such as older orders, was secondary.

    Once the database is up, you can then work on getting other data back and merging it into the production system.

    Let us know what you think this Friday and what’s more important to your business (and why).

    Steve Jones

    Originally Published at http://www.sqlservercentral.com/articles/Editorial/72117/

  • The Institute for Backup Trauma

    This is an editorial reprinted from April 27, 2005.

    It’s viral marketing, but it’s not bad. Livevault produced a short video starring John Cleese as the director for this institute that seeks to counsel those that have had problems with tape backups. “Such an unreliable medium”, this one is straight out there. An advertisement for Livevault, a little silly, satirical, but not that bad.

    It’s an interesting concept and one that I’ve debated with various colleagues over the years. Tape v disk. Which one is better for securing and ensuring the integrity of your data, which one is better for business continuity, which one works for your budget.

    When I started in this business, it was a no brainer. Disk was way more expensive than tape and there wasn’t even a choice. Nowhere I worked could afford to backup their data to disk. On top of that, the technology for managing disk backups wasn’t great. I remember working for a small company and we couldn’t afford a VCS system. At least, I couldn’t pry money out of my boss for one, despite the fact that the business lived and died based on the code that was written. So we setup 5 folders on the network: Monday, Tuesday, Wednesday, Thursday, and you guessed it, Friday. Under each one, the other developer and I each had our own folder and a batch job that would copy data off our desktops to the appropriate folder on the appropriate day. We erased everything older than 5 days, though we still had tapes if we needed them.

    Today, with disk being nearly as cheap and much, much faster than tape, and the need to go back more than one version in a business environment being fairly rare, it makes some sense to use disk. Microsoft released their own software, that helps with data recovery on disk, so there must be either some growth or a very high profit margin in this area 🙂

    Personally, I think that the idea is a good one, but like many new technologies that change the way a user works, this one will take some time to get used to. I’m all for users being able to recover their own data without calling an admin, especially when the admin is me, but I’m sure that there will be lots of calls on how to find the files, which version, I forgot to save it, but my machine crashed and I want to go back 3 versions, annoyed calls when the last version on disk rolls off, etc.

    But I wouldn’t give up my tape. At J.D. Edwards, we backed up the databases to disk, then to tape that night, with two copies of the tapes being made. One went offsite and one was in a rotation on site. The triple protection served me well and we never had issues getting data off disk or one of the two tapes.

    Of course, that was expensive, so it’d not an option I’ve had often. Still, one disk and one tape worked out well and these days having disk be so cheap, a second copy on disk is something I’d shoot for.

    Steve Jones

  • T-SQL Tuesday #13– The Business and Disaster Recovery

    I’m hosting this month’s T-SQL Tuesday blog party, but that doesn’t mean I can’t participate. I posted the invitation last week, and hopefully we’ll have a large number of people participating today.

    The Cost of HADR

    I’ve dealt with a lot of business people over the years, and for a variety of reasons. At one job, I worked for a small company, literally about 50 people, and we had a number of systems critical to our business. We had a large fax server, receiving hundreds of faxes a day from overseas. We had a large imaging server/platform in which we stored our faxes along with quite a bit of other business correspondence that we needed a record of, and we had an inventory management/sales system running on SQL Server that people used every day to run the business.

    These were all important systems, and if they were down, business would slow to a crawl. The owner of the company wanted to take advantage of all our technology and mandated “no downtime” and “zero data loss” to my boss, the VP of sales for the company. He was, of course, happy to push this down to me as a goal for the next year.

    This was in 1997, and as I investigated our possibilities for achieving this, I realized very quickly that it wasn’t a good move for the company. Clustering in SQL Server v6.5 was shaky and hard to install and maintain. Marathon Technologies had a machine that was basically a “cluster-in-box” that would do a lot of what we wanted, but it would have been around a $30k server. Since our two SQL Servers cost about $7k together, this didn’t seem like a great solution to me.

    Instead I investigated what downtime cost us. What would it cost in terms of work and salary if we had to re-enter data that might be lost. We had been down for various periods of time due to different events, and the business had survived, even one day when we were shut completely down for 2 days.

    It quickly came to light that we’d have to be down for over a week to really cost the business more than $30k, and that was debatable. We could still conduct lots of work on paper and over the phone, so it didn’t make sense to aim for zero data loss and zero downtime. I explained it to my boss, laid out a simple case for him with a few numbers highlighted, and the provided an alternative for investment in a little redundancy, designed to minimize losses, but not prevent them.

    In the end the big boss agreed. He hadn’t really meant zero loss, after all, anyone in business knows that there are always going to be some losses. What he wanted was his business to keep running, cost effectively. That’s what we gave him at a much more reasonable price.