Voice of the DBA

Tag: disaster recovery

  • An Overview of SQL Server AlwaysOn

    One of the changes coming in SQL Server v11 (not sure what the name will actually be), also known as “Denali”, is the addition of more High Availability/Disaster Recovery Options. When I first heard about this, I was excited, but the public CTP had limited support for it. I’m hoping the next CTP will substantially improve the feature and this makes it to the RTM.

    In the meantime, here’s a Channel 9 video from Justin Erickson that gives a short explanation of how this works and how it compares with the current Mirroring/Log Shipping/Clustering options.

    SQL Server AlwaysOn

  • Avoiding Logging

    I can avoid this kind of logging, but not SQL Server's kind.

    As I have been studying more about SQL Server internals this year, one of the myths that keeps being debunked is the idea there are operations in SQL Server that are not logged. That’s not true as everything you do in SQL Server is logged to the transaction log in order to ensure that SQL Server remains ACID compliant and cannot end up in an unrecoverable state.

    I’m not sure where this myth started, but I suspect that some version of the telephone game is at fault. I constantly see people asking for a way to insert or delete data from SQL Server without affecting the transaction log. One of these wish list requests likely got transformed into an imaginary feature that many people think has to exist in the product. Surely there is the capability to not log things if they are deemed unimportant.

    There isn’t, and you can’t, and I’m not sure that I’d even want this feature. Logging introduces overhead, and it can slow down processing, but it also provides a tremendous amount of safety and security. Computers will crash, power will get turned off, and hardware will fail. Mr. Murphy will ensure that one of these will occur while a large import of some sort is taking place. I don’t know how the rest of you feel, but personally I would prefer that when my database is restarted, I can be sure that my data is in a consistent state. Transaction log logging ensures this is the case in SQL Server.

    We often make decisions based on the data sets we have, and for the most part we want to be sure that all the data we added, updated, or removed from the system is in a known state. We do not want to accept partial transactions being recorded. We need to be sure that every debit into an account is matched up with a credit out of another account. Too many people seem to think that either their systems will never crash, or that this reliability can be guaranteed without logging.

    Accept the overhead of logging, and be glad that it’s unavoidable. At some point in your career, I’m sure you’ll be grateful.

    Steve Jones

    The Voice of the DBA Podcasts

  • Backing up the log in simple mode

    Someone posted a note saying that their backups were really large and slow. So they were trying to manage their backup scheme and ensure some level of recovery.  I was going back and forth, along with others, talking about the way your recovery would go and the merits of that particular backup scheme. While posting, the person mentioned at one point that his databases were in simple mode.

    Huh?

    AFAIK, or rather, As-Far-As-I-Remember, you can’t do a log backup in simple mode. I think that’s been a part of the product, but before posting, I decided to test.

    Short answer, if you don’t want to read, is that you can’t backup a transaction log if the database is in simple mode.

    First I grabbed a sample database on my local 2008 instance and set it to simple mode. Actually, first I had to set a database to simple mode, and I didn’t want to use the GUI. This isn’t something I do often, so I had to look up the syntax:

    ALTER DATABASE SET OPTIONS – The first example shows setting to full recovery mode.

    ALTER DATABASE db2 SET RECOVERY SIMPLE

    Once this was set, I created a few transactions and ran a backup.

    CREATE TABLE MyTable (id INT)

INSERT MyTable DEFAULT VALUES
    BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    This gives me the following error:

    Msg 4208, Level 16, State 1, Line 2

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Msg 3013, Level 16, State 1, Line 2

    BACKUP LOG is terminating abnormally.

    This is what I expect, as there is no backup chain that can be used to build a backup. What about running a full and then a log right away?

    BACKUP DATABASE db2 TO DISK = 'db2_db_test.bak'
BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    Once again, the error occurs, and there should be no checkpoint after the full backup starts.

    db_simple_log

    So I think that my posted is mis-informed of his environment, which is a dangerous place for a DBA to be, especially with regard to backup and 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.