Azure SQL Backups

In this post, I will explain how Azure automatically protects any databases you create with the Azure SQL service.
 

 

Database-as-a-Service Benefit

When you run SQL Server in a virtual machine, you are responsible for backups. I have found that people typically underestimate the workload involved here. Azure Backup for IaaS virtual machines will do a very nice job of backing up your virtual machines. But what about your databases? An “SQL backup” does not take place, therefore your transaction logs don’t get truncated. After some time, the volume(s) that contain transaction logs will be filled and SQL Server will grind to a halt. And to be honest, your DBAs probably won’t be happy with VM-only backups.
Note: Azure Backup announced at Microsoft Ignite 2017 that they are working on a service to back up SQL Server in Azure virtual machines with SQL-native full, differential, and transaction log backups.
If you deploy databases using Azure SQL, then Microsoft takes care of all your backups for you. Unless you go outside the norms, there’s nothing for you to do or even enable! Have I caught your interest now?

What Backups Are Done?

When you create your database, Azure will automatically start protection. The first full backup will commence and complete within 30 minutes of database creation. After that you will have:

• A weekly full backup
• Differential backups every few hours
• Transaction log backups every 5-10 minutes

The “prime order” of Azure SQL backups is that impact on the performance of the database should be minimized; this is why the above includes phrases such as “every few hours” or “5-10 minutes”, instead of something more precise. Azure is protecting your workload and other people’s workloads that are running on the underlying service fabric.

Note: Azure SQL runs on Azure Service Fabric, the PaaS service which was later made available to customers that want to develop microservices.

Backup Retention

There are two elements to the retention policy of your backups:

• Storage allocation versus requirements
• How long backups will be retained for
• Encryption
• Database deletion

You are given a certain amount of storage for free, based on the size of your database; this amount is 200 percent. For example, if you have created a database with 250GB of data storage, then you are also given 500GB of backup storage at no additional cost. If your backups require more than the allocated 200 percent, then there is an additional storage charge.
Note that the storage used for Azure SQL backups is Read-Access Geo-Redundant Storage (RA-GRS). That means there are 3 synchronous copies of the backups in the same region as the database and 3 synchronous copies in the paired region.
Backup retention is based on the tier of the database:

• Basic tier: 7 days of backup retention
• Standard and Premium tiers: 35 days of backup retention

If you require more retention, then it is possible to long-term retention (LTR) for up to 10 years using a recovery services vault. This was in preview at the time of writing.

 
If you are using Transparent Data Encryption (TDE) in your database, then the backups of that database will be encrypted. Any new databases that are created have TDE enabled by default.
Backups are retained for the above retention periods after a database is deleted; this means that an accidental “drop” can be recovered from.

Restores

You can restore Azure SQL databases in many circumstances including:

• Restore to a point in time (thanks to transaction log backups) within the retention period, resulting in a new database on the same server.
• Restore a deleted database to the same server that it was originally on, either to how it was when it was deleted or any point in time in the retention period.
• Restore a database to another Azure region to another server, providing you with a form of disaster recovery (with a relatively slow recovery time objective or RTO).
• Restore a database using an LTR recovery point from an Azure Recovery Service Vault. This allows you to restore from up to 10 years ago, depending on how you configured LTR.