Blog

Azure Backup: SQL Server Backups on Azure and How to Use Them

A successful business continuity strategy depends on how fast your data can be retrieved in the event of an unexpected outage. Investing in a well-rounded backup solution is critical for databases, and the cloud is the most cost-effective part of such plans. Even if you aren’t using a fully native Azure database, an on-prem database such as SQL Server can back up to economic cloud storage on Azure.

Backing up SQL databases is considered critical, since the SLAs of many line-of-business enterprise applications depend on these databases’ RPO and backup retention. Azure Backup enables organizations to take backup of databases in SQL Server in Azure, thereby helping them to achieve the required RPO and long-term retention goals. This blog will give a step-by-step walkthrough of how to leverage Azure Backup to protect SQL workloads in Azure.

What is Azure Backup?

Azure Backup is a cloud-based backup service that can be used to protect workloads deployed on-premises as well as in Azure. Azure Backup can be used to create backups of on-premises workloads such as VMs in VMware, Hyper-V, SQL databases, Exchange, SharePoint, and much more.

There are multiple uses for Azure Backup, SQL Server backup being just one of them. Let’s take a closer look at how to enable an Azure Backup solution for your SQL Server backup, starting with the prerequisites.

Prerequisites for Setting Up Azure Backup for SQL Server

Before we can start configuring backup for SQL Server on Azure, the following prerequisites are to be met:

Networking connectivity: The VM that hosted the SQL databases should have network connectivity to the following Azure services: Azure Backup, Azure storage, and Azure Active directory. If the VMs are protected by NSGs or Azure Firewall, then use the “AzureBackup” service tag and Azure datacenter FQDN/IPs to allow the access.

SQL DB naming convention: Azure Backup cannot be configured for databases with leading spaces, trailing spaces, exclamation marks, or special characters such as enclosing square brackets ([]), forward slashes ‘/’ and semicolons (;).

Other backup solutions: Disable other DB-level backup solutions for the machine. This is not applicable for Azure VM-level backup.

Backup account: A virtual service account named SERVICE\AzureWLBackupPluginSvc is created automatically in the SQL VM while enabling the backup. This account should have SQL sysadmin rights. For VMs that are not created from the Azure marketplace, customers should manually give the SQL sysadmin rights in the DB for the machine. Additionally, the NT AUTHORITY\SYSTEM account should have a public login on the SQL instance as this account is used for the DB discovery/inquiry by the Azure Backup service.

The illustration below is a high-level architecture of Azure Backup for SQL server. When you enable Azure Backup for SQL server, an extension named AzureBackupWindowsWorkload is installed in the machine which consists of a SQL plugin and coordinator service. These components together manage the backup/restore configuration in SQL and the dataflow to Azure Backup.

Azure backup architecture for SQL serverAzure backup architecture for SQL server [Image courtesy: Microsoft]

How to Enable SQL Server Backup on Azure

Once the prerequisites are completed, we can proceed with the backup configuration. To start with, the recovery services vault where the backup will be stored should be created if it doesn’t already exist.

1. Log in to the Azure portal and click on All services. Search for “recovery services.” Click on “Recovery Services Vaults” result:

Recovery Services Vaults

2. On the Recovery Services vaults page, click on “Create recovery services vault”:

Create recovery services vault

3.  In the next screen, provide the following details:

  • The name of your subscription
  • The resource group where the vault should be created
  • The vault name which is unique to your Azure subscription
  • The Azure regions where the vault will be deployed. Note that the vault should be created in the same region as the Azure VM hosting the SQL database.

TheAzure regions where the vault will be deployed

4. In the next screen, add any tags that are required. Tags are key-value pairs that help to logically group Azure resources and subscriptions for ease of manageability and also for enhanced visibility in billing.

When you are done, click on “Review + create” to continue.

review and create vault

5. Review the details and click on “Create” to provision the vault:

create recovery security vault

6. Once your new vault is created, open the Recovery services vault from the Azure portal and click on “+Backup”:


Add backup

7. In the Backup Goal screen, select the backup goals as follows:

  • Where is your workload running?: Make sure that this is selected as Azure.
  • What do you want to backup?: Select the option “SQL server in Azure VM” from the drop-down menu.

Backup goal

8. Click on “Start discovery” to check for VMs where backup is not configured:

Start discovery

