Azure Backup - Troubleshooting the backup issues for SQL Server on Azure VM or SAP HANA on Azure VM (Database).

When you have SQL databases on Azure VMs that are syncing with Azure Backup, you may run into various issues. These could be due to configurations or missing dependencies. In this blog post, we will take a look at a few of the common issues that I had faced and possible solutions to these issues.

1. First thing you should do for SAP HANA on Azure VM (Database).

Before you go down the path of troubleshooting, make sure that you have re-run the pre-registration script. You will either resolve the issues or will get more understanding of why the issues are coming. You can download the script form this link: Pre-registration Script

Note that you may need to re-discover the databases after running the script again. If you look in the official troubleshooting guide you will notice that most of the issues are resolved by this one thing i.e. re-running the pre-registration script.

2. Unhealthy status

Possible cause # 1:

• The unhealthy status could simply mean that you have just configured the backup on a database and there is no backup for the database present in the Azure recovery services vault. In this scenario, you can either wait for the backup schedule to trigger and take the initial backup. Or you can trigger a manual backup.

Possible cause # 2:

• Another reason could be that the Backup service is not able to take the backups of the SQL databases on the Azure VM. You should navigate to the Monitoring section in the recovery services vault and click on the Backup jobs. Filter the jobs by the name of the database or the Azure VM name. Look for any failed jobs and check the error details. These failed jobs will also contain the details about the reason why the job failed.

In one instance, my jobs were failing because the disk drive for Log was completely full. The resolution was to shut down the VM, expand the disk space via the Azure portal and then start the VM. Then expand the disk from the disk manager from within the VM. We manually triggered the full backup and it was successful. This in turn changed the status from Unhealthy to Healthy.

3. Unreachable or Not reachable status

The most common cause for this issue is that the Azure Backup service is not able to reach the VM. Chances are that the backup-related communication is being blocked somewhere in the network. The backup agent indicates to the VM that it should send the backup to Azure. The communication happens mostly Outbound from the VM to the below services in Azure:

1. Azure Backup
2. Azure Storage
3. Azure Active Directory

If any of the services is not allowed then the status changes to Unreachable.

You may have two or both scenarios in your environment:

1. Your VM has an NSG linked to the VM's network interface card or an NSG linked to the subnet in which the VM exists.
2. Your VM is in an environment where all communication is gated via a Network Virtual Appliance (NVA) or Firewall.

If you have NSGs then skip to the last section to see how to configure the NSGs for the backup service to work. If you have Firewall then continue here on how to troubleshoot.

You should look for outbound communication from the VM to these services in the Firewall if you have any. You will need to validate against the public IP addresses for these public services. But how will you find the IP addresses for these. We will look at that next.

4. How to find the IP addresses for Azure services

Microsoft publishes the public IP addresses for all of its services in a JSON file. This is categorized by Service tags. These service tags correspond to each of the Azure services.

You can download this JSON file by navigating to this link: Azure IP Ranges and Service Tags – Public Cloud

After downloading this JSON, search for "Storage", "Active Directory" and "Backup" services to find the section which defines a list of public IP addresses for that service. Note that there will be multiple IP address ranges for each service. Look for the region-specific ranges where you have deployed the VM and the backup vault.

If you find an IP address from the list being blocked for any of the 3 services mentioned above, then you should follow the next section and work with your Firewall team to open these required rules.

5. Configuration of the Firewall to allow Azure backup

Note this only applies to the backup of SQL Server on Azure VM or SAP HANA on Azure VM (Database). Allow access to the following services in the Firewall for the network infrastructure where the Azure VM exists.

6. NSG configurations to allow Azure backup

Note this only applies to the backup of SQL Server on Azure VM or SAP HANA on Azure VM (Database).

Go to the NSG i.e. Network Security Group of the Azure VM which is either linked via subnet or via the network interface of the VM. Navigate to the Outbound rules and add three rules to allow communication to the below destination Service Tags on all ports:

1. Storage
2. Azure Backup
3. Azure Active Directory

The rules will look like below:

7. Permissions issue or UserErrorSQLNoSysadminMembership

If you are getting the error for "UserErrorSQLNoSysadminMembership" then that means the permissions for SQL Server on Azure VM have not been set appropriately. This usually happens if you built the SQL server on the Azure VM yourself. That is you didn't use any marketplace image for SQL on Azure VM.

You need to set the permissions as defined here: Set VM permissions.

More Information

If you are still facing issues, do refer the below links for official documentation:

• Back up SAP HANA databases in an Azure VM
• Back up a SQL Server database in an Azure VM
• Troubleshoot SQL Server database backup by using Azure Backup
• Troubleshoot backup of SAP HANA databases on Azure