RTO 15 minutes with differential backups

  • DBANewbie

    Hall of Fame

    Points: 3089

    Hi.

    I'm working through the official exam reference book for 70-764 by Isakov and some of the sample questions + answers are driving me crazy!

    One such example is for a question which asks you to come up with a backup strategy to meet a set of requirements, one of these requirements being that the RTO is 15 minutes.

    In the answer section for this question, the author states that an RTO of 15 minutes cannot be met by performing differential backups every 15 minutes but doesn't explain why this is the case. The author states that the correct answer is that log backups should be taken every 15 minutes instead. As far as i know, either of the two solutions could work, and you wouldn't be able to know this for sure until you tested how long it takes for the restore of the differential backups to complete.

    Am i mistaken here?

    Any help would be greatly appreciated!

  • Jeff Moden

    SSC Guru

    Points: 995648

    You have come across one of the primary reasons why I hate certification courses, exams, and the related books/articles.  They're a possibly decent starting point but, oh my...

    IMHO, it is quite feasible to actually reduce the RTO by taking more differential backups (you only need to restore the latest one) to reduce the number of transaction log backups that you'd have to restore after doing the initial FULL restore or after a later differential restore.  They also seem to have forgotten that the original required FULL restore might take more than 15 minutes all by itself.  There has to be more to the precursory information leading up to that question in order for the author to make such a claim.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sterling3721

    Old Hand

    Points: 393

    In my opinion, based on the answer, "an RTO of 15 minutes can be met by log backups every 15 minutes", it's more of an RPO, NOT RTO. log backup every 15 minutes or even differential if feasible can cut data loss to 15 minutes or less(RPO), but does not mean your system be up and running within 15 minutes. For example, if it's fail-over cluster and it's hardware issue, a fail-over only takes seconds(or minutes based on how soon crash recovery can finish); it's standalone instance, you may need to setup SQL server, restore backups(full, differential, log, etc) and it may take hours to be up and running(this is RTO). RTO can be accomplished via database mirroring/log shipping/Always On availability groups(synchronous or asynchronous).

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Recovery Time Objective (RTO) is not determined by what you back up or how frequently. Well, it is, but not the way described here. It's about how long it takes you to recover. In fact, a more frequent differential backup could make RTO faster. Recovery Point Objective (RPO), the amount of data expressed in time, that you're will to lose, that's what is usually covered by the frequency and type of backup.

    It all sounds a little backassward.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Brahmanand Shukla

    Right there with Babe

    Points: 777

    Backup policy can be leveraged more specifically for Point In Time Recovery. It can be also used for DR where the RTO applies but if your Database Size is huge then you can talk about RTO in minutes. I've had a Production instance in SQL 2008 with SSD drive with IO speed more than 2K, 64 GB RAM and 16 Cores and it was a physical server. The database size was around 850 GB. Restores use to take around an hour for Full Backup sometimes even more. You also need to add the time taken for Differential Backup and obviously Log Backups.

    Better RTO can be achieved by keeping a standby Secondary/DR server with replication enabled using Logshipping, AlwaysOn or Mirroring depending upon you version and edition of SQL Server. Recently I've also came across a concept in Azure where there is no Database Replication but the Disk based replication and they call it as ASR (Azure Site Recovery).

    P.S. You need to have your Secondary/DR over cloud for ASR.

  • DBANewbie

    Hall of Fame

    Points: 3089

    Thanks very much to each of you for taking the time to reply, I think that the common conception here is that the given answer doesn't actually fit the question. I'll just exclude it from my practice questions going forwards.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply