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

: 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.

Subscribe to Petri Newsletters Office 365 Insider Our Petri Office 365 Insider is dedicated to sharing detailed knowledge from top Office 365 experts. Delivered once a month to your inbox. All Newsletters Petri.com may use your contact information to provide updates, offers and resources that may be of interest to you. You can unsubscribe at any time. To learn more about how we manage your data, you can read our Privacy Policy and Terms of Service. !Already a Petri.com member? Login here for 1-click registration.

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: