有关备份 Azure VM 上运行的 SQL Server 数据库的常见问题解答FAQ about SQL Server databases that are running on an Azure VM backup

本文将会解答有关备份 Azure 虚拟机 (VM) 上运行的 SQL Server 数据库以及使用 Azure 备份服务的常见问题。This article answers common questions about backing up SQL Server databases that run on Azure virtual machines (VMs) and use the Azure Backup service.

是否可以对同一台计算机上的 IaaS VM 和 SQL Server 使用 Azure 备份?Can I use Azure backup for IaaS VM as well as SQL Server on the same machine?

是,可以同时在同一 VM 上进行 VM 备份和 SQL 备份。Yes, you can have both VM backup and SQL backup on the same VM. 对于这种情况,在内部,我们将在 VM 上触发仅限复制的完整备份,以避免截断日志。In this case, we internally trigger copy-only full backup on the VM to not truncate the logs.

解决方案是重试还是自动修复备份?Does the solution retry or auto-heal the backups?

在某些情况下,Azure 备份服务会触发补救备份。Under some circumstances, the Azure Backup service triggers remedial backups. 如果存在下述六种条件中的任何一种,可进行自动修复:Auto-heal can happen for any of the six conditions mentioned below:

  • 如果日志备份或差异备份因 LSN 验证错误而失败,则下一次日志备份或差异备份将转换为完整备份。If log or differential backup fails due to LSN Validation Error, next log or differential backup is instead converted to a full backup.
  • 如果在执行日志备份或差异备份之前未执行完整备份,该日志备份或差异备份将转换为完整备份。If no full backup has happened before a log or differential backup, that log or differential backup is instead converted to a full backup.
  • 如果最新完整备份的时间点超过 15 天,则下一次日志备份或差异备份将转换为完整备份。If the latest full backup's point-in-time is older than 15 days, the next log or differential backup is instead converted to a full backup.
  • 由于扩展升级而取消的所有备份作业将在升级完成后重新触发,并且扩展将会启动。All the backup jobs that get canceled due to an extension upgrade are re-triggered after the upgrade is completed and the extension is started.
  • 如果在还原期间选择覆盖数据库,则下一次日志/差异备份将会失败,并改为触发完整备份。If you choose to overwrite the database during Restore, the next log/differential backup fails and a full backup is triggered instead.
  • 如果由于数据库恢复模式发生更改而需要使用完整备份来重置日志链,则会在下一个计划时间自动触发完整备份。In cases where a full backup is required to reset the log chains due to change in database recovery model, a full gets triggered automatically on the next schedule.

默认已为所有用户启用自动修复功能;但是,如果你要禁用它,请执行以下操作:Auto-heal as a capability is enabled for all user by default; However in case you choose to opt-out of it, then perform the below:

  • 在 SQL Server 实例上的 C:\Program Files\Azure Workload Backup\bin 文件夹中,创建或编辑 ExtensionSettingsOverrides.json 文件。On the SQL Server instance, in the C:\Program Files\Azure Workload Backup\bin folder, create or edit the ExtensionSettingsOverrides.json file.
  • ExtensionSettingsOverrides.json 中,设置 {"EnableAutoHealer": false}In the ExtensionSettingsOverrides.json, set {"EnableAutoHealer": false}.
  • 保存更改并关闭该文件。Save your changes and close the file.
  • 在 SQL Server 实例上打开“管理任务”,然后重启 AzureWLBackupCoordinatorSvc 服务。 On the SQL Server instance, open Task Manage and then restart the AzureWLBackupCoordinatorSvc service.

是否可以控制 SQL Server 上运行的并发备份数?Can I control as to how many concurrent backups run on the SQL server?

是的。Yes. 可以限制备份策略的运行速率,以尽量减少对 SQL Server 实例的影响。You can throttle the rate at which the backup policy runs to minimize the impact on a SQL Server instance. 若要更改设置,请执行以下操作:To change the setting:

  1. 在 SQL Server 实例上的 C:\Program Files\Azure Workload Backup\bin 文件夹中,创建 ExtensionSettingsOverrides.json 文件。On the SQL Server instance, in the C:\Program Files\Azure Workload Backup\bin folder, create the ExtensionSettingsOverrides.json file.

  2. ExtensionSettingsOverrides.json 文件中,将 DefaultBackupTasksThreshold 设置更改为较小的值(例如 5)。In the ExtensionSettingsOverrides.json file, change the DefaultBackupTasksThreshold setting to a lower value (for example, 5).
    {"DefaultBackupTasksThreshold": 5}

  3. 保存更改并关闭该文件。Save your changes and close the file.

  4. 在 SQL Server 实例上,打开“任务管理器”。 On the SQL Server instance, open Task Manager. 重启 AzureWLBackupCoordinatorSvc 服务。Restart the AzureWLBackupCoordinatorSvc service.

    尽管在备份应用程序消耗大量资源时此方法有所帮助,但使用 SQL Server Resource Governor 可通过更常规的方式来指定传入应用程序请求可以使用的 CPU、物理 IO 和内存量限制。While this method helps if the backup application is consuming a large quantity of resources, SQL Server Resource Governor is a more generic way to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

Note

在 UX 中,仍可随时继续计划任意数量的备份,但是,这些备份将按某个滑动窗口进行处理,例如,在上述示例中,滑动窗口为 5。In the UX you can still go ahead and schedule as many backups at any given time, however they will processed in a sliding window of say, 5, as per the above example.

是否可以从次要副本运行完整备份?Can I run a full backup from a secondary replica?

根据 SQL 限制,只能针对次要副本运行“仅限复制的完整备份”,而不允许“完整备份”。According to SQL limitations, you can run Copy Only Full backup on Secondary Replica; however Full backup is not allowed.

