When it comes to database backups for IaaS based Azure SQL Servers you have 3 options.

1. Automated Backups, both V1 and V2 depending on the SQL Server version.
2. Azure Backup leveraging the power of Azure Recovery Services Vault.
3. Manual Mode – roll your own backups to URL.

Lets look at number 2. I have not covered this yet. Microsoft labels this ” Enterprise class backup capability for SQL Server on Azure VMs. All backups are stored and managed in a Recovery Services vault”.

Advantages of the solution? (ref:https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore#:~:text=2014%20Virtual%20Machines-,Azure%20Backup%20for%20SQL%20VMs,in%20a%20Recovery%20Services%20vault.)

• Zero-infrastructure backup: You do not have to manage backup servers or storage locations.
• Scale: Protect many SQL VMs and thousands of databases.
• Pay-As-You-Go: This capability is a separate service provided by Azure Backup, but as with all Azure services, you only pay for what you use.
• Central management and monitoring: Centrally manage all of your backups, including other workloads that Azure Backup supports, from a single dashboard in Azure.
• Policy driven backup and retention: Create standard backup policies for regular backups. Establish retention policies to maintain backups for years.
• Support for SQL Always On: Detect and protect a SQL Server Always On configuration and honor the backup Availability Group backup preference.
• 15-minute Recovery Point Objective (RPO): Configure SQL transaction log backups up to every 15 minutes.
• Point in time restore: Use the portal to recover databases to a specific point in time without having to manually restore multiple full, differential, and log backups.
• Consolidated email alerts for failures: Configure consolidated email notifications for any failures.
• Role-based access control: Determine who can manage backup and restore operations through the portal.

Lets look into the Azure portal. The first step I suggest is create the recovery vault. I will assume you have an IaaS based SQL Server to work with.

 

 

As you can see, I already have one. If you do not have one, click add. Once created click BACKUP.

Your backup goal will be – SQL server in Azure VM.

Next you configure the policies, you will always have a default one (policy type Azure VM). You will need to create a policy for SQL. You know, the classic Full, Diff and Log backups.

As you can see, I have a full with hourly based log backups.

Important note here for real world scenarios, some planning will be required to get this service setup and configured to communicate with the VM and underlying components that the service requires. I kept it simple for this post by putting everything within the same VNET. Please read this to understand what you might need to do regarding connectvity.

(https://docs.microsoft.com/en-us/azure/backup/backup-sql-server-database-azure-vms#establish-network-connectivity)

Now, once you have setup the policy and enabled it, you should monitor it by looking at the “backup job” section below.

While still in the configuring stage, it is a good idea to configure alerts too which you will find under the monitor section, just a little tip.