SQL Server 2005 資料庫鏡像部署腳本與見證伺服器設定完整指南

前言：高可用性架構的核心組件

資料庫鏡像 提供資料庫級別的熱備援，支援 **高安全模式 (同步)** 與 **高效能模式 (非同步)**。本文提供標準化部署 T-SQL 腳本，涵蓋兩夥伴節點與見證伺服器端點建立、鏡像會話啟動、手動容錯移轉指令。

架構拓撲與連接埠規劃

Server1: 192.168.5.106 (Principal), Server2: 192.168.5.106 (Mirror), Witness: 192.168.5.106 (同一測試機不同埠)。生產環境必須為**獨立實體/虛擬機器**，跨機架/可用區域部署。

標準部署腳本 (依序在各節點執行)

節點 1：Principal 伺服器

-- 1. 建立鏡像端點 (僅需執行一次)
CREATE ENDPOINT DbMirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023)
    FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

-- 2. 設定夥伴為 Mirror 伺服器
ALTER DATABASE AdventureWorks
    SET PARTNER = 'TCP://192.168.5.106:5022';
GO

節點 2：Mirror 伺服器

-- 1. 建立鏡像端點
CREATE ENDPOINT DbMirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

-- 2. 設定夥伴為 Principal 伺服器
ALTER DATABASE AdventureWorks
    SET PARTNER = 'TCP://192.168.5.106:5023';
GO

見證伺服器 (可選，支援自動容錯移轉)

-- 1. 建立見證端點 (ROLE = WITNESS)
CREATE ENDPOINT DbMirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5033)
    FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);
GO

-- 2. 在 Principal 執行：加入見證
ALTER DATABASE AdventureWorks
    SET WITNESS = 'TCP://192.168.5.106:5033';
GO

手動容錯移轉 (計畫性維護/測試)

-- 在 Principal 伺服器執行，角色互換
USE MASTER;
GO
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO

執行後，原 Mirror 成為新 Principal，原 Principal 成為 Mirror。連線字串需支援 `Failover Partner=MirrorServer` 自動重導。

關鍵設定檢查清單

1. 端點權限： 服務帳號需具 `CONNECT` 權限 `GRANT CONNECT ON ENDPOINT::DbMirroring TO [Domain\ServiceAccount];`。
2. 防火牆： 雙向開放 5022, 5023, 5033 (或自訂埠)。
3. 憑證/Windows 驗證： 跨網域環境需設定憑證 `CREATE CERTIFICATE ...` 並授權端點。
4. 資料庫復原模式： **必須為 FULL 復原模式**，且已執行至少一次完整備份 + 記錄備份至 Mirror 並 `WITH NORECOVERY` 還原。
5. 相容性等級： 兩節點版本、版次 (Standard/Enterprise)、修補程式層級建議一致。

監控與故障排除

-- 鏡像狀態總覽
SELECT
    db_name(sd.database_id) AS DatabaseName,
    sd.mirroring_state_desc AS State,
    sd.mirroring_role_desc AS Role,
    sd.mirroring_partner_instance AS Partner,
    sd.mirroring_witness_state_desc AS WitnessState,
    sd.mirroring_safety_level_desc AS SafetyLevel,
    sd.mirroring_connection_timeout AS TimeoutSec
FROM sys.database_mirroring sd
WHERE sd.mirroring_guid IS NOT NULL;

-- 鏡像效能計數器 (效能監視器)
-- SQLServer:Database Mirroring -> Bytes Received/Sec, Bytes Sent/Sec, Log Remaining for Undo KB, ...

常見錯誤與對策

結語

資料庫鏡像以「資料庫級、同步/非同步可選、自動容錯移轉 (含見證)、透明用戶端重導」成為 SQL Server 2005-2016 經典高可用方案。**關鍵在於：FULL 復原模式、完整備份鏈初始化、端點加密與權限、網路雙向通暢、定期容錯移轉演練。** 現代架構 (2012+) 建議優先考慮 Always On 可用性群組 (支援多副本、讀取負載平衡、群組級容錯移轉)。