Automated Backup v2 for SQL Server 2016 Azure Virtual Machines (Resource Manager)

Automated Backup v2 automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure VM running SQL Server 2016 Standard, Enterprise, or Developer editions. This enables you to configure regular database backups that utilize durable Azure blob storage. Automated Backup v2 depends on the SQL Server IaaS Agent Extension.

Note

Azure has two different deployment models for creating and working with resources: Resource Manager and classic. This article covers using the Resource Manager deployment model, which Microsoft recommends for new deployments instead of the classic deployment model.

Prerequisites

To use Automated Backup v2, review the following prerequisites:

Operating System:

  • Windows Server 2012 R2
  • Windows Server 2016

SQL Server version/edition:

  • SQL Server 2016 Standard
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Developer
Important

Automated Backup v2 works with SQL Server 2016. If you are using SQL Server 2014, you can use Automated Backup v1 to back up your databases. For more information, see Automated Backup for SQL Server 2014 Azure Virtual Machines.

Database configuration:

  • Target databases must use the full recovery model.
  • System databases do not have to use full recovery model. However, if you require log backups to be taken for Model or MSDB, you must use full recovery model.

For more information about the impact of the full recovery model on backups, see Backup Under the Full Recovery Model.

Azure deployment model:

  • Resource Manager
Note

Automated Backup relies on the SQL Server IaaS Agent Extension. Current SQL virtual machine gallery images add this extension by default. For more information, see SQL Server IaaS Agent Extension.

Settings

The following table describes the options that can be configured for Automated Backup v2. The actual configuration steps vary depending on whether you use the Azure portal or Azure Windows PowerShell commands.

Basic Settings

Setting Range (Default) Description
Automated Backup Enable/Disable (Disabled) Enables or disables Automated Backup for an Azure VM running SQL Server 2016 Standard or Enterprise.
Retention Period 1-30 days (30 days) The number of days to retain backups.
Storage Account Azure storage account An Azure storage account to use for storing Automated Backup files in blob storage. A container is created at this location to store all backup files. The backup file naming convention includes the date, time, and machine name.
Encryption Enable/Disable (Disabled) Enables or disables encryption. When encryption is enabled, the certificates used to restore the backup are located in the specified storage account in the same automaticbackup container using the same naming convention. If the password changes, a new certificate is generated with that password, but the old certificate remains to restore prior backups.
Password Password text A password for encryption keys. This is only required if encryption is enabled. In order to restore an encrypted backup, you must have the correct password and related certificate that was used at the time the backup was taken.

Advanced Settings

Setting Range (Default) Description
System Database Backups Enable/Disable (Disabled) When enabled, this feature will also back up the system databases: Master, MSDB, and Model. For the MSDB and Model databases, verify that they are in full recovery mode if you want log backups to be taken. Log backups are never taken for Master. And no backups are taken for TempDB.
Backup Schedule Manual/Automated (Automated) By default, the backup schedule will be automatically determined based on the log growth. Manual backup schedule allows the user to specify the time window for backups. In this case, backups will only ever take place at the specified frequency and during the specified time window of a given day.
Full backup frequency Daily/Weekly Frequency of full backups. In both cases, full backups will begin during the next scheduled time window. When weekly is selected, backups could span multiple days until all databases have successfully backed up.
Full backup start time 00:00 – 23:00 (01:00) Start time of a given day during which full backups can take place.
Full backup time window 1 – 23 hours (1 hour) Duration of the time window of a given day during which full backups can take place.
Log backup frequency 5 – 60 minutes (60 minutes) Frequency of log backups.

Understanding full backup frequency

It is important to understand the difference between daily and weekly full backups. In this effort, we will walk through two example scenarios.

Scenario 1: Weekly backups

You have a SQL Server VM which contains a number of very large databases.

On Monday, you enable Automated Backup v2 with the following settings:

  • Backup schedule: Manual
  • Full backup frequency: Weekly
  • Full backup start time: 01:00
  • Full backup time window: 1 hour

This means that the next available backup window is Tuesday at 1 AM for 1 hour. At that time, Automated Backup will begin backing up your databases one at a time. In this scenario, your databases are large enough that full backups will complete for the first couple databases. However, after one hour not all of the databases have been backed up.

When this happens, Automated Backup will begin backing up the remaining databases the next day, Wednesday at 1 AM for 1 hour. If not all databases have been backed up in that time, it will try again the next day at the same time. This will continue until all databases have been successfully backed up.

Once it reaches Tuesday again, Automated Backup will begin backing up all databases once again.

