You can backup SQL Server databases and instances running inside Azure VM using Azure Backup. The solution leverages the SQL native APIs to take backup of your SQL databases.
When you specify the SQL Server VM that you want to protect and query for the databases in it, the Azure Backup service will install a backup extension on the VM named AzureBackupWindowsWorkload. The extension is a sort of set of plugins, that controls the triggers of the worlflows and the operations of the backup and restore itself.
To be able to discover databases on a VM, the Azure Backup creates an account called NT SERVICE\AzureWLBackupPluginSvc. This account is used for backup and restore and requires SQL sysadmin permissions. If you did not install SQL Server from the marketplace, you will need to add the sysadmin role to the user account, from SQL Management studio -> Security -> Logins. If you do not add the account, you will get an error UserErrorSQLNoSysadminMembership when you discover databases from VM’s. So make sure to add the sysadmin role to the service account before adding server to the backup policy. More of this error can be seen here.
To create an SQL Server in Azure VM backup policy, head to your Recovery Service Vault and select Backup
In the backup goal, select Azure as the place where your workloads are running and SQL Server in Azure VM to what to backup.
In Backup Goal -> Discover DBs in VMs, select Start Discovery to search for unprotected VMs in the subscription. It can take a while, depending on the number of unprotected virtual machines in the subscription.
In the VM list, select the VM running the SQL Server database -> Discover DBs.
Azure Backup discovers all SQL Server databases on the VM. During discovery the following occurs in the background:
- Azure Backup register the VM with the vault for workload backup. All databases on the registered VM can only be backed up to this vault.
- Azure Backup installs the AzureBackupWindowsWorkload extension on the VM. No agent is installed on the 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 needs SQL sysadmin permissions as mentioned earlier. All SQL Server VMs created in the Azure Marketplace come with the SqlIaaSExtension installed. The AzureBackupWindowsWorkload extension uses the SQLIaaSExtension to automatically get the required permissions so in those cases you do not have to add the permissions manually.
When DB discovery is finished, you’ll see the outcome. If you did not add the required permissions, you’ll also see the error message stating that you need to add the correct permissions for the service account.
Next step is to configure the backup itself
Click Configure Backup, the Select items to backup blade appears.
Select all the databases you want to protect > OK.
The maximum amount of databases in one backup job is limited to 50 to keep the backup loads optimized. Select Autoprotect, if you want the backup to automatically add the new databases created in the future. If its not turned on, you will need to manually add all the new databases in the instance. You can read more about autoprotect from here.
Click OK to open the Backup policy blade.
Select the desired backup policy, or create a new one. When you’re ready, enable the backup.
RSS - Posts