事務日誌核心角色
- **寫前日誌 (WAL)**：資料變更先寫日誌緩衝區 → 刷盤 (強制) → 異步寫入資料頁 (Checkpoint/Lazy Writer)。
- **恢復依據**：啟動時 **Redo (前滾)** 已提交交易 + **Undo (回滾)** 未提交交易，保證 ACID。
- **同步載體**：日誌傳送、資料庫鏡像、AlwaysOn 皆以「傳送/重放日誌記錄」為核心。
故障一：伺服器意外關機 (斷電/藍畫面/殺進程) 導致資料遺失風險
機制
- 交易提交時，日誌記錄 **強制寫入磁碟** (Write-Ahead Logging)，資料頁**暫留記憶體**。
- 斷電瞬間：日誌已持久化，資料頁可能未刷新。
- 重啟啟動資料庫：
- 掃描日誌 → **Redo** 所有已提交交易 (將資料頁帶到最新)。
- **Undo** 所有未提交交易 (回滾部分變更)。
- 資料庫進入 **一致狀態 (ONLINE)**。
關鍵操作
- **檢查日誌可用性**：啟動前確認 `.ldf` 完整、無損毀。若日誌遺失/損壞，**無法 Redo/Undo**，資料庫進入 SUSPECT/EMERGENCY，需人工修復 (見 83722)。
- **不要強制刪除 .ldf 重建** (除非接受資料遺失且無備份)。
- **啟用 CHECKSUM 頁面驗證**：`ALTER DATABASE ... SET PAGE_VERIFY CHECKSUM;` 早期偵測硬體損壞。
故障二：異地高可用同步 — 日誌傳送 vs 資料庫鏡像 vs AlwaysOn
方案 A：日誌傳送 — 成熟、低成本、手動切換
主庫 (Primary) 次要庫 (Secondary) 監視器 (Monitor, 可選)
│ │ │
├─ BACKUP LOG → 共享資料夾 ← RESTORE LOG WITH STANDBY (唯讀可查詢)
│ (排程: 15分/1小時) (排程: 延遲還原) │
│ │ │
└─ 清理舊備份 └─ 報告狀態/警報 ← 記錄歷史/發送警報
- **復原模式限制**：主庫必須 **FULL** 或 **BULK_LOGGED** (SIMPLE 會截斷日誌導致傳送中斷)。
- **多主一從**：一台次要庫可承載多個主庫備份，節省硬體/授權成本。
- **切換流程**：手動執行尾部日誌備份 → 次要庫 `RESTORE ... WITH RECOVERY` → 修改連線字串/DNS → 上線。**RTO 分鐘級，RPO = 最後一次傳送間隔**。
- **優點**：簡單、標準版支援、次要庫可讀 (STANDBY)、頻寬需求低 (壓縮備份)。
- **缺點**：無自動容錯移轉、切換需人工、次要庫還原期間有延遲。
方案 B：資料庫鏡像 — 自動容錯移轉、同步/非同步模式 (SQL 2005-2016，已淘汰轉 AlwaysOn)
| 模式 | 同步性 | 資料遺失風險 | 效能影響 | 自動容錯移轉 |
|---|
| 高安全性 (同步) | 主從雙寫確認 | 零 (同步提交) | 主庫延遲 = 網路 RTT + 從庫寫入 | 需見證伺服器支援 |
| 高效能 (非同步) | 主庫不等從庫確認 | 可能 (最後幾筆日誌) | 主庫幾乎無延遲 | 不支援 (需手動強制服務) |
- **架構**：主體 + 鏡像 (配對) + 見證 (選配，僅高安全性模式用於自動容錯移轉仲裁)。
- **限制**：單一資料庫、僅 FULL 復原模式、無讀取擴展 (鏡像庫不可讀，需快照)。
- **角色切換**：自動 (高安全性+見證) 或手動 `ALTER DATABASE ... SET PARTNER FAILOVER`。
方案 C：AlwaysOn 可用性群組 (SQL 2012+ 標準配置) — 企業級首選
- **群組層級**容錯移轉 (多資料庫原子切換)。
- **可讀次要複本** (Readable Secondary) — 報表卸載、備份卸載。
- **多目標** (最多 8 次要，含 2 同步 + 6 非同步)。
- **靈活容錯移轉策略** (自動/手動/外部)。
- **無共享儲存需求** (不同於 FCI)。
災難切換實戰 SOP (以日誌傳送為例)
- 確認主庫狀態：若主庫仍可存取 → `BACKUP LOG Db_Test TO DISK='...' WITH NORECOVERY` (尾部日誌)。
- 次要庫應用尾部日誌：`RESTORE LOG Db_Test_Bak FROM DISK='...' WITH RECOVERY` → 資料庫上線 (零資料遺失)。
- 主庫不可存取：次要庫直接 `RESTORE LOG Db_Test_Bak WITH RECOVERY` → **損失尾部日誌後所有交易** (RPO = 最後成功傳送點)。
- 驗證業務功能：連線測試、關鍵查詢、寫入測試。
- 更新應用配置：連線字串、DNS CNAME、負載平衡器健康檢查。
- 事後複盤：RTO/RPO 實測、根因分析、改進備份頻率/網路冗餘。
日誌管理最佳實踐
- **定期完整備份 + 高頻日誌備份** (如 15 分鐘) — 控制 RPO、截斷日誌空間。
- **監控 `log_reuse_wait_desc`** — `LOG_BACKUP` (需備份)、`ACTIVE_TRANSACTION` (長交易)、`REPLICATION`/`AVAILABILITY_REPLICA` (同步延遲)。
- **預設自動增長固定 MB + 瞬間檔案初始化** — 避免 VLF 碎片化 (過多 VLF 導致啟動/還原/備份緩慢)。
- **異地備份副本** — 至少異地一份 (雲存儲/異地機房)。
- **定期演練還原** — 未測試的備份 = 無備份。
總結
事務日誌是 SQL Server **靈魂**：
1. **意外關機** — 靠日誌 Redo/Undo 自動修復，**勿動 .ldf**。
2. **異地同步** — 日誌傳送 (手動、成本低)、鏡像 (自動、淘汰中)、**AlwaysOn (現代標準、群組級、可讀次要)**。
3. **災難切換** — 有尾部日誌 = 零遺失；無尾部日誌 = 接受 RPO。
**投資日誌備份基礎設施 (頻率、網路、異地、演練)，就是投資資料資產的最終保障。**
