SQL Server 2005 資料庫鏡像搭建完整教學 (高安全性/高效能/見證伺服器)

資料庫鏡像 是 SQL Server 2005 引入的高可用性技術，以非同步/同步方式將交易記錄從主體伺服器傳送至鏡像伺服器，支援自動容錯移轉 (需見證伺服器)。本文提供三台伺服器環境的完整設定步驟、證書驗證模式與測試腳本。

環境規劃

• 作業系統：Windows Server 2003 SP2+ (建議 2008 R2+)
• SQL 版本：SQL Server 2005 SP3+ (SP1 啟用鏡像需追蹤旗標 1400，SP3 內建)
• 資料庫：ccerp_jzt，復原模式 FULL

前置作業：資料庫備份/還原至鏡像端 (NORECOVERY)

-- 主體 A：完整備份
BACKUP DATABASE ccerp_jzt TO DISK = N'F:\databak\ccerp_jzt_full.bak' WITH INIT;
-- 記錄備份 (鏡像同步起點)
BACKUP LOG ccerp_jzt TO DISK = N'F:\databak\ccerp_jzt_log.trn' WITH NO_TRUNCATE;

-- 鏡像 B：建立同名空資料庫 (路徑需一致或還原時 MOVE)
RESTORE DATABASE ccerp_jzt FROM DISK = N'F:\databak\ccerp_jzt_full.bak'
WITH NORECOVERY,
     MOVE 'ccerp_ydswzip_Data' TO 'D:\Data\ccerp_jzt.mdf',
     MOVE 'ccerp_ydswzip_Log'  TO 'D:\Data\ccerp_jzt_log.ldf';

RESTORE LOG ccerp_jzt FROM DISK = N'F:\databak\ccerp_jzt_log.trn' WITH NORECOVERY;

驗證模式選擇

• Windows 網域帳號：三台加入同網域，SQL 服務啟動帳號使用同一網域帳號，最簡單。
• 憑證驗證：跨網域/工作群組環境必須使用，本文詳述此模式。

憑證模式完整設定腳本

1. 建立主金鑰與憑證 (三台皆執行，憑證名稱區分)

-- 主體 A
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd#2024';
CREATE CERTIFICATE HostA_Cert WITH SUBJECT = 'HostA Certificate', START_DATE = '2024-01-01';

-- 鏡像 B
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd#2024';
CREATE CERTIFICATE HostB_Cert WITH SUBJECT = 'HostB Certificate', START_DATE = '2024-01-01';

-- 見證 C
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd#2024';
CREATE CERTIFICATE HostC_Cert WITH SUBJECT = 'HostC Certificate', START_DATE = '2024-01-01';

2. 建立鏡像端點 (三台皆執行)

-- 主體 A (ROLE = ALL 可作主體/鏡像)
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE HostA_Cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

-- 鏡像 B
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE HostB_Cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

-- 見證 C (ROLE = WITNESS)
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE HostC_Cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = WITNESS);

3. 備份憑證並互換 (檔案層面複製)

-- 各自備份
BACKUP CERTIFICATE HostA_Cert TO FILE = 'E:\HostA_Cert.cer';
BACKUP CERTIFICATE HostB_Cert TO FILE = 'E:\HostB_Cert.cer';
BACKUP CERTIFICATE HostC_Cert TO FILE = 'E:\HostC_Cert.cer';

將三個 .cer 檔案複製到每台伺服器的相同路徑 (如 E:\Certs\)，每台最終擁有三個憑證檔。

4. 建立登入、使用者、授權端點連線 (三台皆執行，對應他台憑證)

-- 主體 A 範例：建立 B、C 的登入並授權
CREATE LOGIN HostB_Login WITH PASSWORD = 'ComplexPwd#123';
CREATE USER HostB_User FOR LOGIN HostB_Login;
CREATE CERTIFICATE HostB_Cert AUTHORIZATION HostB_User FROM FILE = 'E:\Certs\HostB_Cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HostB_Login;

CREATE LOGIN HostC_Login WITH PASSWORD = 'ComplexPwd#123';
CREATE USER HostC_User FOR LOGIN HostC_Login;
CREATE CERTIFICATE HostC_Cert AUTHORIZATION HostC_User FROM FILE = 'E:\Certs\HostC_Cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HostC_Login;

-- 鏡像 B、見證 C 同理建立其他兩台的登入/使用者/憑證/授權

5. 啟動鏡像夥伴關係 (先在鏡像端，再主體端，最後設定見證)

-- 鏡像 B 執行
ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.44:5022';

-- 主體 A 執行
ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';
ALTER DATABASE ccerp_jzt SET WITNESS = 'TCP://192.168.137.49:5022';

驗證鏡像狀態

SELECT
    DB_NAME(database_id) AS DatabaseName,
    mirroring_role_desc AS Role,
    mirroring_state_desc AS State,
    mirroring_safety_level_desc AS SafetyLevel,
    mirroring_partner_instance AS Partner
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

正常應顯示：主體 PRINCIPAL/SYNCHRONIZED、鏡像 MIRROR/SYNCHRONIZED、安全性 FULL (同步)。

常見操作測試

常見錯誤與解決

• 錯誤 1498：未安裝 SP1+，需套用 SP3 或啟動追蹤旗標 -T1400。
• 錯誤 1475：鏡像端有未備份的大量記錄作業，需在主體 BACKUP LOG ... WITH NO_TRUNCATE，鏡像 RESTORE LOG ... WITH NORECOVERY。
• 防火牆：三台需開放 TCP 5022 (端點通訊埠)。
• 時鐘同步：三台時間差 < 10 秒，建議加入網域時間同步。

版本限制提醒

• Standard Edition 僅支援 同步模式 (SAFETY FULL)。
• Enterprise/Developer Edition 支援非同步模式 (SAFETY OFF) 以追求效能。
• 見證伺服器可使用 Express Edition (僅支援見證角色)。