Backups Best Practice for SQL Server IaaS

  • PhilipC

    Hall of Fame

    Points: 3124

    What is considered best practice for SQL backups for SQL Servers (specifically 2017) running in an Azure VM?

    I'm aware of:

    • Normal SQL Backups to Local Disks
    • Azure Backup
    • Backup to URL
    • VM Snapshots

    What I'm considering is:

    • Running an Azure Virtual Machine Backup to protect the running virtual machine, enabling me to restore the complete machine if it was lost.
    • Backup SQL Server using Azure Backup, for a true SQL Server backup with log protection/truncation enabling me to keep individual database backups for weeks/months.

    What I'm concerned about is the size of that VM Backup in that is going to grow and grow every time, but curious as to if the VM was to have problems, how I would get it back online quickly.

    What's the best approach here.

  • Grant Fritchey

    SSC Guru

    Points: 395653

    Whether running in a VM on Azure, AWS, or locally, a VM is just a VM. You're responsible for your database backups the same way across all of them. The VM backup will allow you to recover the databases on the VM, but only if you recover all of them at the same time. It won't allow for individual recovery. Personally, while I'd use it, I wouldn't count on it for my database backups. I'd still do standard full/differential/log backups on those so that I can meet individual Recovery Point Objectives and Recovery Time Objectives. Further, it makes testing recovery, a must, easier to do.

    So, both. The VM backup because you can recover everything, the database backups because you're much more likely to need discrete recovery.

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

  • Jeff Moden

    SSC Guru

    Points: 995144

    Grant Fritchey wrote:

    Whether running in a VM on Azure, AWS, or locally, a VM is just a VM. You're responsible for your database backups the same way across all of them. The VM backup will allow you to recover the databases on the VM, but only if you recover all of them at the same time. It won't allow for individual recovery. Personally, while I'd use it, I wouldn't count on it for my database backups. I'd still do standard full/differential/log backups on those so that I can meet individual Recovery Point Objectives and Recovery Time Objectives. Further, it makes testing recovery, a must, easier to do.

    So, both. The VM backup because you can recover everything, the database backups because you're much more likely to need discrete recovery.

    Preach it, brother.  I'll also restate to emphasize that the VM backups will not accommodate PIT (Point-in-Time) backups.

    --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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • PhilipC

    Hall of Fame

    Points: 3124

    Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

    What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.

     

     

    • This reply was modified 1 week, 2 days ago by  PhilipC.
  • Grant Fritchey

    SSC Guru

    Points: 395653

    PhilipC wrote:

    Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

    What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.

    Well, let's be clear. That shouldn't interfere with log backups. That's going to interfere with differentials, but not logs.

    And, if I'm using differentials as part of our RPO/RTO, that may have to be disabled. However, it's one of those things that gets worked out.

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

  • Jeff Moden

    SSC Guru

    Points: 995144

    PhilipC wrote:

    Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

    What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.

    I only take care of the SQL Server Native Backups.  The infrastructure folks take care of backing up the VMs and, since I do a full restore of my big database and a smaller one every night, I can assure you that we don't have a problem with the VM backups instantiating SQL Server full backups.  Not sure what their setup is.  I can take a look tomorrow to see if VSS Writer is disabled but I'm pretty sure that it isn't because SQL Server Backups kind of rely on that service a whole lot.

    --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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • PhilipC

    Hall of Fame

    Points: 3124

    Jeff Moden wrote:

    PhilipC wrote:

    Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

    What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.

    I only take care of the SQL Server Native Backups.  The infrastructure folks take care of backing up the VMs and, since I do a full restore of my big database and a smaller one every night, I can assure you that we don't have a problem with the VM backups instantiating SQL Server full backups.  Not sure what their setup is.  I can take a look tomorrow to see if VSS Writer is disabled but I'm pretty sure that it isn't because SQL Server Backups kind of rely on that service a whole lot.

    Question for your Jeff, how do you know that the VM level backups aren't instantiating SQL Server full backups unless you try and restore your transaction log backups from that full backup and find that the log chain is broken?

    I've always disabled the VSS Writer because of this issue and never had a problem with my SQL backups but interested when it comes to the cloud.

  • PhilipC

    Hall of Fame

    Points: 3124

    Grant Fritchey wrote:

    PhilipC wrote:

    Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

    What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.

    Well, let's be clear. That shouldn't interfere with log backups. That's going to interfere with differentials, but not logs.

    And, if I'm using differentials as part of our RPO/RTO, that may have to be disabled. However, it's one of those things that gets worked out.

    I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.

  • Jeff Moden

    SSC Guru

    Points: 995144

    PhilipC wrote:

    I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.

    People keep driving nails in the coffin of Azure for me.  The more I hear things about it like what you posted above, the more I hope I'll never have to work with it.

    To wit (and I guess I missed it), if this is all about an Azure instance, I'm not qualified to make anything but what I think are common sense recommendations because I don't use it.

    --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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • PhilipC

    Hall of Fame

    Points: 3124

    Jeff Moden wrote:

    PhilipC wrote:

    I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.

    People keep driving nails in the coffin of Azure for me.  The more I hear things about it like what you posted above, the more I hope I'll never have to work with it.

    To wit (and I guess I missed it), if this is all about an Azure instance, I'm not qualified to make anything but what I think are common sense recommendations because I don't use it.

    No problem, appreciate you taking the time to contribute to the thread anyway mate 🙂

  • Grant Fritchey

    SSC Guru

    Points: 395653

    PhilipC wrote:

    I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.

    Don't take my word for it. Here's one of Paul Randal's myth busting posts. Log chain breaks are caused by things other than full backups. Here's a TechNet article also listing causes of breaks to the log chain, no full backup. Finally, here's one, of several, of Gail Shaw's posts on the topic saying, quite explicitly, full backups don't interfere with the log chain.

    Now, I'm not saying that whatever process you're using for the Azure backups might not be breaking the log chain, but if they are, it's got to be a change in the recovery model or something else that's occurring, because it's not the full backup.

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

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

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