关于 Azure VM 中的 SQL Server 备份About SQL Server Backup in Azure VMs

Azure 备份提供了一个基于流的专业解决方案,用于备份在 Azure VM 中运行的 SQL Server。Azure Backup offers a stream-based, specialized solution to back up SQL Server running in Azure VMs. 此解决方案考虑到了 Azure 备份的零基础结构备份、长期保留和集中管理的优点。This solution aligns with Azure Backup's benefits of zero-infrastructure backup, long-term retention, and central management. 它还特别为 SQL Server 提供了以下优势:It additionally provides the following advantages specifically for SQL Server:

  1. 工作负荷感知备份,支持所有备份类型(完整备份、差异备份和日志备份)Workload aware backups that support all backup types - full, differential, and log
  2. 15 分钟 RPO(恢复点目标),频繁进行日志备份15-min RPO (recovery point objective) with frequent log backups
  3. 最多一秒的时点恢复Point-in-time recovery up to a second
  4. 单数据库级别的备份和还原Individual database level backup and restore

若要查看我们目前支持的备份和还原方案,请参阅支持矩阵To view the backup and restore scenarios that we support today, refer to the support matrix.

备份过程Backup process

此解决方案利用 SQL 本机 API 来备份 SQL 数据库。This solution leverages the SQL native APIs to take backups of your SQL databases.

  • 指定要保护的 SQL Server VM 并查询其中的数据库后,Azure 备份服务将在此 VM 上以 AzureBackupWindowsWorkload 扩展名安装工作负荷备份扩展。Once you specify the SQL Server VM that you want to protect and query for the databases in it, Azure Backup service will install a workload backup extension on the VM by the name AzureBackupWindowsWorkload extension.

  • 此扩展包含协调器和 SQL 插件。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.

  • 为了能够发现此 VM 上的数据库,Azure 备份将创建帐户 NT SERVICE\AzureWLBackupPluginSvcTo be able to discover databases on this VM, Azure Backup creates the account NT SERVICE\AzureWLBackupPluginSvc. 此帐户用于备份和还原,需要拥有 SQL sysadmin 权限。This account is used for backup and restore and requires SQL sysadmin permissions. NT SERVICE\AzureWLBackupPluginSvc 帐户是虚拟服务帐户,因此不需要任何密码管理。The NT SERVICE\AzureWLBackupPluginSvc account is a Virtual Service Account, and therefore does not require any password management. Azure 备份利用 NT AUTHORITY\SYSTEM 帐户进行数据库发现/查询,因此此帐户需是 SQL 上的公共登录名。Azure Backup leverages the NT AUTHORITY\SYSTEM account for database discovery/inquiry, so this account needs to be a public login on SQL. 如果 SQL Server VM 不是从 Azure 市场创建的,你可能会收到错误 UserErrorSQLNoSysadminMembershipIf you didn't create the SQL Server VM from the Azure Marketplace, you might receive an error UserErrorSQLNoSysadminMembership. 如果发生此错误,请遵照这些说明予以解决。If this occurs follow these instructions.

  • 在所选数据库上触发配置保护后,备份服务将使用备份计划和其他策略详细信息设置协调器,扩展将这些详细信息本地缓存在 VM 上。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.

  • 在计划的时间,协调器与插件通信,并开始使用 VDI 从 SQL 服务器流式处理备份数据。At the scheduled time, the coordinator communicates with the plugin and it starts streaming the backup data from the SQL server using VDI.

  • 插件将数据直接发送到恢复服务保管库,因此不需要暂存位置。The plugin sends the data directly to the recovery services vault, thus eliminating the need for a staging location. Azure 备份服务在存储帐户中加密和存储数据。The data is encrypted and stored by the Azure Backup service in storage accounts.

  • 数据传输完成后,协调器通过备份服务确认提交。When the data transfer is complete, coordinator confirms the commit with the backup service.

    SQL 备份体系结构

开始之前Before you start

在开始之前,请验证以下条件:Before you start, verify the below:

  1. 确保有一个 SQL Server 实例在 Azure 中运行。Make sure you have a SQL Server instance running in Azure. 可以在市场中快速创建 SQL Server 实例You can quickly create a SQL Server instance in the marketplace.
  2. 查看功能注意事项方案支持Review the feature consideration and scenario support.
  3. 查看有关此方案的常见问题解答Review common questions about this scenario.

设置 VM 权限Set VM permissions

当你在 SQL Server 上运行发现时,Azure 备份会执行以下操作:When you run discovery on a SQL Server, Azure Backup does the following:

  • 添加 AzureBackupWindowsWorkload 扩展。Adds the AzureBackupWindowsWorkload extension.
  • 创建 NT SERVICE\AzureWLBackupPluginSvc 帐户,以发现虚拟机上的数据库。Creates an NT SERVICE\AzureWLBackupPluginSvc account to discover databases on the virtual machine. 此帐户用于备份和还原,需要拥有 SQL sysadmin 权限。This account is used for a backup and restore and requires SQL sysadmin permissions.
  • Azure 备份使用 NT AUTHORITY\SYSTEM 帐户来发现 VM 上运行的数据库。Discovers databases that are running on a VM, Azure Backup uses the NT AUTHORITY\SYSTEM account. 此帐户必须是 SQL 上的公共登录名。This account must be a public sign-in on SQL.

如果你未在 Azure 市场中创建 SQL Server VM,或者在 SQL 2008 和 2008 R2 上操作,可能会收到 UserErrorSQLNoSysadminMembership 错误。If you didn't create the SQL Server VM in the Azure Marketplace or if you are on SQL 2008 and 2008 R2, you might receive a UserErrorSQLNoSysadminMembership error.

有关在使用 Windows 2008 R2 上运行的 SQL 20082008 R2 时如何授予权限,请参阅此文For giving permissions in case of SQL 2008 and 2008 R2 running on Windows 2008 R2, refer here.

对于所有其他版本,可使用以下步骤解决权限问题:For all other versions, fix permissions with the following steps:

  1. 使用拥有 SQL Server sysadmin 权限的帐户登录到 SQL Server Management Studio (SSMS)。Use an account with SQL Server sysadmin permissions to sign in to SQL Server Management Studio (SSMS). 除非需要特殊权限,否则 Windows 身份验证应该正常运行。Unless you need special permissions, Windows authentication should work.

  2. 在 SQL 服务器上,打开“安全/登录名”文件夹。On the SQL Server, open the Security/Logins folder.

    打开“安全/登录名”文件夹以查看帐户

  3. 右键单击“登录名”文件夹并选择“新建登录名”。 Right-click the Logins folder and select New Login. 在“登录名 - 新建”中,选择“搜索”。 In Login - New, select Search.

    在“登录名 - 新建”对话框中选择“搜索”

  4. 在虚拟机注册和 SQL 发现阶段已创建 Windows 虚拟服务帐户 NT SSERVICE\AzureWLBackupPluginSvc。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. 单击 “确定”Click OK.

    选择“检查名称”以解析未知的服务名称

  5. 在“服务器角色”中,确保“sysadmin”角色已选中。 In Server Roles, make sure the sysadmin role is selected. 单击 “确定”Click OK. 现在,所需的权限应会存在。The required permissions should now exist.

    确保 sysadmin 服务器角色已选中

  6. 现在,将数据库与恢复服务保管库相关联。Now associate the database with the Recovery Services vault. 在 Azure 门户上的“受保护的服务器”列表中,右键单击处于错误状态的服务器,并选择“重新发现数据库” 。In the Azure portal, in the Protected Servers list, right-click the server that's in an error state > Rediscover DBs.

    验证服务器是否拥有相应的权限

  7. 在“通知”区域查看进度。Check progress in the Notifications area. 找到选定的数据库后,会显示一条成功消息。When the selected databases are found, a success message appears.

    部署成功消息

备注

如果 SQL Server 安装了多个 SQL Server 实例,则必须将 NT Service\AzureWLBackupPluginSvc 帐户的 sysadmin 权限添加到所有 SQL 实例。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.

为 SQL sysadmin 授予 SQL 2008 和 SQL 2008 R2 的权限Give SQL sysadmin permissions for SQL 2008 and SQL 2008 R2

NT AUTHORITY\SYSTEMNT Service\AzureWLBackupPluginSvc 登录名添加到 SQL Server 实例:Add NT AUTHORITY\SYSTEM and NT Service\AzureWLBackupPluginSvc logins to the SQL Server Instance:

  1. 在对象资源管理器中转到该 SQL Server 实例。Go the SQL Server Instance in the Object explorer.

  2. 导航到“安全性”->“登录名”Navigate to Security -> Logins

  3. 右键单击“登录名”,然后单击“新建登录名...”Right click on the Logins and click New Login…

    使用 SSMS 的新登录名

  4. 转到“常规”选项卡并输入 NT AUTHORITY\SYSTEM 作为登录名。Go to the General tab and enter NT AUTHORITY\SYSTEM as the Login Name.

    SSMS 的登录名

  5. 转到“服务器角色”,选择“public”和“sysadmin”角色。 Go to Server Roles and choose public and sysadmin roles.

    在 SSMS 中选择角色

  6. 转到“状态”。Go to Status. 授予连接到数据库引擎的权限,并将“登录名”设置为“已启用”。Grant the Permission to connect to database engine and Login as Enabled.

    在 SSMS 中授予权限

  7. 单击“确定”。Click OK.

  8. 重复相同的步骤序列(上述步骤 1-7),将 NT Service\AzureWLBackupPluginSvc 登录名添加到 SQL Server 实例。Repeat the same sequence of steps (1-7 above) to add NT Service\AzureWLBackupPluginSvc login to the SQL Server instance. 如果该登录名已存在,请确保它具有 sysadmin 服务器角色并处于这种状态:已授予连接到数据库引擎的权限,且“登录名”设置为“已启用”。If the login already exists, make sure it has the sysadmin server role and under Status it has Grant the Permission to connect to database engine and Login as Enabled.

  9. 授予权限后,在门户中重新发现数据库:“保管库”->“备份基础结构”->“Azure VM 中的工作负荷”: After granting permission, Rediscover DBs in the portal: Vault -> Backup Infrastructure -> Workload in Azure VM:

    在 Azure 门户中重新发现数据库

或者,可以在管理员模式下运行以下 PowerShell 命令,来自动授予权限。Alternatively, you can automate giving the permissions by running the following PowerShell commands in admin mode. 实例名称默认设置为 MSSQLSERVER。The instance name is set to MSSQLSERVER by default. 根据需要更改脚本中的实例名称参数:Change the instance name argument in script if need be:

param(
    [Parameter(Mandatory=$false)]
    [string] $InstanceName = "MSSQLSERVER"
)
if ($InstanceName -eq "MSSQLSERVER")
{
    $fullInstance = $env:COMPUTERNAME   # In case it is 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

  • 了解有关备份 SQL Server 数据库的信息。Learn about backing up SQL Server databases.
  • 了解如何还原已备份的 SQL Server 数据库。Learn about restoring backed up SQL Server databases.
  • 了解如何管理已备份的 SQL Server 数据库。Learn about managing backed up SQL Server databases.