[翻译]——SQL Server 2019加速数据库恢复新特性

前言：本文是对这篇博客Accelerated Database Recovery; Instant Rollback and Database Recovery^[1]的翻译，翻译如有不当的地方，敬请谅解，请尊重原创和翻译劳动成果，转载的时候请注明出处。谢谢！

英文原文地址：https://www.sqlshack.com/accelerated-database-recovery-instant-rollback-and-database-recovery/

本文的主题是加速数据库的恢复，包括在SQL Server 2019中Kill掉Active会话，服务器宕机（abnormal shutdown）和加速恢复功能特征本身（accelerate recovery）

SQL Server数据库恢复是DBA的一项重要并且关键的任务。我们定期进行数据库备份，以便从任何意外停机中恢复数据库。我们会面临很多DBA无法控制实际恢复的场景，唯一的解决方案是等待恢复（recovery）完成。在本文中，我们将讨论 SQL Server数据库恢复方案以及SQL Server 2019新增的数据库恢复的新功能。

我们首先准备测试环境，然后解释恢复（recovery）问题。在这个示例中，我们使用的的是SQL Server 2019，您可以使用select @@version命令验证实际版本。

使用下面SQL创建示例表。

USE [SQLShackDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[tblSQLShackDemo](
  [S.No.] [int] IDENTITY(0,1) NOT NULL,
  [value] [uniqueidentifier] NULL,
  [Date] [datetime] NULL
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[tblSQLShackDemo] ADD  DEFAULT (getdate()) FOR [Date]
GO

场景1：Kill掉一个正在运行的活动会话

假设你正在运行一个大型的插入或更新的DML语句，这个语句处于正在执行状态（executing state），由于某些原因，例如高CPU或内存消耗、阻塞、死锁、数据库性能问题，你需要终止这个会话，执行了Kill命令后，会话将进入回滚状态，并且可能需要很长时间才能完成恢复过程。

我们向表tblSQLShackDemo插入500K条记录来演示这个问题，执行下面SQL开始一个事务

Begin transaction
Declare @Id int
Set @Id = 1
 
While @Id <= 1000000
Begin 
   Insert Into tblSQLShackDemo(value) values (newid())
   Set @Id = @Id + 1
End

执行SQL后，我们可以使用 sp_who2 ‘SPID’ 命令检查其状态。

当SQL语句还在执行过程中，我们可以使用NOLOCK提示来统计表的记录数。

select count(*) from tblSQLShackDemo(nolock)

到目前为止，SQL执行了3分钟，插入了457134条记录。

现在我们Kill掉SPID以启动回滚过程。执行命令KILL 55，在这个命令中，55是运行INSERT语句会话的SPID

在sp_who2命令中，我们可以看到会话的状态为ROLLBACK

我们可以使用以下SQL跟踪回滚会话的进度。

KILL 55 WITH STATUSONLY

在下面的屏幕截图中，你可以看到它显示估计回滚时间为3657秒，大约60分钟

如果这个SQL语句在Kill掉之前执行的时间越长，那么会话回滚可能会需要更多多的时间，也许是几个小时。你还要承担回滚过程中额外的CPU和Memory负载。当前事务还会在特定表上阻塞其它会话。在这种情况下，除了等待它完成之外，我们DBA也无法做任何事情。

场景2:数据库异常宕机

让我们想象一下另外一个场景，当你启动了一个事务，往我们的样例表中插入大量的数据，突然系统崩溃了（crashed）,一旦系统重新启动，你需要启动SQL Server服务，SQL Server服务上线后，用户数据库仍在执行恢复。

SQL Server服务启动后，将会将数据库联机（online），在下面屏幕截图中，你可以看到数据库状态处于恢复状态

这个时候，我们还无法访问数据库。我们可以从SQL Server日志中查看、了解更多详细信息。在错误日志中，你可能会看到下面这些消息。

Recovery of database ‘SQLShackDemo’ (5) is 0% complete (approximately 36351 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

根据错误日志条目，大约需要 36,351 秒，即大约 10 小时。这是真的吗？我们是否需要等待10 个小时，SQL Server 数据库才能联机上线（online)？是真的。我们需要等待数据库完全online。最糟糕的是，除了刷新错误日志和监控进度之外，我们什么也做不了。对于DBA来说，这确实是一种无奈的情况。

在下面的屏幕截图中，数据库恢复的第三阶段开始。此时，数据库可供用户使用，数据库可以访问，但是SQL Server仍在恢复数据库。

一旦数据库恢复完成后，我们会在错误日志中收到以下消息。

Recovery completed for database SQLShackDemo (database ID 5) in 1802 second(s) (analysis 1375 ms, redo 551401 ms, undo 1246756 ms.) This is an informational message only. No user action is required.

SQL Server用了1802秒，也就是大约30分钟来恢复这个用户数据库，可能需要更长的时间，具体取决于SQL Server在恢复后使数据库处于一致状态所执行的工作量

我们将在本机的后面部分详细介绍恢复阶段。

到目前为止，我们可以看到SQL Server DBA的以下痛点。

超长的恢复时间（recovery time）
回滚（undo）耗费太长时间。

让我们在SQL Server 2019中重复上面的场景，体验一下SQL Server 2019 加速数据库恢复的新功能特征吧。

Accelerated Database Recovery with SQL Server 2019

SQL Server 2019 引入了新的数据库恢复功能加速数据库恢复。它重新设计了SQL Server 中的数据库恢复过程。我们可以立即回滚任何事务。它还可以在发生任何灾难（例如服务器崩溃、集群或 AG 故障转移）时改进数据库恢复。

我们需要在数据库级别启用加速数据库恢复功能。默认情况下，所有数据库都是禁用的。

在这个例子中，我们创建了另一个数据库 SQLSHACKDEMO_ADR 以及同一个表 tblSqlShackDemo。

我们可以看到在sys.databases中增加了一个新列来检查特定数据库上是否启用了加速数据库恢复特性。

SELECT  name ,
        create_date ,
        compatibility_level ,
        physical_database_name ,
        is_accelerated_database_recovery_on
FROM    sys.databases;

我们可以使用下面数据库命令启用“加速数据库恢复”功能。

ALTER DATABASE SQLSHACKDEMO_ADR SET ACCELERATED_DATABASE_RECOVERY = ON;

我花了大约 7 分钟在空的数据库上启用此功能。它可能会在 SQL Server 2019 的后续版本中得到改进。

现在，运行上面提到的SQL命令。在下面的屏幕截图中，我们可以看到为 SQLShackDemo_ADR 数据库启用了加速数据库恢复。

让我们使用这个启用了加速数据库恢复功能的数据库来执行上面两个场景。

场景1：Kill掉一个正在运行的活动会话

运行SQL，在 tblSQLShackDemo 表中插入批量记录并在大约3分钟后终止会话。

下面是两者的区别：

没有“加速数据库恢复”功能的数据库的回滚大约需要 60分钟才能完成回滚。
拥有“加速数据库恢复”功能的数据库快速完成了回滚

让我们重复场景2，正在执行SQL语句的时候重启SQL Server，服务恢复后，我们连接到SQL Server实例，我们可以看到数据库已经联机在线（online)

是的，它真的是联机在线了，我们不会再痛苦的等待很久，一直刷新错误日志急迫的等待数据库联机上线的消息。

让我们去看看错误日志，我们会看到下一些消息：

Recovery completed for database SQLShackDemo_ADR (database ID 6) in 12 second(s) (analysis 8162 ms, redo 2593 ms, undo 236 ms.) This is an informational message only. No user action is required.

下面你可以注意到两次执行之间的差异。

在下面的屏幕截图中，您可以以图形方式注意到数据库恢复的时间差异。

在 SQL Server 中，数据库恢复有下面三个阶段的步骤。

分析(Analysis)
重做(Redo)
回滚(Undo)

在下表中，我们可以理解这三个恢复阶段。

分析阶段：SQL Server定期运行内部检查点进程。当SQL Server启动时，它会从最后一个成功的检查点开始读取事务日志。它向前读取日志,重建事务表(transactions table)和脏页表（dirty pages table）,在分析阶段结束时，我们有提交事务（需要前滚）或未提交的事务（需要回滚）。
重做阶段：在这个阶段，SQL Server从最旧的未提交事务开始读取，并在脏页表的帮助下，它在崩溃点接管系统。（从SQL Server 2005 开始）重做阶段后，用户可以访问 SQL Server数据库。
回滚阶段：SQL Server 需要回滚系统崩溃时所有活动更改（译者注释：其实这里翻译为SQL Server需要回滚（undo）系统奔溃时未提交的事务）。SQL Server 开始向后读取事务日志，并在活动事务表的帮助下回滚事务。当我们杀死一个活动事务时，SQL Server 需要执行 undo 恢复过程。因此，回滚也可能需要更长的时间。

下图（参考 – Microsoft Docs）显示了整个数据库的恢复过程。

Accelerated Database Recovery in SQL Server 2019

一旦我们在SQL Server数据库上启用了加速数据库回复，它就会存储所有修改的版本。它类似于Read Committed Snapshot Isolation隔离级别中的版本控制。SQL Server将以前的版本存储在叫做s-log的二级内存优化日志中。

持久版本存储 (PVS)：在持久版本存储中，SQL Server 将行版本存储在启用了加速数据库恢复功能的数据库中
逻辑还原：SQL Server 使用 PVS 立即撤消更改，不需要从事务日志中读取详细信息，这是一个耗时的过程
sLog：它存储日志记录，用于非版本化操作的日志记录。这些操作可以是 DDL 命令、批量查询。它使重做和回滚处理更快，因为它们只需要处理非版本化操作
Cleaner：Cleaner 进程会自动删除 SQL Server 不需要的版本进行恢复

下图（参考 – Microsoft Docs）显示了使用加速数据库恢复的整个数据库恢复过程。

结论：

在本文中，我们探讨了SQL Server 2019加速数据库恢复功能。它缩短了数据库恢复时间，解决了DBA痛苦尴尬的境遇。

译者题外话，之前工作中也遇到过数据库recovery耗费很长时间的问题，这个确实是一个令DBA头痛且无奈的事情，我们既不能干预也不能加速，只能等待，SQL Server 2019这个新增的特性确实让人眼前一亮，以后再也不用头痛这种问题了。看Accelerated Database Recovery的原理，一股浓浓的熟悉的配方味道。确实跟Oracle的UNDO表空间原理很相像。但是实现方式也还有一些差别。