——sql server 2019加速數據庫恢復新特性

本文的主題是加速數據庫的恢復，包括在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創建示例表。

假設你正在運行一個大型的插入或更新的DML語句，這個語句處于正在執行狀態（executing state），由于某些原因，例如高CPU或內存消耗、阻塞、死鎖、數據庫性能問題，你需要終止這個會話，執行了Kill命令后，會話將進入 回滾狀態，并且可能需要很長時間才能完成恢復過程。

我們向表tblSQLShackDemo插入500K條記錄來演示這個問題，執行下面SQL開始一個事務

執行SQL后，我們可以使用 sp_who2 ‘SPID’ 命令檢查其狀態。

當SQL語句還在執行過程中，我們可以使用NOLOCK提示來統計表的記錄數。

到目前為止，SQL執行了3分鐘，插入了457134條記錄。

現在我們Kill掉SPID以啟動回滾過程。執行命令KILL 55，在這個命令中，55是運行INSERT語句會話的SPID

在sp_who2命令中，我們可以看到會話的狀態為ROLLBACK

我們可以使用以下SQL跟蹤回滾會話的進度。

在下面的屏幕截圖中，你可以看到它顯示估計回滾時間為3657秒，大約60分鐘

如果這個SQL語句在Kill掉之前執行的時間越長，那么會話回滾可能會需要更多多的時間，也許是幾個小時。你還要承擔回滾過程中額外的CPU和Memory負載。當前事務還會在特定表上阻塞其它會話。在這種情況下，除了等待它完成之外，我們DBA也無法做任何事情。

讓我們想象一下另外一個場景，當你啟動了一個事務，往我們的樣例表中插入大量的數據，突然系統崩潰了（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 加速數據庫恢復的新功能特征吧。

SQL Server 2019 引入了新的數據庫恢復功能加速數據庫恢復。它重新設計了SQL Server 中的數據庫恢復過程。我們可以立即回滾任何事務。它還可以在發生任何災難（例如服務器崩潰、集群或 AG 故障轉移）時改進數據庫恢復。

我們需要在數據庫級別啟用加速數據庫恢復功能。默認情況下，所有數據庫都是禁用的。

在這個例子中，我們創建了另一個數據庫 SQLSHACKDEMO_ADR 以及同一個表 tblSqlShackDemo。

我們可以看到在sys.databases中增加了一個新列來檢查特定數據庫上是否啟用了加速數據庫恢復特性。

我們可以使用下面數據庫命令啟用“加速數據庫恢復”功能。

我花了大約 7 分鐘在空的數據庫上啟用此功能。它可能會在 SQL Server 2019 的后續版本中得到改進。

現在，運行上面提到的SQL命令。在下面的屏幕截圖中，我們可以看到為 SQLShackDemo_ADR 數據庫啟用了加速數據庫恢復。

讓我們使用這個啟用了加速數據庫恢復功能的數據庫來執行上面兩個場景。

運行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表空間原理很相像。但是實現方式也還有一些差別。[1]

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