Back up SQL Server databases in Azure VMs

SQL Server databases are critical workloads that require a low recovery-point objective (RPO) and long-term retention. You can back up SQL Server databases running on Azure virtual machines (VMs) by using Azure Backup.

This article shows how to back up a SQL Server database that's running on an Azure VM to an Azure Backup Recovery Services vault.

In this article, you'll learn how to:

  • Create and configure a vault.
  • Discover databases and set up backups.
  • Set up auto-protection for databases.

Note

Soft delete for SQL server in Azure VM and soft delete for SAP HANA in Azure VM workloads is now available in preview.
To sign up for the preview, write to us at AskAzureBackupTeam@microsoft.com

Prerequisites

Before you back up a SQL Server database, check the following criteria:

  1. Identify or create a Recovery Services vault in the same region and subscription as the VM hosting the SQL Server instance.
  2. Verify that the VM has network connectivity.
  3. Make sure that the SQL Server databases follow the database naming guidelines for Azure Backup.
  4. Check that you don't have any other backup solutions enabled for the database. Disable all other SQL Server backups before you back up the database.

Note

You can enable Azure Backup for an Azure VM and also for a SQL Server database running on the VM without conflict.

Establish network connectivity

For all operations, a SQL Server VM requires connectivity to Azure public IP addresses. VM operations (database discovery, configure backups, schedule backups, restore recovery points, and so on) fail without connectivity to Azure public IP addresses.

Establish connectivity by using one of the following options:

Allow the Azure datacenter IP ranges

This option allows the IP ranges in the downloaded file. To access a network security group (NSG), use the Set-AzureNetworkSecurityRule cmdlet. If your safe recipients list only includes region-specific IPs, you'll also need to update the safe recipients list the Azure Active Directory (Azure AD) service tag to enable authentication.

Allow access using NSG tags

If you use NSG to restrict connectivity, then you should use AzureBackup service tag to allows outbound access to Azure Backup. In addition, you should also allow connectivity for authentication and data transfer by using rules for Azure AD and Azure Storage. This can be done from the Azure portal or via PowerShell.

To create a rule using the portal:

  1. In All Services, go to Network security groups and select the network security group.
  2. Select Outbound security rules under Settings.
  3. Select Add. Enter all the required details for creating a new rule as described in security rule settings. Ensure the option Destination is set to Service Tag and Destination service tag is set to AzureBackup.
  4. Click Add, to save the newly created outbound security rule.

To create a rule using PowerShell:

  1. Add Azure account credentials and update the national clouds
    Add-AzureRmAccount

  2. Select the NSG subscription
    Select-AzureRmSubscription "<Subscription Id>"

  3. Select the NSG
    $nsg = Get-AzureRmNetworkSecurityGroup -Name "<NSG name>" -ResourceGroupName "<NSG resource group name>"

  4. Add allow outbound rule for Azure Backup service tag
    Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name "AzureBackupAllowOutbound" -Access Allow -Protocol * -Direction Outbound -Priority <priority> -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix "AzureBackup" -DestinationPortRange 443 -Description "Allow outbound traffic to Azure Backup service"

  5. Add allow outbound rule for Storage service tag
    Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name "StorageAllowOutbound" -Access Allow -Protocol * -Direction Outbound -Priority <priority> -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix "Storage" -DestinationPortRange 443 -Description "Allow outbound traffic to Azure Backup service"

  6. Add allow outbound rule for AzureActiveDirectory service tag
    Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name "AzureActiveDirectoryAllowOutbound" -Access Allow -Protocol * -Direction Outbound -Priority <priority> -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix "AzureActiveDirectory" -DestinationPortRange 443 -Description "Allow outbound traffic to AzureActiveDirectory service"

  7. Save the NSG
    Set-AzureRmNetworkSecurityGroup -NetworkSecurityGroup $nsg

Allow access by using Azure Firewall tags. If you're using Azure Firewall, create an application rule by using the AzureBackup FQDN tag. This allows outbound access to Azure Backup.

Deploy an HTTP proxy server to route traffic. When you back up a SQL Server database on an Azure VM, the backup extension on the VM uses the HTTPS APIs to send management commands to Azure Backup and data to Azure Storage. The backup extension also uses Azure AD for authentication. Route the backup extension traffic for these three services through the HTTP proxy. There are no wildcard domains in use with Azure Backup to add to the allow list for your proxy rules. You will need to use the public IP ranges for these services provided by Azure. The extensions are the only component that's configured for access to the public internet.

Connectivity options include the following advantages and disadvantages:

Option Advantages Disadvantages
Allow IP ranges No additional costs Complex to manage because the IP address ranges change over time

Provides access to the whole of Azure, not just Azure Storage
Use NSG service tags Easier to manage as range changes are automatically merged

No additional costs

Can be used with NSGs only

Provides access to the entire service
Use Azure Firewall FQDN tags Easier to manage as the required FQDNs are automatically managed Can be used with Azure Firewall only
Use an HTTP proxy Single point of internet access to VMs
Additional costs to run a VM with the proxy software
No published FQDN addresses, allow rules will be subject to Azure IP address changes

Database naming guidelines for Azure Backup

Avoid using the following elements in database names:

  • Trailing and leading spaces
  • Trailing exclamation marks (!)
  • Closing square brackets (])
  • Semicolon ';'
  • Forward slash '/'

Aliasing is available for unsupported characters, but we recommend avoiding them. For more information, see Understanding the Table Service Data Model.

Note

The Configure Protection operation for databases with special characters like "+” or “&" in their name is not supported. You can either change the database name or enable Auto Protection, which can successfully protect these databases.

Create a Recovery Services vault

A Recovery Services vault is an entity that stores backups and recovery points created over time. The Recovery Services vault also contains the backup policies that are associated with the protected virtual machines.

To create a Recovery Services vault, follow these steps.

  1. Sign in to your subscription in the Azure portal.

  2. On the left menu, select All services.

    Select All services

  3. In the All services dialog box, enter Recovery Services. The list of resources filters according to your input. In the list of resources, select Recovery Services vaults.

    Enter and choose Recovery Services vaults

    The list of Recovery Services vaults in the subscription appears.

  4. On the Recovery Services vaults dashboard, select Add.

    Add a Recovery Services vault

    The Recovery Services vault dialog box opens. Provide values for the Name, Subscription, Resource group, and Location.

    Configure the Recovery Services vault

    • Name: Enter a friendly name to identify the vault. The name must be unique to the Azure subscription. Specify a name that has at least 2 but not more than 50 characters. The name must start with a letter and consist only of letters, numbers, and hyphens.

    • Subscription: Choose the subscription to use. If you're a member of only one subscription, you'll see that name. If you're not sure which subscription to use, use the default (suggested) subscription. There are multiple choices only if your work or school account is associated with more than one Azure subscription.

    • Resource group: Use an existing resource group or create a new one. To see the list of available resource groups in your subscription, select Use existing, and then select a resource from the drop-down list. To create a new resource group, select Create new and enter the name. For more information about resource groups, see Azure Resource Manager overview.

    • Location: Select the geographic region for the vault. To create a vault to protect virtual machines, the vault must be in the same region as the virtual machines.

      Important

      If you're not sure of the location of your VM, close the dialog box. Go to the list of virtual machines in the portal. If you have virtual machines in several regions, create a Recovery Services vault in each region. Create the vault in the first location, before you create the vault for another location. There's no need to specify storage accounts to store the backup data. The Recovery Services vault and Azure Backup handle that automatically.

  5. When you're ready to create the Recovery Services vault, select Create.

    Create the Recovery Services vault

    It can take a while to create the Recovery Services vault. Monitor the status notifications in the Notifications area at the upper-right corner of the portal. After your vault is created, it's visible in the list of Recovery Services vaults. If you don't see your vault, select Refresh.

    Refresh the list of backup vaults

