Site icon Mr. 沙先生

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

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

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

 

 

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

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

首先需要三台伺服器

  1. 主要伺服器 SQL 2008 R2  Standard版本   ———————文章中代號HOST_A
  2. 鏡像伺服器 SQL 2008 R2  Standard版本   ———————文章中代號HOST_B
  3. 見證伺服器 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的部分也需要再做一次,這樣可能會不好理解,在這裡解釋一個觀念

很多問題其實都是在建立信任的階段 (憑證到使用者登入) 就產生的問題,要針對每一階段去做確認才能正確的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

Exit mobile version