Backup strategy| Azure VM snapshots VS SQL server backups for ready only databases - Which is the best option?

  • Keistad

    Ten Centuries

    Points: 1036

    I am designing a backup strategy for one of the new applications. We have a Azure SQL server VM (IaaS) which hosts one critical ready only database and we are using SQL server 2017 version.

    As a general traditional practice, we configure full, differential and log backups on our SQL server. But I am trying to challenge this idea to ourselves and answer "why do we need SQL backups when we can take VM snapshots which will serve the purpose considering its a ready only database and it is under simple recovery model"

    Could anyone help me to understand why we may/have to opt for SQL server backups over VM snapshots in Azure?

  • Sue_H

    SSC Guru

    Points: 89869

    Keistad - Thursday, November 15, 2018 12:08 AM

    I am designing a backup strategy for one of the new applications. We have a Azure SQL server VM (IaaS) which hosts one critical ready only database and we are using SQL server 2017 version.

    As a general traditional practice, we configure full, differential and log backups on our SQL server. But I am trying to challenge this idea to ourselves and answer "why do we need SQL backups when we can take VM snapshots which will serve the purpose considering its a ready only database and it is under simple recovery model"

    Could anyone help me to understand why we may/have to opt for SQL server backups over VM snapshots in Azure?

    If you needed the backup file for restores to a different environment, on premise server

    Sue

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88019

    Sue_H - Thursday, November 15, 2018 2:46 PM

    Keistad - Thursday, November 15, 2018 12:08 AM

    I am designing a backup strategy for one of the new applications. We have a Azure SQL server VM (IaaS) which hosts one critical ready only database and we are using SQL server 2017 version.

    As a general traditional practice, we configure full, differential and log backups on our SQL server. But I am trying to challenge this idea to ourselves and answer "why do we need SQL backups when we can take VM snapshots which will serve the purpose considering its a ready only database and it is under simple recovery model"

    Could anyone help me to understand why we may/have to opt for SQL server backups over VM snapshots in Azure?

    If you needed the backup file for restores to a different environment, on premise server

    Sue

    If this is truly a read-only database then it must be sourced from somewhere...how is this database built?  Is it ever updated and changed - and if so how is that process managed?

    If the read-only database is sourced from another instance (e.g. backup and restore from other system) then you don't need local backups.
    If the read-only database is built with a process and then set to read-only then you really only need a single backup taken after the build and stored in a safe location.

    Unless there is other data on that server that is changing on a regular basis - then a daily snapshot isn't even necessary.  A single snapshot would suffice - but that will depend on how large the snapshot gets over time due to delta changes.

    With all of that said - setting up native SQL backups is a good practice regardless of other options that are available.  It is also much easier to manage if you know that all SQL Server systems will have a defined backup and scheduled maintenance.  Just because a database is set to read-only does not mean you can ignore running integrity checks - but you can avoid index rebuild and statistics updates (if the data isn't changing on some scheduled basis).

    The only advantage to adding (not replacing) VM snapshots is that you can recover the whole system if needed.  The advantage to native backups and maintenance is a standardized approach across all servers regardless of other available technologies which leads to consistent management of all systems.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

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

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