Discover SQL Server databases

How to discover databases running on a VM:

  1. In the Azure portal, open the Recovery Services vault you use to back up the database.

  2. In the Recovery Services vault dashboard, select Backup.

    Select Backup to open the Backup Goal menu

  3. In Backup Goal, set Where is your workload running? to Azure.

  4. In What do you want to backup, select SQL Server in Azure VM.

    Select SQL Server in Azure VM for the backup

  5. In Backup Goal > Discover DBs in VMs, select Start Discovery to search for unprotected VMs in the subscription. This search can take a while, depending on the number of unprotected VMs in the subscription.

    • Unprotected VMs should appear in the list after discovery, listed by name and resource group.

    • If a VM isn't listed as you expect, see whether it's already backed up in a vault.

    • Multiple VMs can have the same name, but they'll belong to different resource groups.

      Backup is pending during search for DBs in VMs

  6. In the VM list, select the VM running the SQL Server database > Discover DBs.

  7. Track database discovery in Notifications. The time required for this action depends on the number of VM databases. When the selected databases are discovered, a success message appears.

    Deployment success message

  8. Azure Backup discovers all SQL Server databases on the VM. During discovery, the following elements occur in the background:

    • Azure Backup registers the VM with the vault for workload backup. All databases on the registered VM can be backed up to this vault only.

    • Azure Backup installs the AzureBackupWindowsWorkload extension on the VM. No agent is installed on a SQL database.

    • Azure Backup creates the service account NT Service\AzureWLBackupPluginSvc on the VM.

      • All backup and restore operations use the service account.
      • NT Service\AzureWLBackupPluginSvc requires SQL sysadmin permissions. All SQL Server VMs created in the Marketplace come with the SqlIaaSExtension installed. The AzureBackupWindowsWorkload extension uses the SQLIaaSExtension to automatically get the required permissions.
    • If you didn't create the VM from the Marketplace or if you are on SQL 2008 and 2008 R2, the VM may not have the SqlIaaSExtension installed, and the discovery operation fails with the error message UserErrorSQLNoSysAdminMembership. To fix this issue, follow the instructions under Set VM permissions.

      Select the VM and database

Configure backup

  1. In Backup Goal > Step 2: Configure Backup, select Configure Backup.

    Select Configure Backup

  2. In Select items to backup, you see all the registered availability groups and standalone SQL Server instances. Select the arrow to the left of a row to expand the list of all the unprotected databases in that instance or Always On availability group.

    Displaying all SQL Server instances with standalone databases

  3. Choose all the databases you want to protect, and then select OK.

    Protecting the database

    To optimize backup loads, Azure Backup sets a maximum number of databases in one backup job to 50.

    • To protect more than 50 databases, configure multiple backups.
    • To enable the entire instance or the Always On availability group, in the AUTOPROTECT drop-down list, select ON, and then select OK.

    Note

    The auto-protection feature not only enables protection on all the existing databases at once, but also automatically protects any new databases added to that instance or the availability group.

  4. Select OK to open Backup policy.

    Enable auto-protection for the Always On availability group

  5. In Backup policy, choose a policy and then select OK.

    • Select the default policy as HourlyLogBackup.

    • Choose an existing backup policy previously created for SQL.

    • Define a new policy based on your RPO and retention range.

      Select Backup policy

  6. In Backup, select Enable backup.

    Enable the chosen backup policy

  7. Track the configuration progress in the Notifications area of the portal.

    Notification area

Create a backup policy

A backup policy defines when backups are taken and how long they're retained.

  • A policy is created at the vault level.
  • Multiple vaults can use the same backup policy, but you must apply the backup policy to each vault.
  • When you create a backup policy, a daily full backup is the default.
  • You can add a differential backup, but only if you configure full backups to occur weekly.
  • Learn about different types of backup policies.

