在 Azure Stack 上备份 SQL ServerBack up SQL Server on Azure Stack

使用本文配置 Microsoft Azure 备份服务器 (MABS) 以在 Azure Stack 上保护 SQL Server 数据库。Use this article to configure Microsoft Azure Backup Server (MABS) to protect SQL Server databases on Azure Stack.

向 Azure 备份以及从 Azure 恢复 SQL Server 数据库的管理工作涉及三个步骤:The management of SQL Server database backup to Azure and recovery from Azure involves three steps:

  1. 创建备份策略来保护 SQL Server 数据库Create a backup policy to protect SQL Server databases
  2. 创建按需备份副本Create on-demand backup copies
  3. 从磁盘和 Azure 恢复数据库Recover the database from Disks, and from Azure

先决条件和限制Prerequisites and limitations

  • 如果具有包含远程文件共享上的文件的数据库,则保护将失败,错误 ID 为 104。If you have a database with files on a remote file share, protection will fail with Error ID 104. MABS 不支持保护远程文件共享上的 SQL Server 数据。MABS doesn't support protection for SQL Server data on a remote file share.
  • MABS 无法保护远程 SMB 共享上存储的数据库。MABS can't protect databases that are stored on remote SMB shares.
  • 确保将可用性组副本配置为只读Ensure that the availability group replicas are configured as read-only.
  • 必须将系统帐户 NTAuthority\System 显式添加到 SQL Server 上的 Sysadmin 组中。You must explicitly add the system account NTAuthority\System to the Sysadmin group on SQL Server.
  • 在为部分包含的数据库执行备用位置恢复时,你必须确保目标 SQL 实例启用了包含的数据库功能。When you perform an alternate location recovery for a partially contained database, you must ensure that the target SQL instance has the Contained Databases feature enabled.
  • 在为文件流数据库执行备用位置恢复时,你必须确保目标 SQL 实例启用了文件流数据库功能。When you perform an alternate location recovery for a file stream database, you must ensure that the target SQL instance has the file stream database feature enabled.
  • 对 SQL Server AlwaysOn 的保护:Protection for SQL Server AlwaysOn:
    • 在创建保护组期间运行查询时,MABS 会检测可用性组。MABS detects Availability Groups when running inquiry at protection group creation.
    • MABS 会检测故障转移,并继续保护数据库。MABS detects a failover and continues protection of the database.
    • MABS 支持 SQL Server 实例的多站点群集配置。MABS supports multi-site cluster configurations for an instance of SQL Server.
  • 保护使用 AlwaysOn 功能的数据库时,MABS 具有以下限制:When you protect databases that use the AlwaysOn feature, MABS has the following limitations:
    • MABS 将遵循基于备份首选项在 SQL Server 中设置的可用性组的备份策略,如下所示:MABS will honor the backup policy for availability groups that's set in SQL Server based on the backup preferences, as follows:
      • 首选辅助副本 - 除了主副本是唯一在线副本的情况之外,备份应在辅助副本上进行。Prefer secondary - Backups should occur on a secondary replica except when the primary replica is the only replica online. 如果有多个次要副本可用,则将选择具有最高备份优先级的节点进行备份。If there are multiple secondary replicas available, then the node with the highest backup priority will be selected for backup. 如果只有主要副本可用,则应在主要副本上进行备份。IF only the primary replica is available, then the backup should occur on the primary replica.
      • 仅辅助副本 - 不应在主副本上执行备份。Secondary only - Backup shouldn't be performed on the primary replica. 如果主副本是唯一在线副本,则不应进行备份。If the primary replica is the only one online, the backup shouldn't occur.
      • 主副本 - 备份应始终在主副本上进行。Primary - Backups should always occur on the primary replica.
      • 任意副本 - 备份可以在可用性组中的任何可用性副本上进行。Any Replica - Backups can happen on any of the availability replicas in the availability group. 将根据每个节点的备份优先级来确定要从中备份的节点。The node to be backed up from will be based on the backup priorities for each of the nodes.
    • 注意以下事项:Note the following:
      • 可从任何可读副本(也就是主要副本、同步次要副本、异步次要副本)进行备份。Backups can happen from any readable replica - that is, primary, synchronous secondary, asynchronous secondary.
      • 如果备份中排除了任何副本,例如“排除副本”已启用或标记为不可读,则在任何选项下都不会选择该副本进行备份。If any replica is excluded from backup, for example Exclude Replica is enabled or is marked as not readable, then that replica won't be selected for backup under any of the options.
      • 如果有多个副本可用且可读,则将选择具有最高备份优先级的节点进行备份。If multiple replicas are available and readable, then the node with the highest backup priority will be selected for backup.
      • 如果备份在所选节点上失败,则备份操作将失败。If the backup fails on the selected node, then the backup operation fails.
      • 不支持恢复到原始位置。Recovery to the original location isn't supported.
  • SQL Server 2014 或更高版本备份问题:SQL Server 2014 or above backup issues:
    • SQL Server 2014 添加了一项新功能,用于为 Azure Blob 存储中的本地 SQL Server 创建数据库SQL server 2014 added a new feature to create a database for on-premises SQL Server in Azure Blob storage. MABS 不能用于保护此配置。MABS can't be used to protect this configuration.
    • SQL AlwaysOn 选项的“首选次要副本”备份首选项存在一些已知问题。There are some known issues with "Prefer secondary" backup preference for the SQL AlwaysOn option. MABS 始终从次要副本创建备份。MABS always takes a backup from secondary. 如果找不到次要副本,则备份将失败。If no secondary can be found, then the backup fails.

开始之前Before you start

安装并准备 Azure 备份服务器Install and prepare Azure Backup Server.

创建备份策略以保护要备份到 Azure 的 SQL Server 数据库Create a backup policy to protect SQL Server databases to Azure

  1. 在 Azure 备份服务器 UI 中,选择“保护”工作区。On the Azure Backup Server UI, select the Protection workspace.

  2. 选择“新建”以创建新的保护组。On the tool ribbon, select New to create a new protection group.

    创建保护组

    Azure 备份服务器将启动“保护组”向导,该向导将引导你完成创建保护组的过程。Azure Backup Server starts the Protection Group wizard, which leads you through creating a Protection Group. 选择“下一页”。Select Next.

  3. 在“选择保护组类型”屏幕上,选择“服务器”。 In the Select Protection Group Type screen, select Servers.

    选择保护组类型 -“服务器”

  4. 在“选择组成员”屏幕中,“可用成员”列表中显示了各种数据源。In the Select Group Members screen, the Available members list displays the various data sources. 选择“+”以展开文件夹并显示子文件夹。Select + to expand a folder and reveal the subfolders. 选中复选框以选择项。Select the checkbox to select an item.

    选择 SQL DB

    所有选定的成员将出现在“所选成员”列表中。All selected items appear in the Selected members list. 选择想要保护的服务器或数据库后,选择“下一步”。After selecting the servers or databases you want to protect, select Next.

  5. 在“选择数据保护方法”屏幕中,为保护组提供名称,并选中“我需要在线保护”复选框。 In the Select Data Protection Method screen, provide a name for the protection group and select the I want online Protection checkbox.

    数据保护方法 - 短期磁盘和在线 Azure

  6. 在“指定短期目标”屏幕中,提供在磁盘上创建备份点所需的输入,并选择“下一步” 。In the Specify Short-Term Goals screen, include the necessary inputs to create backup points to disk, and select Next.

    在示例中,“保持期”为 5 天,“同步频率”为 15 分钟一次,这是备份频率。In the example, Retention range is 5 days, Synchronization frequency is once every 15 minutes, which is the backup frequency. 快速完整备份”设置为“晚上 8:00”。Express Full Backup is set to 8:00 P.M.

    短期目标

    备注

    在显示的示例中,每天晚上 8:00 将通过传输在上一天的晚上 8:00 备份点之后已修改的数据来创建备份点。In the example shown, at 8:00 PM every day a backup point is created by transferring the modified data from the previous day’s 8:00 PM backup point. 此过程称为 快速完整备份This process is called Express Full Backup. 事务日志每 15 分钟同步一次。Transaction logs are synchronized every 15 minutes. 如果需要在晚上 9:00 恢复数据库,则会基于日志从上次的快速完整备份点(在本例中为晚上 8 点)创建恢复点。If you need to recover the database at 9:00 PM, the point is created from the logs from the last express full backup point (8PM in this case).

  7. 在“检查磁盘分配”屏幕上,验证可用的总存储空间以及能够使用的磁盘空间。On the Review disk allocation screen, verify the overall storage space available, and the potential disk space. 选择“下一页”。Select Next.

  8. 在“选择副本创建方法”中,选择如何创建第一个恢复点。In the Choose Replica Creation Method, choose how to create your first recovery point. 可以选择手动传输初始备份(脱离网络),以免网络出现带宽拥塞现象。You can transfer the initial backup manually (off network) to avoid bandwidth congestion or over the network. 如果选择等待传输第一个备份,则可以指定初始传输的时间。If you choose to wait to transfer the first backup, you can specify the time for the initial transfer. 选择“下一页”。Select Next.

    初始复制方法

    初始备份复制要求将整个数据源(SQL Server 数据库)从生产服务器(SQL Server 计算机)传输到 Azure 备份服务器。The initial backup copy requires transferring the entire data source (SQL Server database) from production server (SQL Server computer) to Azure Backup Server. 此类数据可能会非常大,通过网络传输此类数据可能会超过带宽限制。This data might be large, and transferring the data over the network could exceed bandwidth. 因此,可以选择通过以下方式传输初始备份:“手动”(使用可移动媒体),以免网络出现带宽拥塞现象;或“自动通过网络”(于指定时间)。For this reason, you can choose to transfer the initial backup: Manually (using removable media) to avoid bandwidth congestion, or Automatically over the network (at a specified time).

    初始备份完成后,其余的备份都是初始备份副本的增量备份。Once the initial backup is complete, the rest of the backups are incremental backups on the initial backup copy. 增量备份往往比较小,能轻松地通过网络传输。Incremental backups tend to be small and are easily transferred across the network.

  9. 选择需要运行一致性检查的时间,并选择“下一步”。Choose when you want the consistency check to run and select Next.

    一致性检查

    Azure 备份服务器可以通过执行一致性检查来检查备份点的完整性。Azure Backup Server performs a consistency check on the integrity of the backup point. Azure 备份服务器会计算生产服务器(在本方案中为 SQL Server 计算机)上的备份文件和该文件的已备份数据的校验和。Azure Backup Server calculates the checksum of the backup file on the production server (SQL Server computer in this scenario) and the backed-up data for that file. 如果存在冲突,则会认为 Azure 备份服务器上的备份文件已损坏。If there's a conflict, it's assumed the backed-up file on Azure Backup Server is corrupt. Azure 备份服务器 会发送与校验和不匹配部分相对应的块来纠正备份的数据。Azure Backup Server rectifies the backed-up data by sending the blocks corresponding to the checksum mismatch. 由于一致性检查对性能要求较高,因此你可以计划一致性检查或者自动运行它。Because consistency checks are performance-intensive, you can schedule the consistency check or run it automatically.

  10. 如果要指定对数据源进行在线保护,请选择要通过 Azure 进行保护的数据库,并选择“下一步”。To specify online protection of the datasources, select the databases to be protected to Azure and select Next.

    选择数据源

  11. 选择适合组织策略的备份计划和保留策略。Choose backup schedules and retention policies that suit the organization policies.

    计划和保留

    在本示例中,备份会在一天的中午 12:00 和晚上 8:00 各进行一次(参见屏幕底部)In this example, backups are taken once a day at 12:00 PM and 8 PM (bottom part of the screen)

    备注

    最好是在磁盘上设置几个短期恢复点,以便进行快速恢复。It’s a good practice to have a few short-term recovery points on disk, for quick recovery. 这些恢复点适用于“操作恢复”。These recovery points are used for operational recovery. Azure 具有较高的 SLA,其可用性也可以得到保证,因此可作为理想的非现场位置。Azure serves as a good offsite location with higher SLAs and guaranteed availability.

    最佳做法:如果将目的地为 Azure 的备份计划为在本地磁盘备份完成后启动,则始终会将最新的磁盘备份复制到 Azure。Best Practice: If you schedule backups to Azure to start after the local disk backups complete, the latest disk backups are always copied to Azure.

  12. 选择保留策略计划。Choose the retention policy schedule. 有关保留策略工作原理的详细信息,请参阅使用 Azure 备份来取代磁带基础结构文章The details on how the retention policy works are provided at Use Azure Backup to replace your tape infrastructure article.

    保留策略

    在本示例中:In this example:

    • 备份会在一天的中午 12:00 和晚上 8:00 各进行一次(参见屏幕底部),并且会保留 180 天。Backups are taken once a day at 12:00 PM and 8 PM (bottom part of the screen) and are retained for 180 days.
    • 在星期六中午 12:00 进行的备份The backup on Saturday at 12:00 P.M. 会保留 104 周is retained for 104 weeks
    • 在最后一个星期六中午 12:00 进行的备份The backup on Last Saturday at 12:00 P.M. 会保留 60 个月is retained for 60 months
    • 在 3 月的最后一个星期六中午 12:00 进行的备份The backup on Last Saturday of March at 12:00 P.M. 会保留 10 年is retained for 10 years
  13. 选择“下一步”,然后选择相应的选项将初始备份副本传输到 Azure。Select Next and select the appropriate option for transferring the initial backup copy to Azure. 可以选择“自动通过网络”You can choose Automatically over the network

  14. 在“摘要”屏幕中复查策略详细信息后,选择“创建组”以完成工作流 。Once you review the policy details in the Summary screen, select Create group to complete the workflow. 可以选择“关闭”,然后在“监视”工作区中监视作业进度。You can select Close and monitor the job progress in Monitoring workspace.

    保护组创建进度

