SQL Server 日誌傳送完整配置實戰指南

日誌傳送是 SQL Server 提供的高可用性/災難恢復 (HA/DR) 解決方案之一，透過自動化備份、複製、還原交易記錄，在次要伺服器維護一個暖備份資料庫。本文以資料庫 logTrans1 為例，詳細演示使用 T-SQL 完成完整配置的步驟與關鍵參數說明。

架構角色與前置條件

• 主要伺服器 (Primary)： 來源資料庫 logTrans1
• 次要伺服器 (Secondary)： 目標資料庫 logTrans2（尚未建立）
• 監視伺服器 (Monitor)： 可選，本例使用 localhost
• 共享資料夾： \\10.101.10.66\data（主要伺服器寫入，次要伺服器讀取），需授予 SQL Server 服務帳號讀寫權限

步驟 1：初始化次要資料庫

在主要伺服器完整備份，並在次要伺服器以 NORECOVERY 模式還原，保持資料庫處於恢復狀態以接收後續記錄：

-- 主要伺服器執行備份
BACKUP DATABASE logTrans1
TO DISK = 'C:\logt.bak'
WITH INIT, COMPRESSION, STATS = 10;

-- 次要伺服器執行還原（需先建立共享或複製檔案）
RESTORE DATABASE logTrans2
FROM DISK = 'C:\logt.bak'
WITH NORECOVERY,
     MOVE 'logTrans' TO 'C:\logTrans2.mdf',
     MOVE 'logTrans_log' TO 'C:\logTrans2.ldf',
     STATS = 10;

步驟 2：新增主要資料庫至日誌傳送

在主要伺服器執行 sp_add_log_shipping_primary_database，建立備份作業與排程：

DECLARE @LS_BackupJobId UNIQUEIDENTIFIER;
DECLARE @LS_PrimaryId UNIQUEIDENTIFIER;

EXEC master.dbo.sp_add_log_shipping_primary_database
    @database = N'logTrans1',
    @backup_directory = N'D:\data',                    -- 本機備份路徑
    @backup_share = N'\\10.101.10.66\data',             -- 網路共享路徑
    @backup_job_name = N'LSBackup_logTrans1',
    @backup_retention_period = 1440,                    -- 備份檔保留分鐘數 (1天)
    @monitor_server = N'localhost',
    @monitor_server_security_mode = 1,                  -- Windows 驗證
    @backup_threshold = 60,                             -- 備份延遲閾值(分鐘)
    @threshold_alert_enabled = 0,
    @history_retention_period = 1440,                   -- 歷史記錄保留分鐘數
    @backup_job_id = @LS_BackupJobId OUTPUT,
    @primary_id = @LS_PrimaryId OUTPUT,
    @overwrite = 1;                                     -- 覆蓋現有配置

步驟 3：配置備份作業排程

本例設定每 2 分鐘備份一次（生產環境建議 15-60 分鐘）：

DECLARE @schedule_id INT;

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'LSBackup_logTrans1',
    @name = N'BackupDBEvery2Min',
    @enabled = 1,
    @freq_type = 4,                 -- 每日
    @freq_interval = 1,
    @freq_subday_type = 4,          -- 分鐘
    @freq_subday_interval = 2,      -- 每 2 分鐘
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 1,
    @active_start_date = 20080622,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 235959,
    @schedule_id = @schedule_id OUTPUT;

SELECT @schedule_id;

注意： sp_add_jobschedule 位於 msdb 資料庫，必須在該資料庫上下文執行或加前綴。

後續必要步驟（略）

1. 在次要伺服器執行 sp_add_log_shipping_secondary_database 註冊次要資料庫
2. 新增複製作業 (sp_add_log_shipping_secondary_primary) 將備份檔從共享複製到本機
3. 新增還原作業 (sp_add_log_shipping_secondary_database 相關參數) 自動還原記錄
4. 配置警示與監控：sp_add_log_shipping_alert_job、監視伺服器連結

關鍵參數調優建議

常見坑點避雷

• 權限問題： SQL Server 服務帳號必須對共享資料夾有完整讀寫權限
• 防火牆/連接埠： 確保 445 (SMB)、1433 (SQL) 等連接埠互通
• 時鐘同步： 主/次/監視伺服器時間必須同步，否則閾值判斷失效
• 版本相容： 次要伺服器版本必須 ≥ 主要伺服器版本

掌握 T-SQL 配置日誌傳送，能讓您在無 GUI 環境（如 Linux 容器、Core 版本）或自動化部署腳本中完整實現 HA/DR 架構。