是否可以保护本地的可用性组?Can I protect availability groups on-premises?

否。No. Azure 备份可以保护 Azure 中运行的 SQL Server 数据库。Azure Backup protects SQL Server databases running in Azure. 如果可用性组 (AG) 分散在 Azure 与本地计算机之间,则仅当主要副本在 Azure 中运行时,才可以保护 AG。If an availability group (AG) is spread between Azure and on-premises machines, the AG can be protected only if the primary replica is running in Azure. 此外,Azure 备份只能保护恢复服务保管库所在的同一 Azure 区域中运行的节点。Also, Azure Backup protects only the nodes that run in the same Azure region as the Recovery Services vault.

是否可跨区域保护可用性组?Can I protect availability groups across regions?

Azure 备份恢复服务保管库可以检测并保护保管库所在的同一区域中的所有节点。The Azure Backup Recovery Services vault can detect and protect all nodes that are in the same region as the vault. 如果 SQL Server Always On 可用性组跨多个 Azure 区域,请从包含主要节点的区域设置备份。If your SQL Server Always On availability group spans multiple Azure regions, set up the backup from the region that has the primary node. Azure 备份可根据备份优先顺序检测并保护可用性组中的所有数据库。Azure Backup can detect and protect all databases in the availability group according to your backup preference. 如果不符合备份优先顺序,备份将会失败,并出现失败警报。When your backup preference isn't met, backups fail and you get the failure alert.

成功的备份作业是否会创建警报?Do successful backup jobs create alerts?

否。No. 成功的备份作业不会生成警报。Successful backup jobs don't generate alerts. 仅针对失败的备份作业发送警报。Alerts are sent only for backup jobs that fail. 此文介绍了门户警报的详细行为。Detailed behavior for portal alerts is documented here. 但是,如果你希望同时收到已成功作业的警报,可以使用 Azure Monitor 进行监视However, in case you are interested do have alerts even for successful jobs, you can use Monitoring using Azure Monitor.

“备份作业”菜单中是否会显示计划的备份作业?Can I see scheduled backup jobs in the Backup Jobs menu?

“备份作业”菜单只显示临时备份作业。 The Backup Job menu will only show ad-hoc backup jobs. 对于计划的作业,请使用 Azure Monitor 进行监视For scheduled job use Monitoring using Azure Monitor.

未来的数据库会自动添加备份吗?Are future databases automatically added for backup?

是,可以通过自动保护实现此功能。Yes, you can achieve this capability with auto-protection.

如果从自动保护的实例中删除数据库,备份会发生什么情况?If I delete a database from an autoprotected instance, what will happen to the backups?

如果从自动保护的实例中删除某个数据库,仍会尝试数据库备份。If a database is dropped from an autoprotected instance, the database backups are still attempted. 这意味着,已删除的数据库会开始在“备份项”下面显示为不正常状态,但它仍受保护。 This implies that the deleted database begins to show up as unhealthy under Backup Items and is still protected.

停止保护此数据库的正确方法是针对此数据库执行“停止备份”并删除数据The correct way to stop protecting this database is to do Stop Backup with delete data on this database.

如果停止受保护数据库的备份操作,将出现怎样的备份行为?If I do stop backup operation of an autoprotected database what will be its behavior?

如果停止备份但保留数据,则将来的备份不会发生,现有的恢复点将保留不变。If you do stop backup with retain data, no future backups will take place and the existing recovery points will remain intact. 数据库仍被视为受保护,并显示在“备份项”下。 The database will still be considered as protected and be shown under the Backup items.

如果停止备份并删除数据,则将来的备份不会发生,现有的恢复点也会一并删除。If you do stop backup with delete data, no future backups will take place and the existing recovery points will also be deleted. 该数据库被视为不受保护,并显示在“配置备份”中的实例下。The database will be considered un-protected and be shown under the instance in the Configure Backup. 但是,与其他可以手动选择或者可以自动保护的受保护数据库不同,此数据库将会灰显,并且不可选择。However, unlike other up-protected databases that can be selected manually or that can get autoprotected, this database appears greyed out and can’t be selected. 重新保护此数据库的唯一方法是对该实例禁用自动保护。The only way to re-protect this database is to disable auto-protection on the instance. 接下来可以选择此数据库并对其配置保护,或者对该实例重新启用自动保护。You can now select this database and configure protection on it or re-enable auto-protection on the instance again.

如果在保护数据库后更改其名称,会出现怎样的行为?If I change the name of the database after it has been protected, what will be the behavior?

已重命名的数据库被视为新数据库。A renamed database is treated as a new database. 因此,服务将此情况视为找不到数据库,同时会使备份失败。Hence, the service will treat this situation as if the database were not found and with fail the backups.

可以选择现在已重命名的数据库并对其配置保护。You can select the database, which is now renamed and configure protection on it. 如果对实例启用了自动保护,则会自动检测并保护已重命名的数据库。In case the auto-protection is enabled on the instance, the renamed database will be automatically detected and protected.

为什么不显示自动保护实例的已添加数据库?Why can’t I see an added database for an autoprotected instance?

添加到自动保护实例的数据库可能不会立即显示在“受保护的项”下。A database that you add to an autoprotected instance might not immediately appear under protected items. 这是因为,发现功能通常每隔 8 小时运行一次。This is because the discovery typically runs every 8 hours. 但是,如果按下图所示选择“重新发现数据库”来手动运行发现,则可以立即发现并保护新的数据库: However, you can discover and protect new databases immediately if you manually run a discovery by selecting Rediscover DBs, as shown in the following image:

手动发现新添加的数据库

后续步骤Next steps

了解如何备份 Azure VM 上运行的 SQL Server 数据库Learn how to back up a SQL Server database that's running on an Azure VM.