This scenario shows that Automated Backup will only operate within the specified time window, and each database will be backed up once per week. This also shows that it is possible for backups to span multiple days in the case where it is not possible to complete all backups in a single day.

Scenario 2: Daily backups

You have a SQL Server VM which contains a number of very large databases.

On Monday, you enable Automated Backup v2 with the following settings:

  • Backup schedule: Manual
  • Full backup frequency: Daily
  • Full backup start time: 22:00
  • Full backup time window: 6 hours

This means that the next available backup window is Monday at 10 PM for 6 hours. At that time, Automated Backup will begin backing up your databases one at a time.

Then, on Tuesday at 10 for 6 hours, full backups of all databases will start again.

Important

When scheduling daily backups, it is recommended that you schedule a wide time window to ensure all databases can be backed up within this time. This is especially important in the case where you have a large amount of data to back up.

Configuration in the Portal

You can use the Azure portal to configure Automated Backup v2 during provisioning or for existing SQL Server 2016 VMs.

New VMs

Use the Azure portal to configure Automated Backup v2 when you create a new SQL Server 2016 Virtual Machine in the Resource Manager deployment model.

In the SQL Server settings blade, select Automated backup. The following Azure portal screenshot shows the SQL Automated Backup blade.

SQL Automated Backup configuration in Azure portal

Note

Automated Backup v2 is disabled by default.

For context, see the complete topic on provisioning a SQL Server virtual machine in Azure.

Existing VMs

For existing SQL Server virtual machines, select your SQL Server virtual machine. Then select the SQL Server configuration section of the Settings blade.

SQL Automated Backup for existing VMs

In the SQL Server configuration blade, click the Edit button in the Automated backup section.

Configure SQL Automated Backup for existing VMs

When finished, click the OK button on the bottom of the SQL Server configuration blade to save your changes.

If you are enabling Automated Backup for the first time, Azure configures the SQL Server IaaS Agent in the background. During this time, the Azure portal might not show that Automated Backup is configured. Wait several minutes for the agent to be installed, configured. After that the Azure portal will reflect the new settings.

Configuration with PowerShell

You can use PowerShell to configure Automated Backup v2. Before you begin, you must:

Install the SQL IaaS Extension

If you provisioned a SQL Server virtual machine from the Azure portal, the SQL Server IaaS Extension should already be installed. You can determine if it is installed for your VM by calling Get-AzureRmVM command and examining the Extensions property.

$vmname = "vmname"
$resourcegroupname = "resourcegroupname"

(Get-AzureRmVM -Name $vmname -ResourceGroupName $resourcegroupname).Extensions 

If the SQL Server IaaS Agent extension is installed, you should see it listed as “SqlIaaSAgent” or “SQLIaaSExtension”. ProvisioningState for the extension should also show “Succeeded”.

If it is not installed or failed to be provisioned, you can install it with the following command. In addition to the VM name and resource group, you must also specify the region ($region) that your VM is located in.

$region = “EASTUS2”
Set-AzureRmVMSqlServerExtension -VMName $vmname `
    -ResourceGroupName $resourcegroupname -Name "SQLIaasExtension" `
    -Version "1.2" -Location $region 

Verify current settings

If you enabled automated backup during provisioning, you can use PowerShell to check your current configuration. Run the Get-AzureRmVMSqlServerExtension command and examine the AutoBackupSettings property:

(Get-AzureRmVMSqlServerExtension -VMName $vmname -ResourceGroupName $resourcegroupname).AutoBackupSettings

You should get output similar to the following:

Enable                      : True
EnableEncryption            : False
RetentionPeriod             : 30
StorageUrl                  : https://test.blob.core.windows.net/
StorageAccessKey            :  
Password                    : 
BackupSystemDbs             : False
BackupScheduleType          : Manual
FullBackupFrequency         : WEEKLY
FullBackupStartTime         : 2
FullBackupWindowHours       : 2
LogBackupFrequency          : 60

If your output shows that Enable is set to False, then you have to enable automated backup. The good news is that you enable and configure Automated Backup in the same way. See the next section for this information.

Note

If you check the settings immediately after making a change, it is possible that you will get back the old configuration values. Wait a few minutes and check the settings again to make sure that your changes were applied.

Configure Automated Backup v2

You can use PowerShell to enable Automated Backup as well as to modify its configuration and behavior at any time.

First, select or create a storage account for the backup files. The following script selects a storage account or creates it if it does not exist.

$storage_accountname = “yourstorageaccount”
$storage_resourcegroupname = $resourcegroupname

$storage = Get-AzureRmStorageAccount -ResourceGroupName $resourcegroupname `
    -Name $storage_accountname -ErrorAction SilentlyContinue
If (-Not $storage)
    { $storage = New-AzureRmStorageAccount -ResourceGroupName $storage_resourcegroupname `
    -Name $storage_accountname -SkuName Standard_GRS -Location $region } 
Note

Automated Backup does not support storing backups in premium storage, but it can take backups from VM disks which use Premium Storage.

Then use the New-AzureRmVMSqlServerAutoBackupConfig command to enable and configure the Automated Backup v2 settings to store backups in the Azure storage account. In this example, the backups are set to be retained for 10 days. System database backups are enabled. Full backups are scheduled for weekly with a time window starting at 20:00 for two hours. Log backups are scheduled for every 30 minutes. The second command, Set-AzureRmVMSqlServerExtension, updates the specified Azure VM with these settings.

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -Enable `
    -RetentionPeriodInDays 10 -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname -BackupSystemDbs `
    -BackupScheduleType Manual -FullBackupFrequency Weekly `
    -FullBackupStartHour 20 -FullBackupWindowInHours 2 `
    -LogBackupFrequencyInMinutes 30 

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname 

It could take several minutes to install and configure the SQL Server IaaS Agent.

To enable encryption, modify the previous script to pass the EnableEncryption parameter along with a password (secure string) for the CertificatePassword parameter. The following script enables the Automated Backup settings in the previous example and adds encryption.

$password = "P@ssw0rd"
$encryptionpassword = $password | ConvertTo-SecureString -AsPlainText -Force  

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -Enable `
    -EnableEncryption -CertificatePassword $encryptionpassword `
    -RetentionPeriodInDays 10 -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname -BackupSystemDbs `
    -BackupScheduleType Manual -FullBackupFrequency Weekly `
    -FullBackupStartHour 20 -FullBackupWindowInHours 2 `
    -LogBackupFrequencyInMinutes 30 

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

To confirm your settings are applied, verify the Automated Backup configuration.

Disable Automated Backup

To disable Automated Backup, run the same script without the -Enable parameter to the New-AzureRmVMSqlServerAutoBackupConfig command. The absence of the -Enable parameter signals the command to disable the feature. As with installation, it can take several minutes to disable Automated Backup.

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -ResourceGroupName $storage_resourcegroupname

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

Example script

The following script provides a set of variables that you can customize to enable and configure Automated Backup for your VM. In your case, you might need to customize the script based on your requirements. For example, you would have to make changes if you wanted to disable the backup of system databases or enable encryption.

$vmname = "yourvmname"
$resourcegroupname = "vmresourcegroupname"
$region = “Azure region name such as EASTUS2”
$storage_accountname = “storageaccountname”
$storage_resourcegroupname = $resourcegroupname
$retentionperiod = 10
$backupscheduletype = "Manual"
$fullbackupfrequency = "Weekly"
$fullbackupstarthour = "20"
$fullbackupwindow = "2"
$logbackupfrequency = "30"

# ResourceGroupName is the resource group which is hosting the VM where you are deploying the SQL IaaS Extension 

Set-AzureRmVMSqlServerExtension -VMName $vmname `
    -ResourceGroupName $resourcegroupname -Name "SQLIaasExtension" `
    -Version "1.2" -Location $region

# Creates/use a storage account to store the backups

$storage = Get-AzureRmStorageAccount -ResourceGroupName $resourcegroupname `
    -Name $storage_accountname -ErrorAction SilentlyContinue
If (-Not $storage)
    { $storage = New-AzureRmStorageAccount -ResourceGroupName $storage_resourcegroupname `
    -Name $storage_accountname -SkuName Standard_GRS -Location $region }

# Configure Automated Backup settings

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -Enable `
    -RetentionPeriodInDays $retentionperiod -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname -BackupSystemDbs `
    -BackupScheduleType $backupscheduletype -FullBackupFrequency $fullbackupfrequency `
    -FullBackupStartHour $fullbackupstarthour -FullBackupWindowInHours $fullbackupwindow `
    -LogBackupFrequencyInMinutes $logbackupfrequency

# Apply the Automated Backup settings to the VM

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

Next steps

Automated Backup v2 configures Managed Backup on Azure VMs. So it is important to review the documentation for Managed Backup to understand the behavior and implications.

You can find additional backup and restore guidance for SQL Server on Azure VMs in the following topic: Backup and Restore for SQL Server in Azure Virtual Machines.

For information about other available automation tasks, see SQL Server IaaS Agent Extension.

For more information about running SQL Server on Azure VMs, see SQL Server on Azure Virtual Machines overview.