Automated Backups of SQL Server Databases in Azure IaaS VMs

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 $storageAccountName).Primary
$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 | 
	Update-AzureVM 

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.

Regardless of the approach, you should note that this functionality is limited to SQL Server 2014 Standard and Enterprise editions, and in the context of Azure IaaS, it requires virtual machines running Windows Server 2012 or Windows Server 2012 R2 operating system. In addition, as we already pointed out, the maximum database size is limited to 1 TB and the retention period cannot exceed 30 days. You also do not have the ability to dictate how often backups take place (beyond the controls offered by the system stored procedures), since this is determined based on the volume and type of database level changes (such as a database creation) as well as the growth rate and size of transaction logs (in addition to abnormal conditions such as broken log chain), although there are guaranteed minimum frequency thresholds for both full and transaction log backups (equal to one week and two hours, respectively). For the comprehensive list of the factors affecting the actual backup schedule, refer to MSDN documentation. Finally, keep in mind that backups are restricted to user databases only, which, additionally, must be configured with Full or Bulk-logged recovery model.

A restore of any backed up database can be done directly from Object Explorer in SQL Server Management Studio, by selecting the Restore item from the Tasks submenu of its context sensitive menu. At that point, you will be presented with the Connect to Windows Azure Storage dialog box, where you will need to provide the name of the Azure storage account where the backups reside, the account key, which you can retrieve ether from the Azure Preview Portal or by using (Get-AzureStorageKey -StorageAccountName $storageAccountName).Primary PowerShell code, as well as the SQL Credential (with AutoBackup_Credential being your default and likely choice). That in turn will trigger a display of the Restore Database dialog box, from which you can specify the destination database along with the backup timeline that determines recovery point objective.

This concludes our overview of automated backups of SQL Server 2014 hosted on Azure IaaS virtual machines. In our upcoming articles of this series, we will review other methods that simplify common database management and maintenance tasks.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles