SQL Server 資料庫鏡像完整實作指南：憑證模式跨網域高可用部署實戰

架構決策：為何選擇憑證模式？

• 網域環境：首選 Windows 驗證，但需同步服務帳號、SPN 設定、信任關係，變更帳號需重啟 SQL 服務，生產環境風險大。
• 工作群組/跨網域/雲端混合：憑證模式 無需網域，僅需 TCP 連通與憑證交換，部署彈性最高，本文採用此方案。
• 見證伺服器：本範例為簡化架構未部署見證，屬 高效能模式 (非同步) 或 高安全性模式無自動容錯移轉。生產建議加入見證啟用自動容錯移轉。

環境資訊

鏡像端點通訊埠：TCP 5022 (防火牆需雙向開放)。

階段一：主體伺服器 (A) 設定

-- 1. 建立資料庫主金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword#2024';
GO

-- 2. 建立主體專用憑證
CREATE CERTIFICATE Cert_A
WITH SUBJECT = 'Cert_A_Mirroring',
     START_DATE = '2024-01-01',
     EXPIRY_DATE = '2099-01-01';
GO

-- 3. 建立鏡像端點，綁定憑證
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRING (
    AUTHENTICATION = CERTIFICATE Cert_A,
    ENCRYPTION = REQUIRED ALGORITHM AES,  -- 建議 AES 強度
    ROLE = ALL
);
GO

-- 4. 備份憑證公開鑰，複製到鏡像伺服器 B
BACKUP CERTIFICATE Cert_A TO FILE = 'D:\Cert_A.cer';
GO

階段二：鏡像伺服器 (B) 設定

USE master;
GO

-- 1. 建立主金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword#2024';
GO

-- 2. 建立鏡像專用憑證
CREATE CERTIFICATE Cert_B
WITH SUBJECT = 'Cert_B_Mirroring',
     START_DATE = '2024-01-01',
     EXPIRY_DATE = '2099-01-01';
GO

-- 3. 建立端點，綁定自身憑證
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRING (
    AUTHENTICATION = CERTIFICATE Cert_B,
    ENCRYPTION = REQUIRED ALGORITHM AES,
    ROLE = ALL
);
GO

-- 4. 備份憑證公開鑰，複製到主體伺服器 A
BACKUP CERTIFICATE Cert_B TO FILE = 'D:\Cert_B.cer';
GO

階段三：雙向建立登入與授權 (關鍵步驟)

在主體伺服器 A 執行

-- 1. 建立鏡像端連線用登入
CREATE LOGIN Login_B WITH PASSWORD = 'ComplexPwd$Mirror';
GO

-- 2. 建立使用者映射
CREATE USER User_B FOR LOGIN Login_B;
GO

-- 3. 從 B 複製來的公開鑰建立憑證，授權給 User_B
CREATE CERTIFICATE Cert_B AUTHORIZATION User_B FROM FILE = 'D:\Cert_B.cer';
GO

-- 4. 授予連線端點權限
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Login_B];
GO

在鏡像伺服器 B 執行 (對稱操作)

CREATE LOGIN Login_A WITH PASSWORD = 'ComplexPwd$Mirror';
GO
CREATE USER User_A FOR LOGIN Login_A;
GO
CREATE CERTIFICATE Cert_A AUTHORIZATION User_A FROM FILE = 'D:\Cert_A.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Login_A];
GO

階段四：資料庫初始化 — 完整備份 + 記錄備份 + NORECOVERY 還原

主體伺服器 A：備份目標資料庫 TestDB

-- 確保完整復原模式
ALTER DATABASE TestDB SET RECOVERY FULL;
GO

-- 完整備份
BACKUP DATABASE TestDB 
TO DISK = 'D:\SQLBackup\TestDB_Full.bak' 
WITH FORMAT, INIT, COMPRESSION, CHECKSUM;
GO

-- 記錄備份 (同一文件追加)
BACKUP LOG TestDB 
TO DISK = 'D:\SQLBackup\TestDB_Full.bak' 
WITH CHECKSUM;
GO

-- 將 .bak 複製到鏡像伺服器 B 同路徑或共享路徑

鏡像伺服器 B：NORECOVERY 還原 (避免備份集不匹配錯誤 3154)

直接 RESTORE ... FROM DISK 易觸發「備份集與現有資料庫不同」錯誤。建議使用 sp_addumpdevice 邏輯裝置方式，確保備份集識別一致。

-- 1. 建立邏輯備份裝置
EXEC sp_addumpdevice 'disk', 'TestDB_Full_Dev',  'D:\Backup\TestDB_Full.bak';
EXEC sp_addumpdevice 'disk', 'TestDB_Log_Dev',   'D:\Backup\TestDB_Full.bak'; -- 同檔不同組
GO

-- 2. 完整備份還原 (NORECOVERY)
RESTORE DATABASE TestDB
FROM TestDB_Full_Dev
WITH FILE = 1, NORECOVERY, REPLACE, STATS = 10;
GO

-- 3. 記錄備份還原 (NORECOVERY)
-- 先查詢備份組序號
RESTORE HEADERONLY FROM TestDB_Log_Dev;
-- 假設最後一組 Position = 2
RESTORE LOG TestDB
FROM TestDB_Log_Dev
WITH FILE = 2, NORECOVERY;
GO

若備份檔包含多組，FILE 必須指定對應序號。可用 RESTORE HEADERONLY 確認最後一組 LSN 連續性。

階段五：啟動鏡像會話

先在鏡像伺服器 B 執行

ALTER DATABASE TestDB SET PARTNER = 'TCP://192.168.10.2:5022';
GO

再在主體伺服器 A 執行

ALTER DATABASE TestDB SET PARTNER = 'TCP://192.168.10.1:5022';
GO

常見操作與故障處理

手動容錯移轉 (主體主動切換)

-- 在主體伺服器執行
ALTER DATABASE TestDB SET PARTNER FAILOVER;
GO

強制服務 (主體掛掉，鏡像強制上線，可能遺失資料)

-- 在鏡像伺服器執行
ALTER DATABASE TestDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
GO

移除鏡像

ALTER DATABASE TestDB SET PARTNER OFF;
GO

完全清理鏡像殘留物件 (重新部署前必做)

-- 1. 移除鏡像關係
ALTER DATABASE TestDB SET PARTNER OFF;
GO

-- 2. 刪除端點
DROP ENDPOINT Endpoint_Mirroring;
GO

-- 3. 刪除憑證 (先刪憑證再刪主金鑰)
DROP CERTIFICATE Cert_A; -- 或 Cert_B
GO
DROP MASTER KEY;
GO

-- 4. 刪除登入/使用者
DROP LOGIN Login_B; -- 對應另一邊
DROP USER User_B;
GO

關鍵避坑總結

1. 端點加密演算法必須一致：雙邊同用 AES (或同用 RC4)，不一致會導致握手失敗。
2. 憑證到期日要足夠長：建議 2099 年，避免過期導致鏡像中斷。
3. TCP 5022 雙向通暢：`telnet IP 5022` 驗證，防火牆、雲端安全群組雙向放行。
4. 備份還原必須 NORECOVERY 且 LSN 連續：任何中斷都要重新完整備份開始。
5. 啟動順序固定：先鏡像端 SET PARTNER，再主體端，否則報錯 1418。
6. 重新部署前務必完全清理：殘留端點/憑證/登入會導致「名稱已存在」或權限衝突。