SQL Server 2005 資料庫鏡像完整部署指南

一、資料庫鏡像概述

資料庫鏡像是 SQL Server 2005 引入的高可用性解決方案，其核心機制是將主資料庫（Principal）的交易記錄即時傳送至鏡像資料庫（Mirror），實現資料的熱備份。鏡像資料庫平時處於恢復中狀態，不可直接讀取，僅在主資料庫發生故障時透過容錯移轉接管服務。

核心角色

• 主體伺服器：執行生產工作負載的主要 SQL Server 執行個體。
• 鏡像伺服器：維護資料庫備份複本的次要執行個體。
• 見證伺服器（選用）：第三方執行個體，協助自動容錯移轉決策。採用 2:1 投票機制，僅在需自動故障轉移時部署。

優勢與限制

二、環境準備與互信設定

1. 版本確認與復原模式

SELECT @@VERSION;

確認版本為 Standard/Enterprise/Developer 後，將目標資料庫設為完整復原模式：

USE master;
ALTER DATABASE  SET RECOVERY FULL;

2. 憑證式端點互信（單次設定，適用多資料庫）

採用憑證認證避免網域依賴，步驟如下：

步驟 1：建立主金鑰與憑證（主/鏡並行執行）

-- 主體
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd@2024';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate', START_DATE = '01/01/2024';

-- 鏡像
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd@2024';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE = '01/01/2024';

步驟 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
);

-- 鏡像（憑證名稱對調）

步驟 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 檔案互相複製到對方的 D:\SQLBackup\ 目錄。

步驟 4：建立登入、使用者並授權端點連線

-- 主體執行
CREATE LOGIN HOST_B_login WITH PASSWORD = 'StrongPwd@2024';
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];

-- 鏡像執行（對調名稱）

三、建立鏡像關聯（每資料庫執行一次）

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

主資料庫查詢 SID：

USE master; SELECT sid, name FROM syslogins;

鏡像端以相同 SID 建立登入：

USE master;
EXEC sp_addlogin @loginame = 'app_user', @passwd = 'Pwd@123', @sid = 0x074477739DCA0E499C29394FFFC4ADE4;

2. 初始化鏡像資料庫

1. 主庫完整備份 → 鏡像還原，選 WITH NORECOVERY。
2. 主庫交易記錄備份 → 鏡像還原，同樣選 WITH NORECOVERY。

還原成功後，鏡像資料庫將顯示「正在恢復...」圖示。

3. 啟動鏡像會話

-- 主體執行
ALTER DATABASE [TargetDB] SET PARTNER = 'TCP://10.168.6.45:5022';

-- 若主體失敗，改在鏡像執行
ALTER DATABASE [TargetDB] SET PARTNER = 'TCP://10.168.6.49:5022';

若報錯「交易記錄未同步」，補做一次記錄備份並還原至鏡像後重試。

四、常見操作與測試

注意事項

• Standard 版僅支援同步模式（SAFETY FULL）。
• 見證伺服器若離線，自動容錯移轉將失效，建議監控見證心跳。
• 定期驗證 sys.database_mirroring 狀態欄位（MIRRORING_STATE_DESC）。