Azure Backup: the protection of SQL Server in Azure Virtual Machines

Azure Backup is enriched with an important new feature that allows you to natively protect SQL workload, running in IaaS virtual machines that reside in Azure. In this article we will explore the benefits and the characteristics of this new feature.

Figure 1 - Protection with Azure backups of SQL Server in Azure VMs

Azure Backup has always been with an approach cloud-first allowing you to protect your systems quickly, safe and effective. The SQL Server protection in Azure IaaS virtual machines provides the only solution of its kind, characterised by the following elements:

  • Zero-backup infrastructure: you do not need to maintain a classic backup infrastructure, composed from the backup server, by various agents installed on systems and storage that host backups. In addition, nor is it required to use backup scripts, often needed in other backup solutions, to protect SQL Server.
  • Monitor backups by Recovery Services Vault: Using the dashboard, you can easily and intuitively monitor various backup jobs for all types of workloads protected: Azure IaaS VMs, Azure Files and SQL server databases. You can also set up email notifications against unsuccessful backup or restore.
  • Centralized management: you have the option to configure common protection policy, usable for databases residing on separate servers, where is defined the scheduling and the retention for short-term and long-term backup.
  • Restore DB to a precise date and time: an intuitive graphical interface allows the operator to restore the most appropriate recovery point for the selected date. Azure Backup will take care of managing the restoration of full backups, differential and log backup chain in order to get the database at the selected time.
  • Recovery Point Objective (RPO) of 15 minutes: You can back up the transaction log every 15 minutes.
  • Pay as you go service (PAYG): billing takes place monthly on the basis of consumption and there are no upfront costs for the service.
  • Native integration with SQL Server APIs: Azure Backup invokes the native APIs of the solution to ensure a high efficiency and reliability of the operations performed. Backup jobs can be viewed using SQL Server Management Studio (SSMS).
  • Support for Always On Availability Group: the solution is able to back up databases that reside within an Availability Group (AG), ensuring the protection in case of failover events, honoring the preference backup set at the AG level.

This new feature supports the following versions of the operating system and SQL Server, independently that are VMs are generated by a marketplace image or less (SQL Server installation done manually).

Supported operating systems

  • Windows Server 2012
  • Windows Server 2012 R2
  • Windows Server 2016

Linux, at the moment, is not supported.

Supported SQL Server VersionsEditions

  • SQL 2012 Enterprise, Standard, Web, Developer, Express
  • SQL 2014 Enterprise, Standard, Web, Developer, Express
  • SQL 2016 Enterprise, Standard, Web, Developer, Express
  • SQL 2017 Enterprise, Standard, Web, Developer, Express

To take advantage of this feature, the following requirements must be met:

  • Have a Recovery Services vault in the same region where resides the Azure virtual machine hosting SQL databases to be protected.
  • The virtual machine with SQL Server need connectivity to Azure public IPs.
  • On the virtual machine that holds the databases to be protected must be present specific settings. Azure Backup requires the presence of the VM extension AzureBackupWindowsWorkload. This extension is installed in the virtual machine during the process of discovery and enables communication with Azure Backup. The extension installation involves the creation in the VM, by Azure Backup, of the Windows virtual service account named NT Service AzureWLBackupPluginSvc. This virtual service account needs permissions of log in and sysadmin on SQL side, to protect your databases.

To enable the backup of SQL workloads in virtual machine in Azure it is necessary to carry out a process of discovery and later you can configure the protection.

Discovery process

This paragraph shows the procedure to be followed, by accessing the Azure Portal, to enable discovery of databases:

Figure 2 – Initiation of the discovery process

Figure 3 – Discovery in progress

Figure 4 – Discovery of DBs on selected systems

 

Configuring SQL backup

After the discovery phase of the the databases you can proceed with the configuration of SQL Server backup.

Figure 5 - Start the backup configuration, post DBs discovery inside the VMs

Figure 6 – Selection of DBs to be protected

Figure 7 – Creation of the policy that defines the type of SQL backup and data retention

Figure 8 – Enabling backup

 

Backup monitor and restore process

Figure 9 – Dashboard of the Recovery Service vault

Figure 10 - Number of backup items of SQL in Azure VMs

Figure 11 – SQL backup status

By selecting the single DB you can start the restore process.

Figure 12 - Starting the restore process of the DB

Figure 13 – Selecting the destination where to restore the DB

Figure 14 – Selecting the restore point to use

Figure 15 – Restore settings and directories where to place the files

Figure 16 – Starting the restore job

 

The Cost of the Solution

The cost for the protection of SQL Server in Azure Backup is calculated on the number of instances protected (individual Azure VMs or Availability Groups). The cost of a single protected instance depends on the size, which is determined by the overall size of the various protected DBs (without considering the amount of compression and encryption). At this cost it has to be added the cost of Azure storage actually consumed. This is Block Blob Storage including locally redundant storage (LRS) or geo-redundant storage (GRS). For more details on costs please visit the Microsoft's official page.

 

Conclusions

Azure Backup is enhanced with an important feature and confirms to be a great enterprise solution for systems protection, wherever they are. With this feature, Azure differs from any other public cloud, providing a solution for the protection of SQL Server in IaaS virtual machines, totally integrated into the platform. For more information on Azure Backup solution you can consult the official documentation.