——sql server 2019加速數(shù)據(jù)庫恢復(fù)新特性

本文的主題是加速數(shù)據(jù)庫的恢復(fù)，包括在SQL Server 2019中Kill掉Active會話，服務(wù)器宕機(jī)（abnormal shutdown）和加速恢復(fù)功能特征本身（accelerate recovery）

SQL Server數(shù)據(jù)庫恢復(fù)是DBA的一項重要并且關(guān)鍵的任務(wù)。我們定期進(jìn)行數(shù)據(jù)庫備份，以便從任何意外停機(jī)中恢復(fù)數(shù)據(jù)庫。我們會面臨很多DBA無法控制實際恢復(fù)的場景，唯一的解決方案是等待恢復(fù)（recovery）完成。在本文中，我們將討論 SQL Server數(shù)據(jù)庫恢復(fù)方案以及SQL Server 2019新增的數(shù)據(jù)庫恢復(fù)的新功能。

我們首先準(zhǔn)備測試環(huán)境，然后解釋恢復(fù)（recovery）問題。在這個示例中，我們使用的的是SQL Server 2019，您可以使用select @@version命令驗證實際版本。

使用下面SQL創(chuàng)建示例表。

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

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

執(zhí)行SQL后，我們可以使用 sp_who2 ‘SPID’ 命令檢查其狀態(tài)。

當(dāng)SQL語句還在執(zhí)行過程中，我們可以使用NOLOCK提示來統(tǒng)計表的記錄數(shù)。

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

現(xiàn)在我們Kill掉SPID以啟動回滾過程。執(zhí)行命令KILL 55，在這個命令中，55是運(yùn)行INSERT語句會話的SPID

在sp_who2命令中，我們可以看到會話的狀態(tài)為ROLLBACK

我們可以使用以下SQL跟蹤回滾會話的進(jìn)度。

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

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

讓我們想象一下另外一個場景，當(dāng)你啟動了一個事務(wù)，往我們的樣例表中插入大量的數(shù)據(jù)，突然系統(tǒng)崩潰了（crashed）,一旦系統(tǒng)重新啟動，你需要啟動SQL Server服務(wù)，SQL Server服務(wù)上線后，用戶數(shù)據(jù)庫仍在執(zhí)行恢復(fù)。

SQL Server服務(wù)啟動后，將會將數(shù)據(jù)庫聯(lián)機(jī)（online），在下面屏幕截圖中，你可以看到數(shù)據(jù)庫狀態(tài)處于恢復(fù)狀態(tài)

這個時候，我們還無法訪問數(shù)據(jù)庫。我們可以從SQL Server日志中查看、了解更多詳細(xì)信息。在錯誤日志中，你可能會看到下面這些消息。

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.

根據(jù)錯誤日志條目，大約需要 36,351 秒，即大約 10 小時。這是真的嗎？我們是否需要等待10 個小時，SQL Server 數(shù)據(jù)庫才能聯(lián)機(jī)上線（online)？是真的。我們需要等待數(shù)據(jù)庫完全online。最糟糕的是，除了刷新錯誤日志和監(jiān)控進(jìn)度之外，我們什么也做不了。對于DBA來說，這確實是一種無奈的情況。

在下面的屏幕截圖中，數(shù)據(jù)庫恢復(fù)的第三階段開始。此時，數(shù)據(jù)庫可供用戶使用，數(shù)據(jù)庫可以訪問，但是SQL Server仍在恢復(fù)數(shù)據(jù)庫。

一旦數(shù)據(jù)庫恢復(fù)完成后，我們會在錯誤日志中收到以下消息。

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分鐘來恢復(fù)這個用戶數(shù)據(jù)庫，可能需要更長的時間，具體取決于SQL Server在恢復(fù)后使數(shù)據(jù)庫處于一致狀態(tài)所執(zhí)行的工作量

我們將在本機(jī)的后面部分詳細(xì)介紹恢復(fù)階段。

到目前為止，我們可以看到SQL Server DBA的以下痛點。

超長的恢復(fù)時間（recovery time）

回滾（undo）耗費(fèi)太長時間。

讓我們在SQL Server 2019中重復(fù)上面的場景，體驗一下SQL Server 2019 加速數(shù)據(jù)庫恢復(fù)的新功能特征吧。

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

我們需要在數(shù)據(jù)庫級別啟用加速數(shù)據(jù)庫恢復(fù)功能。默認(rèn)情況下，所有數(shù)據(jù)庫都是禁用的。

在這個例子中，我們創(chuàng)建了另一個數(shù)據(jù)庫 SQLSHACKDEMO_ADR 以及同一個表 tblSqlShackDemo。

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

我們可以使用下面數(shù)據(jù)庫命令啟用“加速數(shù)據(jù)庫恢復(fù)”功能。

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

現(xiàn)在，運(yùn)行上面提到的SQL命令。在下面的屏幕截圖中，我們可以看到為 SQLShackDemo_ADR 數(shù)據(jù)庫啟用了加速數(shù)據(jù)庫恢復(fù)。

