[MSSQL] SQL Server – 如何建立 Database Mirroring

shazi7804

11 年前

再參考眾多高可用性 (High Availability) 的架構，在專案的實例上也計畫將這樣的架構加入，其中Failover Clustering中更有Database Mirrioring(鏡像)、Log shipping(交易記錄傳送)、Replication (複寫)

在此篇中我的需求是以資料的重要性及HA高可用的為主，所以我選用的是Database Mirroring的方式進行

由於是HA，也加入見證伺服器使其可以自動容錯移轉。

再參考網路上眾多高手的文章後，在實作上還是需要一步一步除錯才完成，在安全性的考量下連接方式採用＂憑證＂的方式進行。

首先需要三台伺服器

主要伺服器 SQL 2008 R2 Standard版本 ———————文章中代號HOST_A
鏡像伺服器 SQL 2008 R2 Standard版本 ———————文章中代號HOST_B
見證伺服器 SQL 2008 R2 Express SP1以上版本————文章中代號HOST_C

環境說明

HOST_A為主要資料庫，復原模式需要為完整。
HOST_B為鏡像資料庫，在還原過程中無法使用，如果想要使用可透過資料庫快照即可間接使用。
HOST_C為監控A和B的伺服器，想要 Auto Failover 時就需要有監控伺服器來進行，作業系統不拘。
範例資料庫為test

建立 Mirroring
Step.1 將HOST_A的 test 資料庫備份一份到HOST_B上並還原，還原時請選擇『讓資料庫保持不運作』

Step.2 還原完成後資料庫狀態為『正在還原…..』，此時資料庫的部分已經完成，因為採用憑證做認證，接下來不使用鏡像精靈的方式進行安裝，而是使用TSQL。

在HOST_A上建立其他Server 連入的憑證資訊
建立HOST_A憑證

-------建立master資料庫中的主要金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

-------建立憑證 , expiry必須在生效範圍內才可以進行建立端點
USE master;
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate for database mirroring',
EXPIRY_DATE = '11/30/2015';
GO

-------查詢剛剛建立的憑證
select * from sys.certificates

建立HOST_A端點，狀態為STARTED、Listener port=5022，端點名稱為Endpoint_Mirroring

-------建立端點
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_C_cert ,
ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
go

-------查詢端點
SELECT * FROM sys.endpoints;

-------備份HOST_A允許登入的憑證
BACKUP CERTIFICATE HOST_C_cert
TO FILE = 'C:HOST_C_cert.cer';
go

到這邊已經完成HOST_A的憑證建立囉！請在鏡像及見證伺服器都執行一次以上步驟，並將憑證HOST_A改為HOST_B或HOST_C。

在C:下已經產生剛剛備份的憑證，請將此份憑證以最安全的方式copy一份到HOST_B及HOST_C。

待會在連出設定的時候就會用到這些備份檔，請在ABC三台Server上都放置各自的憑證

現在開始建立HOST_B及HOST_C的登入設定，範例為HOST_B，HOST_C比照辦理，只是修改名稱

-------建立登入帳號密碼
USE master;
CREATE LOGIN HOST_B_login
WITH PASSWORD = 'P@ssw0rd';
GO
-------查詢登入
SELECT * FROM sys.server_principals
-------建立登入使用者
USE master;
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
-------查詢使用者
SELECT * FROM sys.sysusers;
-------將HOST_B的憑證與HOST_B的使用者建立登入連接
USE master;
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:HOST_B_cert.cer'
GO
-------檢查連接的憑證
SELECT * FROM sys.certificates
-------將Connect權限及登入授予Endpoint_Mirroring端點
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

這邊所處理的階段是在HOST_A上把HOST_B的憑證加進去HOST_A上，同樣的也把HOST_C的憑證加入到資料庫裡面。

可以查詢憑證檢查一下有沒有把ABC的憑證都加入進去了。

select * from sys.certificates

在HOST_A、B、C上面都加入了憑證後，這樣連線的設定就完成囉！上面的步驟只是完成HOST_A的部分，在HOST_B及C的部分也需要再做一次，這樣可能會不好理解，在這裡解釋一個觀念

HOST_A、B、C上都要建立各自的憑證
HOST_A、B、C都要允許互相可連入(即是將憑證加入)
在執行信任的過程中都會按照流程，憑證 > 端點 > 使用者登入 > 連線進行Mirror

很多問題其實都是在建立信任的階段 (憑證到使用者登入) 就產生的問題，要針對每一階段去做確認才能正確的debug！

進行 Mirror 設定

-------先讓HOST_B進行Mirror really，讓他連入HOST_A
ALTER DATABASE test SET PARTNER ='tcp://HOST_A:5022';

-------再讓HOST_A開始進行Mirror連接
ALTER DATABASE test SET PARTNER ='tcp://HOST_B:5022';

連接完成後可以看到HOST_A的 test 資料庫顯示『主體, 已同步處理』

-------在HOST_A上執行以下SQL把HOST_C加入到這個團隊裡，也就是見證伺服器
ALTER DATABASE test SET WITNESS = 'TCP://HOST_C:5022'

可在資料庫屬性找到鏡像，可以看到剛剛建立的三台都已經在鏡像設定裡面了

※如上圖也是可以用 IP 連接

再來進行一些測試

a. HOST_A中斷時，是否會將DB切到HOST_B去使用？
Ans: yes , loading時間大約30秒內完成

b. HOST_B中斷時，HOST_A是否正常？
Ans: yes , 不影響HOST_A的運作 , 但已沒有備援

c. HOST_C中斷時，HOST_A或HOST_B是否正常連接
Ans: yes , 但已沒有Failover機制 ,可以手動容錯移轉

可以想到一點HOST_C就沒有 Failover，其實可以再新增一台見證伺服器為HOST_D，讓兩台見證伺服器一起監視 Failover。

在執行的過程中，遭遇非常多的問題，有些其實只是一些小問題但卻讓我非常頭痛，以下提供一些遭遇上的問題參考。

Debug：
a. 主體與鏡像伺服器的SQL版本都需要相同，見證伺服器雖然可以使用Express版本，但也需要update到SP1。

b. 在一開始的時候我是使用”鏡像精靈”的方式進行，後來因為安全性改用憑證的方式，在移除鏡像的時候按照精靈的移除步驟去執行”移除鏡像”，是使用TSQL的方式將端點及使用者刪除，所以造成沒有完整移除鏡像，DB也Lock住無法移除。

在這樣的狀況下我繼續將原本的DB用憑證的方式做信任卻遭遇”執行個體無法使用”的狀況。

最後是使用TSQL一步一步將user、login、端點移除後才能使用！

移除用法：

DROP USER user_name
DROP LOGIN login_name
DROP ENDPOINT endPointName
DROP CERTIFICATE certificate_name

以上指令請謹慎使用

參考資料：
鏡像 http://technet.microsoft.com/zh-tw/library/ms179511.aspx
傳出設定 http://technet.microsoft.com/zh-tw/library/ms186384.aspx
傳入設定 http://technet.microsoft.com/zh-tw/library/ms187671.aspx

分享此文：