November 15, 2018 at 12:09 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?
November 15, 2018 at 2:46 pm
Keistad - Thursday, November 15, 2018 12:08 AMI 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
November 18, 2018 at 9:08 am
Sue_H - Thursday, November 15, 2018 2:46 PMKeistad - Thursday, November 15, 2018 12:08 AMI 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.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply