Edit

Azure Backup for SQL Server running in Azure VM

  • Article

Azure Backup, amongst other offerings, provides support for backing up workloads such as SQL Server running in Azure VMs. Since the SQL application is running within an Azure VM, the backup service needs permission to access the application and fetch the necessary details. To do that, Azure Backup installs the AzureBackupWindowsWorkload extension on the VM, in which the SQL Server is running, during the registration process triggered by the user.

Prerequisites

For the list of supported scenarios, refer to the supportability matrix supported by Azure Backup.

Network connectivity

Azure Backup supports NSG Tags, deploying a proxy server or listed IP ranges; for details on each of the methods, refer this article.

Extension schema

The extension schema and property values are the configuration values (runtime settings) that service is passing to CRP API. These config values are used during registration and upgrade. AzureBackupWindowsWorkload extension also uses this schema. The schema is pre-set; a new parameter can be added in the objectStr field

    "runtimeSettings": [{
    "handlerSettings": {
    "protectedSettingsCertThumbprint": "",
    "protectedSettings": {
    "objectStr": "",
    "logsBlobUri": "",
    "statusBlobUri": ""
    }
    },
    "publicSettings": {
    "locale": "en-us",
    "taskId": "1c0ae461-9d3b-418c-a505-bb31dfe2095d",
    "objectStr": "",
    "commandStartTimeUTCTicks": "636295005824665976",
    "vmType": "vmType"
    }
    }]
    }

The following JSON shows the schema for the WorkloadBackup extension.

{
  "type": "extensions",
  "name": "WorkloadBackup",
  "location":"<myLocation>",
  "properties": {
    "publisher": "Microsoft.RecoveryServices",
    "type": "AzureBackupWindowsWorkload",
    "typeHandlerVersion": "1.1",
    "autoUpgradeMinorVersion": true,
    "settings": {
      "locale":"<location>",
      "taskId":"<TaskId used by Azure Backup service to communicate with extension>",

      "objectStr": "<The configuration passed by Azure Backup service to extension>",

      "commandStartTimeUTCTicks": "<Scheduled start time of registration or upgrade task>",
      "vmType": "<Type of VM where registration got triggered Eg. Compute or ClassicCompute>"
    },
    "protectedSettings": {
      "objectStr": "<The sensitive configuration passed by Azure Backup service to extension>",
      "logsBlobUri": "<blob uri where logs of command execution by extension are written to>",
      "statusBlobUri": "<blob uri where status of the command executed by extension is written>"
    }
  }
}

Property values

Name Value/example Data type
locale en-us string
taskId "1c0ae461-9d3b-418c-a505-bb31dfe2095d" string
objectStr
(publicSettings)		 "eyJjb250YWluZXJQcm9wZXJ0aWVzIjp7IkNvbnRhaW5lcklEIjoiMzVjMjQxYTItOGRjNy00ZGE5LWI4NTMtMjdjYTJhNDZlM2ZkIiwiSWRNZ210Q29udGFpbmVySWQiOjM0NTY3ODg5LCJSZXNvdXJjZUlkIjoiMDU5NWIwOGEtYzI4Zi00ZmFlLWE5ODItOTkwOWMyMGVjNjVhIiwiU3Vic2NyaXB0aW9uSWQiOiJkNGEzOTliNy1iYjAyLTQ2MWMtODdmYS1jNTM5ODI3ZTgzNTQiLCJVbmlxdWVDb250YWluZXJOYW1lIjoiODM4MDZjODUtNTQ4OS00NmNhLWEyZTctNWMzNzNhYjg3OTcyIn0sInN0YW1wTGlzdCI6W3siU2VydmljZU5hbWUiOjUsIlNlcnZpY2VTdGFtcFVybCI6Imh0dHA6XC9cL015V0xGYWJTdmMuY29tIn1dfQ==" string
commandStartTimeUTCTicks "636967192566036845" string
vmType "microsoft.compute/virtualmachines" string
objectStr
(protectedSettings)		 "eyJjb250YWluZXJQcm9wZXJ0aWVzIjp7IkNvbnRhaW5lcklEIjoiMzVjMjQxYTItOGRjNy00ZGE5LWI4NTMtMjdjYTJhNDZlM2ZkIiwiSWRNZ210Q29udGFpbmVySWQiOjM0NTY3ODg5LCJSZXNvdXJjZUlkIjoiMDU5NWIwOGEtYzI4Zi00ZmFlLWE5ODItOTkwOWMyMGVjNjVhIiwiU3Vic2NyaXB0aW9uSWQiOiJkNGEzOTliNy1iYjAyLTQ2MWMtODdmYS1jNTM5ODI3ZTgzNTQiLCJVbmlxdWVDb250YWluZXJOYW1lIjoiODM4MDZjODUtNTQ4OS00NmNhLWEyZTctNWMzNzNhYjg3OTcyIn0sInN0YW1wTGlzdCI6W3siU2VydmljZU5hbWUiOjUsIlNlcnZpY2VTdGFtcFVybCI6Imh0dHA6XC9cL015V0xGYWJTdmMuY29tIn1dfQ==" string
logsBlobUri https://seapod01coord1exsapk732.blob.core.windows.net/bcdrextensionlogs-d45d8a1c-281e-4bc8-9d30-3b25176f68ea/sopattna-vmubuntu1404ltsc.v2.Logs.txt?sv=2014-02-14&sr=b&sig=DbwYhwfeAC5YJzISgxoKk%2FEWQq2AO1vS1E0rDW%2FlsBw%3D&st=2017-11-09T14%3A33%3A29Z&se=2017-11-09T17%3A38%3A29Z&sp=rw string
statusBlobUri https://seapod01coord1exsapk732.blob.core.windows.net/bcdrextensionlogs-d45d8a1c-281e-4bc8-9d30-3b25176f68ea/sopattna-vmubuntu1404ltsc.v2.Status.txt?sv=2014-02-14&sr=b&sig=96RZBpTKCjmV7QFeXm5IduB%2FILktwGbLwbWg6Ih96Ao%3D&st=2017-11-09T14%3A33%3A29Z&se=2017-11-09T17%3A38%3A29Z&sp=rw string

Template deployment

We recommended adding AzureBackupWindowsWorkload extension to a virtual machine is by enabling SQL Server backup on the virtual machine. This can be achieved through the Resource Manager template designed for automating backup on a SQL Server VM.

PowerShell deployment

You need to 'register' the Azure VM that contains the SQL application with a Recovery services vault. During registration, AzureBackupWindowsWorkload extension gets installed on the VM. Use Register-AzRecoveryServicesBackupContainerPS cmdlet to register the VM. Replace VMRG Name and VMName with the target resource group name and VM name.

$myVM = Get-AzVM -ResourceGroupName <VMRG Name> -Name <VMName>
Register-AzRecoveryServicesBackupContainer -ResourceId $myVM.ID -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $targetVault.ID -Force

The command will return a backup container of this resource and the status will be registered.

Next steps

  • Learn More about Azure SQL Server VM backup troubleshooting guidelines
  • Common questions about backing up SQL Server databases that run on Azure virtual machines (VMs) and that use the Azure Backup service.