Azure SQL Database is a robust database service offered as part of Microsoft Azure. As part of the Azure backup architecture, Azure SQL Database has a built-in backup mechanism that lets you restore your database to any point in time, within a configurable retention window of 7-35 days. You can also create a Long Term Retention (LTR) policy and save database backups for up to 10 years.
In this post, we’ll explain Azure’s backup SQL capabilities, show how to restore a database from backup, and explain how NetApp Cloud Volumes ONTAP can help optimize and save costs on Azure backup storage.In this article, you will learn:
- What is Azure SQL Database backup
- Backup use cases
- Azure SQL Database Pricing
- Transparent Data Encryption for backups
- How to restore an Azure SQL Database from backup
- Considerations for recovering from backup
- Azure Backup with Cloud Volumes ONTAP
What is Azure SQL Database Backup?
Microsoft Azure SQL Database is a Database as a Service (DBaaS) offering provided as part of the Azure cloud. You can run an Azure SQL Database in three deployment modes:
- Single Database
- Virtual database as part of an Elastic Pool
- Managed Instance that lets you lift-and-shift existing SQL Server databases running on-premises (see our post on Azure migration)
Azure SQL Database has built-in backup functionality, with three levels of backups:
- Full database backups created weekly
- Differential database backups created every 12 hours
- Transaction log backups created every 5-10 minutes
Azure SQL Database offers Point in Time Restore (PITR), meaning you can select a point in time within the retention window of 7-35 days, and restore the database to its previous state at that time. Azure SQL Database backup uses read-access geo-redundant storage (RA-GRS), replicated to a paired data center to make backups resilient to data center outages.
Azure SQL Database Backup Use Cases
There are several scenarios in which you would want to restore an Azure SQL Database backup:
- Point in Time Restore (PITR) within the standard retention window—you can set the retention period to be between 7-35 days.
- Restore a database from a long-term backup—you can save a backup for up to 10 years, if needed for compliance or other purposes. You can do this for both Single Database or Elastic Pool, if you configured the database with a long-term retention policy (LTR).
- Restore a deleted database—if a database was deleted, you can restore it to the point at which it was deleted, or an earlier point in time within the retention period.
- Restore to another geographical region—typically, Azure SQL Database backups are restored to the same server or managed instance that were running your original database. However, if a disaster occurred and you cannot access your original server and database, you can use Geo-restore to recover your database to an existing server in any other Azure region around the world.
Azure SQL Database Pricing
Azure SQL Database has a variety of deployment options, each with its own pricing. In addition to the three deployment modes listed above, there are two compute models - provisioned and serverless - and two pricing models - DTU and vCore.
The two pricing models work as follows:
- Database Transaction Units (DTU) model - charges for Azure SQL Database according to the compute resources required to run your database. You can use a DTU calculator to calculate your approximate compute needs, and then select an instance types or pool size that provides the appropriate number of DTUs.
- vCore model - gives you a specified number of virtual SQL servers running in Azure, with physical hardware characteristics of your choice (CPU, RAM, etc), and a specific storage capacity. This model allows you to bring your existing Microsoft SQL Server licenses with you from your on-premise deployment, saving up to 55% on vCore costs.
To learn more, see our in-depth article about Azure SQL Database pricing .
Transparent Data Encryption for Backups
Azure SQL Database offers Transparent Data Encryption (TDE), which performs real-time encryption and decryption of your production database, its backups and transaction log files. Encryption is applied to data at rest. TDE is enabled by default for Azure SQL Database instances.
If you did not turn off TDE for your database, backups within the retention window as well as LTR backups are automatically encrypted at rest.
How to Restore an Azure SQL Database from Backup Using the Azure Console
Here is how to restore an Azure SQL Database from backup:1. In the Azure Console, select SQL Database and click through to your database server.
2. In the database overview screen, click Restore in the top toolbar.
3. For a Single Database, select Point-in-time backup (as in the screenshot below). This option is not shown for a Managed Instance. For both types of database, choose the database you wish to recover, and the date and time you want to recover from.
Source: Microsoft Azure
4. Click OK to start the restore operation.
Important Considerations for Recovering an Azure SQL Database from Backup
When planning your backup and recovery strategy, consider these important points:
- Recovery time—a database restore can take anywhere between several minutes to 12 hours or more. Restore time is affected by the size of the database, compute size, number of transaction logs involved in the restore, network bandwidth (if restoring to a different region), and the number of restore operations currently happening in the target region.
- Recovering a server—you can restore a database within an Azure SQL Database server, but there is no automated way to restore an entire server with several databases.
- Recovering to a new database—you can use the restore operation to create a new Azure SQL Database. If you want it to be a replacement for the original database, use the same compute size and service tier, and give the restored database the same name as the original database. Make sure you rename the original database if it is still running.
Recovering data into an existing database—you can also use data from your backup to populate an existing database, for example to recover from data corruption. However, this requires writing a data recovery script that extracts data from the restored database and imports it into the existing database.
Azure 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.
In particular, Cloud Volumes ONTAP will help you reduce storage footprint and Azure storage costs, leveraging:
- NetApp Snapshot™ technology that requires no additional storage and does not impact application performance.
- NetApp Storage Efficiencies—Thin provisioning, data compression, and deduplication reduce storage footprint and costs by up to 70%.
- Data Tiering—Automatically and seamlessly move infrequently-used data from block storage to object storage and back.
Want to learn more about Azure Backup?
Have a look at these articles:
- Azure Backup: 5 Things to Think About Before You Backup on Azure
- The 5 Enterprise-Grade Azure Features You Need to Know About: Azure Backup, Security and More
- Backup SQL Database in Azure: Step by Step
- Automating Your Disk Backup and Data Archive: Azure Database Backup
- Using Azure Backup Server to Backup Workloads and Files to Azure
Want to get started? Try out Cloud Volumes ONTAP today with a 30-day free trial.