About SQL Server Backup in Azure VMs

Azure Backup offers a stream-based, specialized solution to back up SQL Server running in Azure Virtual Machines (VMs). This solution aligns with Azure Backup's benefits of zero-infrastructure backup, long-term retention, and central management. It additionally provides the following advantages specifically for SQL Server:

  • Workload aware backups that support all backup types - full, differential, and log
  • 15 minute RPO (recovery point objective) with frequent log backups
  • Point-in-time recovery up to a second
  • Individual-database-levels backup and restore

To view the backup and restore scenarios that we support today, see the support matrix. For common questions, see the frequently asked questions.

Backup process for SQL Server database

This solution uses the SQL native APIs to take backups of your SQL databases.

  • Once you specify the SQL Server VM that you want to protect and query for the databases in it, Azure Backup service installs a workload backup extension on the VM by the name AzureBackupWindowsWorkload extension.

  • This extension consists of a coordinator and a SQL plugin. While the coordinator is responsible for triggering workflows for various operations like configure backup, backup, and restore, the plugin is responsible for actual data flow.

  • To be able to discover databases on this VM, Azure Backup creates the account NT SERVICE\AzureWLBackupPluginSvc. This account is used for backup and restore and requires SQL sysadmin permissions. The NT SERVICE\AzureWLBackupPluginSvc account is a Virtual Service Account, and so doesn't require any password management. Azure Backup uses the NT AUTHORITY\SYSTEM account for database discovery/inquiry, so this account needs to be a public login on SQL. If you didn't create the SQL Server VM from Azure Marketplace, you might receive an error UserErrorSQLNoSysadminMembership. If the error message appears, follow these instructions.

  • Once you trigger configure protection on the selected databases, the backup service sets up the coordinator with the backup schedules and other policy details, which the extension caches locally on the VM.

  • At the scheduled time, the coordinator communicates with the plugin and it starts streaming the backup data from the SQL server using VDI(Virtual Device Interface).

  • The plugin sends the data directly to the Recovery Services vault, thus eliminating the need for a staging location. The Azure Backup service encrypts and stores the data in storage accounts.

  • When the data transfer is complete, coordinator confirms the commit with the backup service.

    SQL Backup architecture

Prerequisites for SQL Server backup

Before you start the SQL Server backup, review the following prerequisites:

  1. Make sure you have a SQL Server instance running in Azure. You can quickly create a SQL Server instance in the marketplace.
  2. Review the feature considerations and scenario support.
  3. Review common questions about this scenario.

Set VM permissions

When you run discovery on a SQL Server, Azure Backup performs the following actions:

  • Adds the AzureBackupWindowsWorkload extension.
  • Creates an NT SERVICE\AzureWLBackupPluginSvc account to discover databases on the virtual machine. This account is used for a backup and restore and requires SQL sysadmin permissions.
  • Discovers databases that are running on a VM, Azure Backup uses the NT AUTHORITY\SYSTEM account. This account must be a public sign-in on SQL.

If you didn't create the SQL Server VM in Azure Marketplace or if you're on SQL 2008 or 2008 R2, you might receive a UserErrorSQLNoSysadminMembership error.

For giving permissions to SQL 2008 and 2008 R2 running on Windows 2008 R2, see this section.

For all other versions, assign the permissions using the following steps:

  1. Use an account with SQL Server sysadmin permissions to sign in to SQL Server Management Studio (SSMS). Unless you need special permissions, Windows authentication should work.

  2. On the SQL Server, open the Security/Logins folder.

    Open the Security/Logins folder to see accounts

  3. Right-click the Logins folder and select New Login. In Login - New, select Search.

    In the Login - New dialog box, select Search

  4. The Windows virtual service account NT SERVICE\AzureWLBackupPluginSvc was created during the virtual machine registration and SQL discovery phase. Enter the account name as shown in Enter the object name to select. Select Check Names to resolve the name. Select OK.

    Select Check Names to resolve the unknown service name

  5. On Server Roles, make sure the sysadmin role is selected. Select OK. The required permissions should now exist.

    Make sure the sysadmin server role is selected

    If the SQL Server instance is part of an Always-On Availability Group (AG), ensure that the NT AUTHORITY\SYSTEM account has the VIEW SERVER STATE permission enabled.

    Screenshot shows how to check permission on a SQL server instance selected for backup.

  6. Now associate the database with the Recovery Services vault. In the Azure portal, in the Protected Servers list, right-click the server that's in an error state > Rediscover DBs.

    Verify the server has appropriate permissions

  7. Check progress in the Notifications area. When the selected databases are found, a success message appears.

    Deployment success message

Note

If your SQL Server has multiple instances of SQL Server installed, then you must add sysadmin permission for NT Service\AzureWLBackupPluginSvc account to all SQL instances.

Assign SQL sysadmin permissions for SQL 2008 and SQL 2008 R2

To add NT AUTHORITY\SYSTEM and NT Service\AzureWLBackupPluginSvc logins to the SQL Server Instance, follow these steps:

  1. Go to the SQL Server Instance in the Object explorer.

  2. Go to Security > Logins.

  3. Right-click the Logins and select New Login New Login using SSMS

  4. Go to the General tab and enter NT AUTHORITY\SYSTEM as the Login Name.

    Login name for SSMS

  5. Go to the Server Roles tab and choose public and sysadmin roles.

    Choosing roles in SSMS

  6. Go to Status, select Grant for the Permission to connect to database engine, and then select Enabled for Login.

    Grant permissions in SSMS

  7. Select OK.

  8. To add the NT Service\AzureWLBackupPluginSvc login to the SQL Server instance, repeat steps 1-7.

    If the login already exists, make sure it has the sysadmin server role and under Status it has the Grant option for the Permission to connect to database engine and Login as Enabled.

  9. After granting permission, Rediscover DBs in the Azure portal by going to the Recovery Services vault > Manage > Backup Infrastructure > Workload in Azure VM.

    Rediscover DBs in Azure portal

Alternatively, you can automate the permission assignment by running the following cmdlets in admin mode.

Note

The instance name is set to MSSQLSERVER by default. Change the instance name argument in script if needed.

param(
    [Parameter(Mandatory=$false)]
    [string] $InstanceName = "MSSQLSERVER"
)
if ($InstanceName -eq "MSSQLSERVER")
{
    $fullInstance = $env:COMPUTERNAME   # In case it's the default SQL Server Instance
}
else
{
    $fullInstance = $env:COMPUTERNAME + "\" + $InstanceName   # In case of named instance
}
try
{
    sqlcmd.exe -S $fullInstance -Q "sp_addsrvrolemember 'NT Service\AzureWLBackupPluginSvc', 'sysadmin'" # Adds login with sysadmin permission if already not available
}
catch
{
    Write-Host "An error occurred:"
    Write-Host $_.Exception|format-list -force
}
try
{
    sqlcmd.exe -S $fullInstance -Q "sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin'" # Adds login with sysadmin permission if already not available
}
catch
{
    Write-Host "An error occurred:"
    Write-Host $_.Exception|format-list -force
}

Next steps

Related content

  • Last updated on