Voice of the DBA

Tag: high availability

  • 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.

  • 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

  • Multiple Mirrors

    Up through SQL Server 2008 R2 database mirroring has been limited to a single mirror for each database. While that does work well for many companies, as you become larger and more dependent on your computer systems, there is a need to have multiple mirrors.

    Often someone wants to failover to another database on the same network for capacity reasons if there are minor issues. Moving all clients to a secondary data center is not something you always want to do, especially as the secondary data center might not include the same level of bandwidth and may cost more.

    HADR

    In SQL 11, there is work underway to implement HADR, high availability and disaster recovery, which is an enhanced database mirroring. In this new technology, multiple mirrors are possible, allowing you to have multiple up to date copies of your data in separate locations. These are now called “replicas” and you get up to 4, according to Brent Ozar (HADRON Rocks). I didn’t see that listed, but I’m sure Brent knows more about this stuff than I do.

    The other nice things is that you can failover multiple databases together, which you put in an “availability group” and configure together. This is good since many applications might require multiple databases, and it could be important that all databases move to the same server at the same time.

    Overall this is cool, and I am looking forward to this technology being deployed. Only one replica works in CTP1, so we can’t really see this working, but it could be an amazing edition to your DR strategy in the future. One note is that this is built on Windows clustering, which no longer requires shared storage, but might require Enterprise Edition, so that will limit how widely this is deployed.

    I’ll write more on this later, but the ability to have multiple mirrors, and have multiple databases failover as a group, is something that DBAs should look forward to.

  • Database Mirroring Connection Strings – Automatic Failover

    I saw a post recently from Hugo Shebbeare that reminded me of something that I’ve seen asked often on the forums. One thing that I preach to people is that they should use their blog to show what they know, and in this case I want to do that. Also, I remind people to give credit to the inspiration, as I’ve done with the link to Hugo above.

    Database mirroring has automatic failover if you have newer SQL Server clients. Those of you with Vista or Windows 7 should be fine, though XP might need an update. SQL Server 2005 started distributing a client that would handle automatic failover, and it does this through the connection string. Here’s a typical one:

    Server=DBServer01;Database=Sales;Connection Timeout=30;Integrated Security=SSPI;Failover Partner=DBServer02

    In this string we have our main database instance (DBServer01) and the database (Sales). Our mirror server is called DBServer02, and the database name on this server would be the same. Note that you can use IP or named instances as in DBServer01\Sales as well for the connection strings.

    If the client has issues connecting to the primary, when the Connection Timeout passes, it will try to connect to the mirror server and start working there. Note that if you had a transaction in progress when the failure occurred, your application has to reconnect to the other server and resubmit the batch.