2

I have a Microsoft SQL DB running on Azure VM

I have daily snapshots of the VM and this is currently my only backup mechanism.

Is this considered best practice? Am I risking something here?

1
  • 2
    Are you willing to lose 24 hours of transactions if the database crashes 23 hours 59 minutes after the last VM snapshot? Sep 18 '19 at 20:47
3

Before I tell you if you following best practice or no, I suggest you understand few terms which dictates what kind of backup plan is suitable for you. This will also enable you to decide if you are risking something or no.

Recovery models

There are three recovery models:

Full recovery model (the default and the most commonly used) All modifications in the database a fully logged The log will not clear until a transaction log backup is performed

Bulk-logged recovery model Some modifications (like an index rebuild or a bulk load) can be minimally logged, which reduces the amount of log records generated The log will not clear until a transaction log backup is performed, just like the full recovery model

Simple recovery model Some modifications can be minimally logged, just like the bulk-logged recovery model The log will not clear until a checkpoint occurs (usually automatically), and transaction log backups are not possible

Backup types

There are three commonly used backup types in SQL Server:

Full database backup

Transaction log backup

Differential database backup

RTO stands for Recovery Time Objective.

In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again. RTO is often talked about in terms of the number of nines of desirable up time or accessibility for the data/database/system. For example, 5-nines means 99.999% up-time, and if that’s being measured 24 hours a day, 365 days a year, then that translates into just over 5 minutes of allowable downtime per year. That’s really hard to achieve. It’s easier to meet 4-nines (52.5 minutes per year of downtime) or 3-nines (8.75 hours per year of downtime).

When discussing the desired up time, you have to decide whether it’s being measured 24×365, or 9am-5pm during weekdays, or some other time period. You also need to decide whether the measured downtime includes scheduled downtime for maintenance/patching or not, as it’s much easier to meet a high number of nines if scheduled maintenance is permitted.

RPO stands for Recovery Point Objective.

In simple terms, you can think of RPO as a measure of how much data or work it’s acceptable to lose. It’s relatively easy to achieve very minimal or even zero data/work loss using backups, but depending on the amount of damage the database suffered when the disaster hit, recovering might take a lot of time. For instance, if an entire database is destroyed, depending on the architecture of the database and the backups that exist, it may take a significant amount of time to recover the database up to the point of the disaster. Most RPOs are defined as the the amount of time for which work may be lost. For example, an RPO might be that the database should be recoverable to a point within thirty minutes of the disaster occurring, which means that up to 30 minutes of work may be lost.

Once you understand the above concept then you need to make a backup strategy. Make sure your business counter-part is in agreement with your strategy, especially about how much data you can lose. Read the article I listed in reference about planning a recovery strategy.

Reference:

  1. The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types by Paul Randal
  2. Database Maintenance Best Practices Part III – Transaction Log Maintenance by Kimberly Tripp
  3. The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy by Paul Randal
  4. The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO by Paul Randal

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.