To create a backup policy:

  1. In the vault, select Backup policies > Add.

  2. In Add, select SQL Server in Azure VM to define the policy type.

    Choose a policy type for the new backup policy

  3. In Policy name, enter a name for the new policy.

  4. In Full Backup policy, select a Backup Frequency. Choose either Daily or Weekly.

    • For Daily, select the hour and time zone when the backup job begins.

    • For Weekly, select the day of the week, hour, and time zone when the backup job begins.

    • Run a full backup, because you can't turn off the Full Backup option.

    • Select Full Backup to view the policy.

    • You can't create differential backups for daily full backups.

      New backup policy fields

  5. In RETENTION RANGE, all options are selected by default. Clear any retention range limits that you don't want, and then set the intervals to use.

    • Minimum retention period for any type of backup (full, differential, and log) is seven days.

    • Recovery points are tagged for retention based on their retention range. For example, if you select a daily full backup, only one full backup is triggered each day.

    • The backup for a specific day is tagged and retained based on the weekly retention range and the weekly retention setting.

    • Monthly and yearly retention ranges behave in a similar way.

      Retention range interval settings

  6. In the Full Backup policy menu, select OK to accept the settings.

  7. To add a differential backup policy, select Differential Backup.

    Retention range interval settings Open the differential backup policy menu

  8. In Differential Backup policy, select Enable to open the frequency and retention controls.

    • You can trigger only one differential backup per day.
    • Differential backups can be retained for a maximum of 180 days. For longer retention, use full backups.
  9. Select OK to save the policy and return to the main Backup policy menu.

  10. To add a transactional log backup policy, select Log Backup.

  11. In Log Backup, select Enable, and then set the frequency and retention controls. Log backups can occur as often as every 15 minutes and can be retained for up to 35 days.

  12. Select OK to save the policy and return to the main Backup policy menu.

    Edit the log backup policy

  13. On the Backup policy menu, choose whether to enable SQL Backup Compression or not. This option is disabled by default. If enabled, SQL Server will send a compressed backup stream to the VDI. Please note that Azure Backup overrides instance level defaults with COMPRESSION / NO_COMPRESSION clause depending on the value of this control.

  14. After you complete the edits to the backup policy, select OK.

Note

Each log backup is chained to the previous full backup to form a recovery chain. This full backup will be retained until the retention of the last log backup has expired. This might mean that the full backup is retained for an extra period to make sure all the logs can be recovered. Let’s assume user has a weekly full backup, daily differential and 2 hour logs. All of them are retained for 30 days. But, the weekly full can be really cleaned up/deleted only after the next full backup is available i.e., after 30 + 7 days. Say, a weekly full backup happens on Nov 16th. As per the retention policy, it should be retained until Dec 16th. The last log backup for this full happens before the next scheduled full, on Nov 22nd. Until this log is available until Dec 22nd, the Nov 16th full can't be deleted. So, the Nov 16th full is retained until Dec 22nd.

Enable auto-protection

You can enable auto-protection to automatically back up all existing and future databases to a standalone SQL Server instance or to an Always On availability group.

  • There's no limit on the number of databases you can select for auto-protection at one time.
  • You can't selectively protect or exclude databases from protection in an instance at the time you enable auto-protection.
  • If your instance already includes some protected databases, they'll remain protected under their respective policies even after you turn on auto-protection. All unprotected databases added later will have only a single policy that you define at the time of enabling auto-protection, listed under Configure Backup. However, you can change the policy associated with an auto-protected database later.

To enable auto-protection:

  1. In Items to backup, select the instance for which you want to enable auto-protection.

  2. Select the drop-down list under AUTOPROTECT, choose ON, and then select OK.

    Enable auto-protection on the availability group

  3. Backup is configured for all the databases together and can be tracked in Backup Jobs.

If you need to disable auto-protection, select the instance name under Configure Backup, and then select Disable Autoprotect for the instance. All databases will continue to be backed up, but future databases won't be automatically protected.

Disable auto-protection on that instance

Next steps

Learn how to: