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

SQL Server 数据库属于关键工作负荷,要求较低的恢复点目标 (RPO) 和长期保留。SQL Server databases are critical workloads that require a low recovery point objective (RPO) and long-term retention. 可以使用 Azure 备份来备份 Azure VM 上运行的 SQL Server 数据库。You can back up SQL Server databases running on Azure VMs using Azure Backup.

备份过程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.

方案支持Scenario support

支持Support 详细信息Details
支持的部署Supported deployments 支持 SQL 市场 Azure VM 和非市场(手动安装的 SQL Server)VM。SQL Marketplace Azure VMs and non-Marketplace (SQL Server manually installed) VMs are supported.
支持的地理区域Supported geos China中国
受支持的操作系统Supported operating systems Windows Server 2016、Windows Server 2012 R2、Windows Server 2012Windows Server 2016, Windows Server 2012 R2, Windows Server 2012

目前不支持 Linux。Linux isn't currently supported.
支持的 SQL Server 版本Supported SQL Server versions SQL Server 2017(此处详述)、SQL Server 2016 和 SP(此处详述)、SQL Server 2014、SQL Server 2012。SQL Server 2017 as detailed here, SQL Server 2016 and SPs as detailed here, SQL Server 2014, SQL Server 2012.

Enterprise、Standard、Web、Developer、Express。Enterprise, Standard, Web, Developer, Express.
支持的 .NET 版本Supported .NET versions 安装在 VM 上的 .NET Framework 4.5.2 及更高版本.NET Framework 4.5.2 and above installed on the VM

功能注意事项和限制Feature consideration and limitations

  • SQL Server 备份可配置在 Azure 门户或 PowerShell 中 。SQL Server backup can be configured in the Azure portal or PowerShell. 我们不支持 CLI。We do not support CLI.
  • 此解决方案在 Azure 资源管理器 VM 和经典 VM 这两种部署上均受支持。The solution is supported on both kinds of deployments - Azure Resource Manager VMs and classic VMs.
  • 运行 SQL Server 的 VM 需要建立 Internet 连接才能访问 Azure 公共 IP 地址。VM running SQL Server requires internet connectivity to access Azure public IP addresses.
  • 不支持 SQL Server 故障转移群集实例 (FCI) 和 SQL Server Always on 故障转移群集实例。SQL Server Failover Cluster Instance (FCI) and SQL Server Always on Failover Cluster Instance are not supported.
  • 不支持对镜像数据库和数据库快照执行备份和还原操作。Back up and restore operations for mirror databases and database snapshots aren't supported.
  • 使用多个备份解决方案来备份独立的 SQL Server 实例或 SQL Always On 可用性组可能导致备份失败,请避免执行此操作。Using more than one backup solutions to back up your standalone SQL Server instance or SQL Always on availability group may lead to backup failure; refrain from doing so.
  • 如果通过相同或不同的解决方案单独备份可用性组的两个节点,可能也会导致备份失败。Backing up two nodes of an availability group individually with same or different solutions, may also lead to backup failure.
  • Azure 备份支持只读数据库的仅完整备份和仅复制完整备份类型 Azure Backup supports only Full and Copy-only Full backup types for Read-only databases
  • 无法保护包含大量文件的数据库。Databases with large number of files can't be protected. 支持的最大文件数约为 1000 。The maximum number of files that is supported is ~1000.
  • 在一个保管库中最多可以备份约 2000 个 SQL Server 数据库 。You can back up to ~2000 SQL Server databases in a vault. 如果有大量数据库,可创建多个保管库。You can create multiple vaults in case you have a greater number of databases.
  • 一次最多可配置 50 个数据库的备份;此限制有助于优化备份负载 。You can configure backup for up to 50 databases in one go; this restriction helps optimize backup loads.
  • 我们支持最高 2 TB 大小的数据库;对于超过此大小的数据库,可能会出现性能问题 。We support databases up to 2 TB in size; for sizes greater than that, performance issues may come up.
  • 若要了解每个服务器可以保护多少个数据库,我们需要考虑带宽、VM 大小、备份频率、数据库大小等因素。下载资源规划器,它根据 VM 资源和备份策略提供每个服务器可以具有的大致数据库数。To have a sense of as to how many databases can be protected per server, we need to consider factors such as bandwidth, VM size, backup frequency, database size, etc. Download the resource planner that gives the approximate number of databases you can have per server based on the VM resources and the backup policy.
  • 对于可用性组,将基于几个因素从不同节点获取备份。In case of availability groups, backups are taken from the different nodes based on a few factors. 下面概述了可用性组的备份行为。The backup behavior for an availability group is summarized below.

Always On 可用性组的备份行为Back up behavior in case of Always on availability groups

建议只在 AG 的一个节点上配置备份。It is recommended that the backup is configured on only one node of an AG. 应该始终在主节点所在的区域配置备份。Backup should always be configured in the same region as the primary node. 换句话说,必须始终确保主节点存在于进行备份配置的区域。In other words, you always need the primary node to be present in the region in which you are configuring backup. 如果 AG 的所有节点位于进行备份配置的区域,则没有任何可担心的事情。If all the nodes of the AG are in the same region in which the backup is configured, there isn’t any concern.

对于跨区域 AGFor cross-region AG

  • 不管备份首选项如何,备份都不会发生在那些与备份配置不在同一区域的节点中。Regardless of the backup preference, backups won’t happen from the nodes that are not in the same region where the backup is configured. 这是因为跨区域备份不受支持。This is because the cross-region backups are not supported. 如果只有两个节点,而辅助节点位于另一区域,则在这种情况下,备份会继续在主节点中进行(除非备份首选项为“仅限辅助节点”)。If you have only two nodes and the secondary node is in the other region; in this case, the backups will continue to happen from primary node (unless your backup preference is ‘secondary only’).
  • 如果故障转移与备份配置不在同一区域进行,则已故障转移的区域中的节点上的备份会失败。If a fail-over happens to a region different than the one in which the backup is configured, backups would fail on the nodes in the failed-over region.

根据备份首选项和备份类型(完整/差异/日志/仅复制完整),从特定节点(主要/次要)获取备份。Depending on the backup preference and backups types (full/differential/log/copy-only full), backups are taken from a particular node (primary/secondary).

  • 备份首选项:主要节点Backup preference: Primary
备份类型Backup Type NodeNode
完整Full 主要Primary
差异Differential 主要Primary
日志Log 主要Primary
仅复制完整Copy-Only Full 主要Primary
  • 备份首选项:仅次要节点Backup preference: Secondary Only
备份类型Backup Type NodeNode
完整Full 主要Primary
差异Differential 主要Primary
日志Log 次要Secondary
仅复制完整Copy-Only Full 次要Secondary
  • 备份首选项:次要节点Backup preference: Secondary
备份类型Backup Type NodeNode
完整Full 主要Primary
差异Differential 主要Primary
日志Log 次要Secondary
仅复制完整Copy-Only Full 次要Secondary
  • 无备份首选项No Backup preference
备份类型Backup Type NodeNode
完整Full 主要Primary
差异Differential 主要Primary
日志Log 次要Secondary
仅复制完整Copy-Only Full 次要Secondary

设置 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.

    部署成功消息

Note

如果 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.