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.
Before you back up a SQL Server database, check the following criteria:
- Identify or create a Recovery Services vault in the same region or locale as the VM hosting the SQL Server instance.
- Verify that the VM has network connectivity.
- Make sure that the SQL Server databases follow the database naming guidelines for Azure Backup.
- Specifically for SQL 2008 and 2008 R2, add registry key to enable server registration. This step will be not be required when the feature is generally available.
- 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.
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 IP ranges in the download. To access a network security group (NSG), use the Set-AzureNetworkSecurityRule cmdlet. If you're safe recipients list only 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 portal or PowerShell.
To create a rule using portal:
- In All Services, go to Network security groups and select the network security group.
- Select Outbound security rules under Settings.
- 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.
- Click Add, to save the newly created outbound security rule.
To create a rule using Powershell:
- Add Azure account credentials and update the national clouds
- Select the NSG subscription
Select-AzureRmSubscription "<Subscription Id>"
- Select the NSG
$nsg = Get-AzureRmNetworkSecurityGroup -Name "<NSG name>" -ResourceGroupName "<NSG resource group name>"
- 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"
- 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. The extensions are the only component that's configured for access to the public internet.
Connectivity options include the following advantages and 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||Granular control in the proxy over the storage URLs is allowed
Single point of internet access to VMs
Not subject to Azure IP address changes
|Additional costs to run a VM with the proxy software|
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.
Add registry key to enable registration
Create the Registry Directory Path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WorkloadBackup\TestHook (you will need to create the 'Key' TestHook under WorkloadBackup which in turn needs to be created under Microsoft).
Under the Registry Directory Path, create a new 'string value' with the string name AzureBackupEnableWin2K8R2SP1 and value: True
Alternatively, you can automate this step by running .reg file with the following command:
Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WorkloadBackup\TestHook] "AzureBackupEnableWin2K8R2SP1"="True"
Create a Recovery Services vault
A Recovery Services vault is an entity that stores the 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:
Sign in to your subscription in the Azure portal.
On the left menu, select All services.
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.
The list of Recovery Services vaults in the subscription appears.
On the Recovery Services vaults dashboard, select Add.
The Recovery Services vault dialog box opens. Provide values for the Name, Subscription, Resource group, and Location.
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 two, 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 box. To create a new resource group, select Create new and enter the name. For complete 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.
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 the Azure Backup service handle that automatically.
When you're ready to create the Recovery Services vault, select Create.
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.
Discover SQL Server databases
How to discover databases running on a VM:
In the Azure portal, open the Recovery Services vault you use to back up the database.
In the Recovery Services vault dashboard, select Backup.
In Backup Goal, set Where is your workload running? to Azure.
In What do you want to backup, select SQL Server in Azure VM.
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.
In the VM list, select the VM running the SQL Server database > Discover DBs.
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.
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.
In Backup Goal > Step 2: Configure Backup, select Configure Backup.
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.
Choose all the databases you want to protect, and then select OK.
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.
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.
Select OK to open Backup policy.
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.
In Backup, select Enable backup.
Track the configuration progress in the Notifications area of the portal.
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:
In the vault, select Backup policies > Add.
In Add, select SQL Server in Azure VM to define the policy type.
In Policy name, enter a name for the new policy.
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.
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.
In the Full Backup policy menu, select OK to accept the settings.
To add a differential backup policy, select Differential Backup.
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.
Select OK to save the policy and return to the main Backup policy menu.
To add a transactional log backup policy, select Log Backup.
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.
Select OK to save the policy and return to the main Backup policy menu.
On the Backup policy menu, choose whether to enable SQL Backup Compression.
- Compression is disabled by default.
- On the back end, Azure Backup uses SQL native backup compression.
After you complete the edits to the backup policy, select OK.
Modify policy to change backup frequency or retention range.
Any change in the retention period will be applied retrospectively to all the older recovery points besides the new ones.
In the vault dashboard, go to Manage > Backup Policies and choose the policy you want to edit.
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:
In Items to backup, select the instance for which you want to enable auto-protection.
Select the drop-down list under AUTOPROTECT, choose ON, and then select OK.
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.
Learn how to: