Azure SQL Database Backup & Recovery: The Abridged Guide

Azure SQL Database is a fully managed cloud-based database service, with built-in AI-based tuning, high availability, multi-layered security, and data protection. Azure SQL Database offers a wide range of data protection options, including backup and recovery.

In this post, we’ll examine Azure SQL Database backup and recovery options, including point in time restore (PITR), Azure SQL Database Managed Instance, and more. We’ll also explain how the database recovery process works in Azure SQL Database, and show how NetApp Cloud Volumes ONTAP can help address Azure database challenges.

In this article, you will learn:

• Azure SQL Database backup use cases
• High availability and Azure SQL Database
• Azure SQL Database backup storage consumption
• Azure SQL Database recovery process
• What is Accelerated Database Recovery (ADR)
• Azure SQL Database backup with Cloud Volumes ONTAP

Azure SQL Database Backup Use Cases

When you need to recover your database from backups, there are several restoration options you can choose from, including:

• Point in time restore (PITR)—restores your database to a specific point available in 12-hour increments. The number of points available for restoration depends on the retention period set for your backups (between 7 to 35 days).
• Restore a database from a long-term backup—you can configure long term retention (LTR) of backups for up to 10 years. LTR retains your weekly full backups but not the incremental changes, offering less frequent but longer recovery options.
• Restore a deleted database—you can restore a deleted database from the same server or instance it was created with any retained backup. You can also restore it to the point of deletion if that time was between backup periods.
• Restore to another geographical region—you can use existing backups to restore to a different region to recover from a geographic disaster.

High-Availability and Azure SQL Database

Azure SQL Database is built on a high-availability architecture with a service level agreement (SLA) guaranteeing 99.99% uptime. Azure is responsible for all servicing tasks, including patches, upgrades, maintaining hardware, responding to incidents, and SQL backups. Any downtime created by maintenance or the need for failovers is invisible as long as you enable retry logic in your applications.

As part of the Azure high-availability guarantee, Azure works to ensure that none of your data is lost, even in the case of hardware failures. Additionally, maintenance is performed around your workloads, ensuring your operations remain uninterrupted. This includes continuous upgrades performed to keep your database running on the latest stable version of the Azure SQL Server engine.

Additionally, when using SQL Database, there are two architectural models you can choose from: 

• Standard—employs a separation of compute and storage resources and relies on the availability and reliability of the storage components. This model is designed for budget-oriented applications that can withstand small performance drops created by maintenance activities.
• Premium—employs a cluster of database engine instances and relies on a quorum of nodes to provide availability and reliability. This model is designed for mission-critical applications that demand consistent transaction rates and I/O performance, even during maintenance.

Azure SQL Database Backup Storage Consumption

When using Azure SQL Database, it is important to understand how backups consume storage. This ensures that you are not caught off-guard by storage costs and helps you optimize your storage budgets.

Storage for the various deployment types are calculated as follows:

• Single—includes the sizes of full backups, differential backups, and log backups minus your database size.
• Elastic Pool—includes the sizes of full backups, differential backups, and log backups minus your allocated storage pool size.
• Azure SQL Database Managed Instance—includes the sizes of full backups, differential backups, and log backups minus your instance size. Learn more in our article about Azure SQL Managed Instance.

All deployment options are granted free backup storage equal to the data storage in use. After that, backup storage is charged depending on the workload and size of your databases in GB per month.

Within your backup storage, full backup copies are deleted when the timestamp exceeds the retention period. Differential and log backups are deleted weekly according to when the supporting full backup is deleted. These processes continue even after a database has been dropped or recreated.

Azure SQL Database Recovery Process

When you need to use your backups for recovery, it helps to understand how traditional recovery processes work. With the standard SQL Server recovery process, the ARIES model is used. In this model, there are three phases: 

• Analysis phase—the transaction log is scanned from the last successful checkpoint to the last entry. This determines the state of each transaction at the time of failure.
• Redo phase—the transaction log is scanned from the oldest uncommitted transaction until the last entry. This restores the database to its last known state.
• Undo phase—the transaction log is scanned from each active transaction at the time of failure to initiation. This reverses any operations performed by the unfinished transaction.

The recovery time for your database is based directly on the above processes. Timing depends specifically on the longest active transaction and the number of active transactions at the time of database failure. In short, this means that the shorter your transaction times and the fewer active transactions you have at once, the faster your recovery.

What Is Accelerated Database Recovery (ADR)?

To help speed up the recovery process without sacrificing long-running transactions, like bulk inserts, Azure offers the Accelerated Database Recovery (ADR) feature. This feature is built-in to the SQL Server engine. It is available for all deployment types of SQL Database and is in preview for Azure SQL Data Warehouse.

Using this feature during recovery can provide the following benefits:

• Fast and consistent database recovery—overall recovery speeds are not affected by the length or number of long-running transactions.
• Instantaneous transaction rollback—rollback doesn’t depend on the length of time a transaction was active or how many operations were performed.
• Aggressive log truncation—it does not require full log histories of transactions, reducing the amount of storage needed for logs.

Accelerated Azure SQL Database Recovery Components

ADR works by redesigning the recovery process into four recovery components. These include:

• Persisted Version Store (PVS)—contains the row versions created by the database, replacing the traditional tempdb version store. This improves the availability of readable secondaries and enables the isolation of resources.
• Logical revert—an asynchronous process that enables version-based, row-level reversal of operations. It works by marking aborted transactions invisible to other transactions, using PVS rather than log scanning to undo changes, and removing locks as soon as a transaction is aborted.
• sLog—an in-memory log stream used to store non-versioned operations, including lock acquisitions and metadata cache invalidation. This component is serialized during checkpoint processes and truncated as transactions are committed.
• Cleaner—an asynchronous process that periodically cleans page versions to reduce storage and speed processing.

The Accelerated Database Recovery Process

When the ADR components are applied, the same phases as the traditional recovery process are used. The difference is in the way these phases are performed. By limiting the amount of data that must be processed to perform phases, ADR can significantly speed recovery times. It accomplishes this without sacrificing consistency or reliability.

These improvements are possible because ADR versions all database modifications and only undoes logical operations. Rather than processing active transactions at the time of database failure, these transactions are treated as aborted and ignored.

Azure SQL Database Backup with Cloud Volumes ONTAP

NetApp Cloud Volumes ONTAP, the leading enterprise-grade storage management solution, delivers secure, proven storage management services on AWS, Azure and Google Cloud. Cloud Volumes ONTAP supports up to a capacity of 368TB, and supports various use cases such as file services, databases, DevOps or any other enterprise workload, with a strong set of features including high availability, data protection, storage efficiencies, Kubernetes integration, and more.

In particular, Cloud Volumes ONTAP can help you solve the storage component of a high availability strategy on Azure, and achieve storage efficiencies on Azure for SQL Database and other workloads.