SQL Server 数据库的按需备份On-demand backup of a SQL Server database

虽然前述步骤创建了备份策略,但“恢复点”仅在进行首个备份的时候创建。While the previous steps created a backup policy, a “recovery point” is created only when the first backup occurs. 如果不想等待计划程序进行计划,则以下步骤可触发手动创建恢复点。Rather than waiting for the scheduler to kick in, the steps below trigger the creation of a recovery point manually.

  1. 在创建恢复点之前,请等待数据库的保护组状态显示为“正常”。Wait until the protection group status shows OK for the database before creating the recovery point.

    保护组成员

  2. 右键单击该数据库,并选择“创建恢复点”。Right-click on the database and select Create Recovery Point.

    创建在线恢复点

  3. 在下拉菜单中选择“在线保护”,然后选择“确定”以开始在 Azure 中创建恢复点 。Choose Online Protection in the drop-down menu and select OK to start creation of a recovery point in Azure.

    创建恢复点

  4. 在“监视”工作区中查看作业进度。View the job progress in the Monitoring workspace.

    监视控制台

从 Azure 恢复 SQL Server 数据库Recover a SQL Server database from Azure

若要从 Azure 中恢复受保护的实体(SQL Server 数据库),必须执行以下步骤。The following steps are required to recover a protected entity (SQL Server database) from Azure.

  1. 打开 Azure 备份服务器管理控制台。Open the Azure Backup Server Management Console. 导航到“恢复”工作区,可以在其中看到受保护的服务器。Navigate to Recovery workspace where you can see the protected servers. 浏览所需的数据库(在本示例中为 ReportServer$MSDPM2012)。Browse the required database (in this case ReportServer$MSDPM2012). 选择指定为“在线”点的一个“恢复开始”时间 。Select a Recovery from time that's specified as an Online point.

    选择恢复点

  2. 右键单击数据库名称并选择“恢复”。Right-click the database name and select Recover.

    从 Azure 恢复

  3. MABS 会显示恢复点的详细信息。MABS shows the details of the recovery point. 选择“下一步”。Select Next. 选择恢复类型“恢复到 SQL Server 的原始实例”。To overwrite the database, select the recovery type Recover to original instance of SQL Server. 选择“下一页”。Select Next.

    恢复到原始位置

    在此示例中,MABS 将数据库恢复到另一个 SQL Server 实例或独立的网络文件夹。In this example, MABS recovers the database to another SQL Server instance, or to a standalone network folder.

  4. 在“指定恢复选项”屏幕上,可以选择恢复选项(例如“网络带宽使用限制”),以便限制恢复操作所使用的带宽。In the Specify Recovery options screen, you can select the recovery options like Network bandwidth usage throttling to throttle the bandwidth used by recovery. 选择“下一页”。Select Next.

  5. 在“摘要”屏幕上,会看到目前提供的所有恢复配置。In the Summary screen, you see all the recovery configurations provided so far. 选择“恢复”。Select Recover.

    恢复状态显示数据库正在恢复。The Recovery status shows the database being recovered. 可以选择“关闭”来关闭向导,并在“监视”工作区中查看进度。 You can select Close to close the wizard and view the progress in the Monitoring workspace.

    启动恢复过程

    完成恢复操作后,还原的数据库在应用程序级别是一致的。Once the recovery is completed, the restored database is application consistent.

后续步骤Next steps

请参阅备份文件和应用程序一文。See the Backup files and application article. 请参阅在 Azure Stack 上备份 SharePoint 一文。See the Backup SharePoint on Azure Stack article.