SQL Server 2005 資料庫鏡像完整建置手冊：憑證模式、手動同步與故障切換演練

鏡像架構概覽

• Principal（主體）：服務生產流量的主資料庫
• Mirror（鏡像）：待命備份資料庫，不可直接存取
• Witness（見證）：選用，提供自動容錯移轉的仲裁投票（需 2/3 多數）

優缺點快評

前置條件

• 主/備皆為 Standard、Enterprise 或 Developer 版（Express 僅能作見證）
• 資料庫復原模式設為 FULL

  USE master;
  ALTER DATABASE  SET RECOVERY FULL;

• 主/備機器網路互通，防火牆開放鏡像端點埠（預設 5022）

階段一：主備實例互信（憑證模式，僅需做一次）

1. 建立主金鑰與憑證

-- 主機
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate', START_DATE = '01/01/2008';

-- 備機
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE = '01/01/2008';

2. 建立鏡像端點

-- 主機
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(AUTHENTICATION = CERTIFICATE HOST_A_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

-- 備機（憑證換為 HOST_B_cert）

3. 備份並交換憑證

-- 主機
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
-- 備機
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
-- 將 .cer 檔跨機器複製到對方同路徑

4. 建立登入、使用者、授權端點連線

-- 主機
CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

-- 備機（對稱操作，換成 HOST_A_*）

階段二：建立單一資料庫鏡像（每個 DB 重複執行）

1. 同步登入（避免孤立使用者）

-- 主機查詢 SID
USE master;
SELECT sid, name FROM syslogins WHERE name = 'myuser';

-- 備機建立相同 SID 登入
USE master;
EXEC sp_addlogin @loginame = 'myuser', @passwd = 'password', @sid = 0x074477739DCA0E499C29394FFFC4ADE4;

2. 備機還原資料庫（WITH NORECOVERY）

從主機完整備份還原，選項勾選 RESTORE WITH NORECOVERY。

3. 建立鏡像夥伴關係

-- 主機執行
ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.45:5022';

-- 若主機失敗，嘗試備機執行
ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.49:5022';

成功後 SSMS 會顯示鏡像圖示。

4. 若報錯「交易記錄未同步」

• 主機執行一次 交易記錄備份
• 備機以 RESTORE WITH NORECOVERY 還原該記錄備份
• 再次執行 ALTER DATABASE ... SET PARTNER

階段三：故障切換演練指令

1. 手動容錯移轉（主動切換）

-- 主機
USE master;
ALTER DATABASE  SET PARTNER FAILOVER;

2. 主機宕機，備機強制服務（允許資料遺失）

-- 備機
USE master;
ALTER DATABASE  SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3. 原主機修復後，恢復鏡像並切回

-- 備機（現為主體）
USE master;
ALTER DATABASE  SET PARTNER RESUME;   -- 恢復鏡像會話
ALTER DATABASE  SET PARTNER FAILOVER; -- 切回原主機

4. 安全性模式切換

-- 同步模式（預設，Standard 僅支援此模式）
ALTER DATABASE  SET PARTNER SAFETY FULL;
-- 非同步模式（效能最佳，可能資料遺失）
ALTER DATABASE  SET PARTNER SAFETY OFF;