9. Once the discovery is completed, select the SQL VM where the database is hosted and click on “Discover DBs”:

Discover DBs

10. The status of the DB discovery can be tracked from the Notifications panel.

Track status

11. When you are notified that the discovery has been successful, click on “Configure Backup” to continue:

configure backup

12. On the next screen, click on “Add resources” to proceed to the SQL instance and DB selection:

Add resources

13. From the discovered SQL instances and DBs in the server, select the DB for which you want to configure backup. Click on “OK” to continue.

Note: If you want to automatically protect all DBs in the SQL instance, existing and new ones, enable the “Autoprotect” option on this screen.

enable autoprotect

14. Azure Backup creates a default backup policy for the SQL DBs for daily backup of DB and logs. To create a custom policy, click on “Create a new policy”:

Click on "create. anew policy"

15. Give a name to the policy and configure the Full backup, Differential backup, Log backup schedule, and retention period per your organization’s RPO requirements and database backup strategy.


SQL Native backup compression can also be enabled from this screen. Click on “Create”:

Name the policy

16. Ensure that the new backup policy is selected and click on “Enable Backup”:

enable backup

17. Check your notifications to ensure that the backup has been enabled successfully:

check notifications

18. If you browse to the Recovery services Vault->protected items->Backup items, you can see that the SQL DB is listed there. The backup will be initiated based on the backup policy created in step 15.


As the initial backup is not completed, you will see a warning here. To initiate an ad hoc backup, click on the DB and select “Backup now”:

backup now

19. Select the backup type as “Full” as the initial backup is not yet completed. Compression is disabled by default, which can be enabled using the toggle. Click on “OK” to start the backup:

select as full

20. Once the backup is completed successfully, the status of the backup will be shown as healthy:

complete backup

Reinforced Protection for SQL Databases in Azure with Cloud Volumes ONTAP

We’ve just seen how to set up a cloud-based backup for SQL Server with Azure Backup. SQL Server can also backup to the cloud and gain additional capabilities with the help of Cloud Volumes ONTAP. Cloud Volumes ONTAP is the enterprise class data management solution available in Azure, delivering storage services over hybrid as well as cloud native environments.

This service can be leveraged to augment your SQL Server deployments in Azure and ensure end-to-end data protection using its unique data management features. Cloud Volumes ONTAP gives you the ability to manage your storage systems across multiple environments through a unified interface, meaning your SQL Servers deployed in Azure can also benefit from the same trusted NetApp storage technology as your on-premises SQL Servers.

Though Cloud Volumes ONTAP volumes can be accessed over SMB/NFS protocols by Azure SQL VMs, attaching them as iSCSI drives for storing SQL data is the most preferred option. With built-in advanced features such as deduplication, compression and thin-provisioning, Cloud Volumes ONTAP lowers Azure storage costs significantly for large-scale SQL deployments.

NetApp Snapshot™ technology helps protect the volumes hosting SQL databases using point-in-time copies of Cloud Volumes ONTAP volumes. The storage efficiency of Snapshot technology helps build your DR strategy without heavily impacting your cloud storage bill. These snapshots are application consistent, making restored copies of the databases assured to work without any glitches.

SnapMirror® data replication can be leveraged by organizations planning SQL migrations, as it is able to seamlessly transfer data to an Azure-based Cloud Volumes ONTAP instance. SnapMirror is also a key part to Cloud Volumes ONTAP’s disaster recovery processes as the DB copies on premises can be continuously synchronized with Azure Cloud Volumes ONTAP. Thus, your data can be recovered in the cloud if the on-premises systems are not available due to any catastrophic event.

Another feature of Cloud Volumes ONTAP worth mentioning is the FlexClone® data cloning technology. FlexClone creates space-efficient clones of DB volumes on-demand that are independent, writable clones of the data that can be used for testing and development purposes. This process is non-disruptive with zero performance impact on production workloads, and with storage consumed only for changes to the data, not for the clone itself.

Conclusion

Azure Backup, SQL Server, and Cloud Volumes ONTAP all help organizations protect mission-critical databases as part of an efficient data protection strategy that will minimize the impact of downtime. With Cloud Volumes ONTAP, these benefits are supplemented with instant snapshot capabilities, SnapMirror data replication, and FlexClone data cloning to develop a solid DR plan for their mission critical DB workloads.

New call-to-action

-