讓我們使用這個啟用了加速數(shù)據(jù)庫恢復(fù)功能的數(shù)據(jù)庫來執(zhí)行上面兩個場景。

運(yùn)行SQL，在 tblSQLShackDemo 表中插入批量記錄并在大約3分鐘后終止會話。

下面是兩者的區(qū)別：沒有“加速數(shù)據(jù)庫恢復(fù)”功能的數(shù)據(jù)庫的回滾大約需要 60分鐘才能完成回滾。擁有“加速數(shù)據(jù)庫恢復(fù)”功能的數(shù)據(jù)庫快速完成了回滾

讓我們重復(fù)場景2，正在執(zhí)行SQL語句的時候重啟SQL Server，服務(wù)恢復(fù)后，我們連接到SQL Server實例，我們可以看到數(shù)據(jù)庫已經(jīng)聯(lián)機(jī)在線（online)

是的，它真的是聯(lián)機(jī)在線了，我們不會再痛苦的等待很久，一直刷新錯誤日志急迫的等待數(shù)據(jù)庫聯(lián)機(jī)上線的消息。

讓我們?nèi)タ纯村e誤日志，我們會看到下一些消息：

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.

下面你可以注意到兩次執(zhí)行之間的差異。

在下面的屏幕截圖中，您可以以圖形方式注意到數(shù)據(jù)庫恢復(fù)的時間差異。

在 SQL Server 中，數(shù)據(jù)庫恢復(fù)有下面三個階段的步驟。

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

在下表中，我們可以理解這三個恢復(fù)階段。

分析階段：SQL Server定期運(yùn)行內(nèi)部檢查點進(jìn)程。當(dāng)SQL Server啟動時，它會從最后一個成功的檢查點開始讀取事務(wù)日志。它向前讀取日志,重建事務(wù)表(transactions table)和臟頁表（dirty pages table）,在分析階段結(jié)束時，我們有提交事務(wù)（需要前滾）或未提交的事務(wù)（需要回滾）。

重做階段：在這個階段，SQL Server從最舊的未提交事務(wù)開始讀取，并在臟頁表的幫助下，它在崩潰點接管系統(tǒng)。（從SQL Server 2005 開始）重做階段后，用戶可以訪問 SQL Server數(shù)據(jù)庫。

回滾階段：SQL Server 需要回滾系統(tǒng)崩潰時所有活動更改（譯者注釋：其實這里翻譯為SQL Server需要回滾（undo）系統(tǒng)奔潰時未提交的事務(wù)）。SQL Server 開始向后讀取事務(wù)日志，并在活動事務(wù)表的幫助下回滾事務(wù)。當(dāng)我們殺死一個活動事務(wù)時，SQL Server 需要執(zhí)行 undo 恢復(fù)過程。因此，回滾也可能需要更長的時間。

下圖（參考 – Microsoft Docs）顯示了整個數(shù)據(jù)庫的恢復(fù)過程。

Accelerated Database Recovery in SQL Server 2019

一旦我們在SQL Server數(shù)據(jù)庫上啟用了加速數(shù)據(jù)庫回復(fù)，它就會存儲所有修改的版本。它類似于Read Committed Snapshot Isolation隔離級別中的版本控制。SQL Server將以前的版本存儲在叫做s-log的二級內(nèi)存優(yōu)化日志中。

持久版本存儲 (PVS)：在持久版本存儲中，SQL Server 將行版本存儲在啟用了加速數(shù)據(jù)庫恢復(fù)功能的數(shù)據(jù)庫中邏輯還原：SQL Server 使用 PVS 立即撤消更改，不需要從事務(wù)日志中讀取詳細(xì)信息，這是一個耗時的過程sLog：它存儲日志記錄，用于非版本化操作的日志記錄。這些操作可以是 DDL 命令、批量查詢。它使重做和回滾處理更快，因為它們只需要處理非版本化操作Cleaner：Cleaner 進(jìn)程會自動刪除 SQL Server 不需要的版本進(jìn)行恢復(fù)

下圖（參考 – Microsoft Docs）顯示了使用加速數(shù)據(jù)庫恢復(fù)的整個數(shù)據(jù)庫恢復(fù)過程。

在本文中，我們探討了SQL Server 2019加速數(shù)據(jù)庫恢復(fù)功能。它縮短了數(shù)據(jù)庫恢復(fù)時間，解決了DBA痛苦尷尬的境遇。

譯者題外話，之前工作中也遇到過數(shù)據(jù)庫recovery耗費(fèi)很長時間的問題，這個確實是一個令DBA頭痛且無奈的事情，我們既不能干預(yù)也不能加速，只能等待，SQL Server 2019這個新增的特性確實讓人眼前一亮，以后再也不用頭痛這種問題了。看Accelerated Database Recovery的原理，一股濃濃的熟悉的配方味道。確實跟Oracle的UNDO表空間原理很相像。但是實現(xiàn)方式也還有一些差別。[1]

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