Back up multiple SQL Server VMs from the Recovery Services vault
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 and subscription as the VM hosting the SQL Server instance.
- Verify that the VM has network connectivity.
- Make sure that the Azure Virtual Machine Agent is installed on the VM.
- Make sure that .NET 4.5.2 version or above is installed on the VM.
- Make sure that the SQL Server databases follow the database naming guidelines for Azure Backup.
- Ensure that the combined length of the SQL Server VM name and the resource group name doesn't exceed 84 characters for Azure Resource Manager VMs (or 77 characters for classic VMs). This limitation is because some characters are reserved by the service.
- 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.
- When using SQL Server 2008 R2 or SQL Server 2012, you might run into the time zone issue for backup as described here. Make sure that you are on the latest cumulative updates to avoid the time zone-related issue described above. If applying the updates to the SQL Server instance on the Azure VM isn't feasible, disable Daylight Saving Time (DST) for the time zone on the virtual machine.
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 the Azure Backup service, Azure Storage, and Azure Active Directory. This can be achieved by using private endpoints or by allowing access to the required public IP addresses or FQDNs. Not allowing proper connectivity to the required Azure services may lead to failure in operations like database discovery, configuring backup, performing backups, and restoring data.
The following table lists the various alternatives you can use for establishing connectivity:
|Private endpoints||Allow backups over private IPs inside the virtual network
Provide granular control on the network and vault side
|Incurs standard private endpoint costs|
|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
|Azure Firewall FQDN tags||Easier to manage since the required FQDNs are automatically managed||Can be used with Azure Firewall only|
|Allow access to service FQDNs/IPs||No additional costs
Works with all network security appliances and firewalls
|A broad set of IPs or FQDNs may be required to be accessed|
|Use an HTTP proxy||Single point of internet access to VMs||Additional costs to run a VM with the proxy software|
More details around using these options are shared below:
Private endpoints allow you to connect securely from servers inside a virtual network to your Recovery Services vault. The private endpoint uses an IP from the VNET address space for your vault. The network traffic between your resources inside the virtual network and the vault travels over your virtual network and a private link on the Microsoft backbone network. This eliminates exposure from the public internet. Read more on private endpoints for Azure Backup here.
If you use Network Security Groups (NSG), use the AzureBackup service tag to allow outbound access to Azure Backup. In addition to the Azure Backup tag, you also need to allow connectivity for authentication and data transfer by creating similar NSG rules for Azure AD (AzureActiveDirectory) and Azure Storage(Storage). The following steps describe the process to create a rule for the Azure Backup tag:
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.
Select Add to save the newly created outbound security rule.
You can similarly create NSG outbound security rules for Azure Storage and Azure AD.
Azure Firewall tags
If you're using Azure Firewall, create an application rule by using the AzureBackup Azure Firewall FQDN tag. This allows all outbound access to Azure Backup.
Allow access to service IP ranges
If you choose to allow access service IPs, refer to the IP ranges in the JSON file available here. You'll need to allow access to IPs corresponding to Azure Backup, Azure Storage, and Azure Active Directory.
Allow access to service FQDNs
You can also use the following FQDNs to allow access to the required services from your servers:
|Service||Domain names to be accessed||Ports|
|Azure AD||Allow access to FQDNs under sections 56 and 59 according to this article||As applicable|
Use 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. Use the list of IPs and FQDNs mentioned above for allowing access to the required services. Authenticated proxy servers aren't supported.
Disable proxy for localhost communications within the VM. Proxy will be honored for outbound communications from the SQL VM.
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.
Multiple databases on the same SQL instance with casing difference aren't supported.
Changing the casing of a SQL database isn't supported after configuring protection.
The Configure Protection operation for databases with special characters, such as '+' or '&', in their name isn't supported. You can change the database name or enable Auto Protection, which can successfully protect these databases.
Create a Recovery Services vault
A Recovery Services vault is a management entity that stores recovery points created over time and provides an interface to perform backup related operations. These include taking on-demand backups, performing restores, and creating backup policies.
To create a Recovery Services vault, follow these steps.
Sign in to your subscription in the Azure portal.
Search for Backup center in the Azure portal, and navigate to the Backup Center dashboard.
Select +Vault from the Overview tab.
Select Recovery Services vault and click Continue.
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 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 any data source, the vault must be in the same region as the data source.
If you're not sure of the location of your data source, close the dialog box. Go to the list of your resources in the portal. If you have data sources in multiple regions, create a Recovery Services vault for 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.
After providing the values, select Review + create.
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.
We highly recommend you review the default settings for Storage Replication type and Security settings before configuring backups in the vault. For more information, see the Set Storage redundancy section.
Discover SQL Server databases
How to discover databases running on a VM:
In the Azure portal, go to Backup center and click +Backup.
Select SQL in Azure VM as the datasource type, select the Recovery Services vault you have created, and then click Continue.
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.
Select Add Resources to see all the registered availability groups and standalone SQL Server instances.
In the Select items to backup screen, 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.
Define the Backup policy. You can do one of the following:
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 Enable Backup to submit the Configure Protection operation and 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:
Go to Backup center and click +Policy.
Select SQL Server in Azure VM as the datasource type, select the vault under which the policy should be created, and then click Continue.
In Policy name, enter a name for the new policy.
Select the Edit link corresponding, to Full backup, to modify the default settings.
- Select a Backup Frequency. Choose either Daily or Weekly.
- For Daily, select the hour and time zone when the backup job begins. 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.
Select OK to accept the setting for full backups.
Select the Edit link corresponding to Differential backup, to modify the default settings.
- In Differential Backup policy, select Enable to open the frequency and retention controls.
- You can trigger only one differential backup per day. A differential backup can't be triggered on the same day as a full backup.
- Differential backups can be retained for a maximum of 180 days.
- The differential backup retention period can't be greater than that of the full backup (as the differential backups are dependent on the full backups for recovery).
- Differential Backup isn't supported for the master database.
Select the Edit link corresponding to Log backup, to modify the default settings
- 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.
- If the database is in the simple recovery model, the log backup schedule for that database will be paused and so no log backups will be triggered.
- If the recovery model of the database changes from Full to Simple, log backups will be paused within 24 hours of the change in the recovery model. Similarly, if the recovery model changes from Simple, implying log backups can now be supported for the database, the log backups schedules will be enabled within 24 hours of the change in recovery model.
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. Azure Backup overrides instance level defaults with COMPRESSION / NO_COMPRESSION clause depending on the value of this control.
After you complete the edits to the backup policy, select OK.
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 you have 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, that is, after 30 + 7 days. For example, a weekly full backup happens on Nov 16th. According to 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.
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 a time. Discovery typically runs every eight hours. However, you can discover and protect new databases immediately if you manually run a discovery by selecting the Rediscover DBs option.
- 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: