DBA > Articles

Automated Backups of SQL Server Databases in Azure IaaS VMs

By: Marcin Policht
To read more DBA articles, visit http://dba.fyicenter.com/article/

Following the main theme of our earlier articles published on this forum, we will continue our quest for methods to simplify and streamline provisioning and management of Azure SQL Database and SQL Server running in Infrastructure-as-a-Service (IaaS) Azure virtual machines. For the time being, we will focus on the functionality related to common maintenance tasks, in particular database backups. The features we will present here are facilitated by Azure VM extensions (more specifically, by the extension named SQL Server IaaS Agent), which are lightweight software components running on top of the VM Agent, installed by default on any image-based virtual machine deployed in Azure. Extensions, as their name indicates, extend virtual machine-related capabilities beyond those inherent to the hosting platform. Note, however, that since they are exclusive to Azure IaaS VMs, their benefits are not applicable to Azure SQL Database.

Automated backups can be managed via the Azure Preview Portal, Windows PowerShell (by leveraging the Azure PowerShell module), and SQL Server system stored procedures. The first two of these approaches allow you to configure backups either during deployment of SQL Server image-based virtual machines or once they have been provisioned. The former one provides a friendly user interface, exposed as the Automated backup blade (accessible via SQL AUTOMATED BACKUP entry in the Optional config blade, which appears when clicking on OPTIONAL CONFIGURATION entry in the Create VM blade). From there, you can enable or disable automated backups and designate their settings, including the name of an Azure storage account where backups will be residing (by default, the backup uses the same one that hosts the VM's operating system virtual disk), specifying the retention period (between 1 and 30 days), as well as (optionally) enabling encryption, with a certificate protected by a password of your choice and stored in the same storage account in the automaticbackup container. It is worthwhile noting that you have the option to change the password (which automatically triggers generation of a new certificate) without losing access to older backups.

Interestingly, even if you decide not to enable automated backup during deployment, the newly provisioned virtual machine will still include the SQL Server IaaS Agent VM extension (as long as it is based on a SQL Server gallery image), which you can easily verify by viewing the content of its Extensions blade. This allows you to turn on backup afterwards, although its configuration is accessible in this case via the SQL Auto backup tile in the Configuration section of the VM blade. Clicking on the tile will display the same SQL Automated backup blade available during the provisioning process, exposing the same set of options that we described above.

As we mentioned earlier, an alternative approach relies on Windows PowerShell to accomplish the same objective. More specifically, the relevant functionality is implemented by the New-AzureVMSqlServerAutoBackupConfig cmdlet. Assuming that the target storage account is named sqldbbackups1 (note that this name must be globally unique in the core.window.net namespace), that our virtual machine running SQL Server default instance is named VMSQL1 and it resides in the cloud service named sqlcloudservice1 (here as well you need to comply with the global uniqueness requirement in the cloudapp.net namespace), as well as that our intention is to retain backups for 20 days and encrypt their content with a certificate protected by the 4y0uR3y3$0n1y password, then our code that configures automated backups would take the following format:

$vmName = 'VMSQL1'
$serviceName = 'sqlcloudservice1'
$storageAccountName = 'sqldbbackups1'
$password = '4y0uR3y3$0n1y'
$retentionDays = 20

$storageAccountKey = (Get-AzureStorageKey -StorageAccountName
$storageContext = New-AzureStorageContext -StorageAccountName
$storageAccountName -StorageAccountKey $storageAccountKey
$securedPassword = $password | ConvertTo-SecureString -AsPlainText -Force
$autoBackupConfig = New-AzureVMSqlServerAutoBackupConfig -StorageContext
$storageContext -Enable ` -RetentionPeriod $retentionDays -EnableEncryption -CertificatePassword $securedPassword

Get-AzureVM -ServiceName $serviceName -Name $vmName |
Set-AzureVMSqlServerExtension -AutoBackupSettings $autoBackupConfig |

Windows PowerShell will also come in handy if you have a virtual machine without VM Agent installed, which might have happened if for some reason you decided to exclude it during deployment. Since the presence of the agent is the prerequisite for SQL Server IaaS Agent extension, you will need to remediate this issue first (as long as you want to use PowerShell or Preview Portal to manage automated backups). Start by downloading the agent executable from the Microsoft Downloads site and installing it on the target virtual machine. Once this is completed, run the following script (assuming that the name of the VM and its cloud service are VMSQL2 and sqlcloudservice2 respectively) and, afterwards, execute the first script we provided (after adjusting values of relevant variables) in order to install the SQL Server IaaS Agent extension and configure automated backup parameters:

$vmName = 'VMSQL2'
$serviceName = 'sqlcloudservice2'
$vm = Get-AzureVM –ServiceName $serviceName –Name $vmName
$vm.VM.ProvisionGuestAgent = $true
Update-AzureVM –Name $vmName –VM $vm.VM –ServiceName $serviceName

Automated backups utilize the security context implemented by an autogenerated SQL Credential named AutoBackup_Credential (created automatically when the SQL Server IaaS Agent VM extension gets configured), which provides access to the Azure storage account designated to host backup files (by using the account name as its identity and the corresponding key as its password). Note that this credential can also be used to perform arbitrary backups (supplementing those carried out by the automated process against user databases or protecting system databases). Individual backups are stored as .bak or .log Azure storage page blobs (corresponding to full and transaction log backups, respectively), which, incidentally imposes the limit of 1 TB on the individual backup size.

Another option available to you that offers more flexibility and granularity in managing automated SQL Server backups to an Azure storage account (allowing you, for example, to specify individual databases to be backed up or set different retention periods for each) relies on system stored procedures defined in the msdb database of SQL Server 2014 (as described in MSDN documentation), including the following:

smart_admin.set_db_backup - allows you to enable and configure backup to an Azure storage account for an arbitrary SQL Server database.
smart_admin.set_instance_backup - enables and configures backup to an Azure storage account for all user databases for an arbitrary SQL Server instance.
smart_admin.sp_ backup_master_switch - gives you the ability to pause and resume automated backups.
smart_admin.sp_set_parameter - implements additional backup related features, such as extended events or email notifications.
smart_admin.sp_backup_on_demand - performs an ad-hoc backup to the Azure storage account without breaking the log chain.

Interestingly, this approach does not have dependency on the SQL Server IaaS Agent extension, since the functionality described above is part of the SQL Server Managed Backup to Windows Azure incorporated into the product and applicable to both on-premises and Azure IaaS VM-based deployments of SQL Server 2014. However, in order to implement it you will need to ensure that components configured automatically when relying on the VM Agent extension are already available. More specifically, you will need to designate a storage account that will host backups, obtain its access key, and create a SQL Credential to provide the corresponding security context.

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/