[SQL]在 Workgroup 環境下設定 SQL Server 資料庫鏡像 ( Database Mirroring )

2015-06-16

前一陣子整理一份 Log Shipping 的架設文件，在 FB 上有朋友詢問到，是否可以同時搭配 Database Mirroring + Log Shipping，因此花了一點時間來準備 Mirroring 的環境，順利整理出來以免後續要架設的時候又忘記了。

由於在 Workgroup 的環境下，雖然有人介紹過在兩邊都使用相同帳密，就可以不用麻煩的去設定憑證，但這個方式我沒有測試成功，所以就先整理透過憑證的方式來處理，因此過程中就會比較複雜一點，使用較多的指令碼來進行。

首先我先架設兩個 VM 來做 Lab , 都安裝好 Windows 2012 R2 Standard + SQL Server 2012 Standard 的版本，資訊如下 :

第一台

電腦名稱 : Primary

網路位址 : 10.40.40.76

角色 : Principal Server

第二台

電腦名稱 : Standby1

網路位址 : 10.40.40.77

角色 : Mirroring Server

由於要先建立憑證，因此我先在 Primary 的環境下，用以下的指令碼來建立一組憑證和端點，並且將憑證給備份下來，等一下要複製到 Standby1 的電腦上使用。


-- Primary
SELECT @@SERVERNAME,* FROM sys.symmetric_keys;
GO

-- ##MS_DatabaseMasterKey##
CREATE MASTER KEY ENCRYPTION BY Password = '!QAZ2wsx'
GO

-- 建立憑證
CREATE CERTIFICATE Primary_Mirror_Cert
	WITH SUBJECT = 'DB Mirroring Principal Server Certificate',
	EXPIRY_DATE = '12/31/2100'
 GO

 -- 查看憑證
 SELECT * FROM sys.certificates WHERE name = 'Primary_Mirror_Cert'
 GO

 -- 建立端點
 CREATE ENDPOINT [鏡像] STATE = STARTED 
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATABASE_MIRRORING
 (
	AUTHENTICATION = CERTIFICATE Primary_Mirror_Cert,
	ENCRYPTION = REQUIRED ALGORITHM RC4,
	ROLE = ALL
 )
 GO

 -- 備份憑證
 BACKUP CERTIFICATE Primary_Mirror_Cert 
	TO FILE = 'C:\Temp\Primary_Mirror_Cert.cer'
 GO

在 Standby1 的電腦上，也類似上述的指令，只是把憑證名稱改一下，免得混淆。產生之後我們一樣將憑證備份出來，這個憑證就要複製到 Primary 的電腦上使用了


-- StandBy1
SELECT @@SERVERNAME,* FROM sys.symmetric_keys;
GO

-- ##MS_DatabaseMasterKey##
CREATE MASTER KEY ENCRYPTION BY Password = '!QAZ2wsx'
GO

-- 建立憑證
CREATE CERTIFICATE StandBy1_Mirror_Cert
	WITH SUBJECT = 'DB Mirroring Mirroring Server Certificate',
	EXPIRY_DATE = '12/31/2100'
 GO

 -- 查看憑證
 SELECT * FROM sys.certificates WHERE name = 'StandBy1_Mirror_Cert'
 GO

 -- 建立端點
 CREATE ENDPOINT [鏡像] STATE = STARTED 
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATABASE_MIRRORING
 (
	AUTHENTICATION = CERTIFICATE StandBy1_Mirror_Cert,
	ENCRYPTION = REQUIRED ALGORITHM RC4,
	ROLE = ALL
 )
 GO

 -- 備份憑證
 BACKUP CERTIFICATE StandBy1_Mirror_Cert 
	TO FILE = 'C:\Temp\StandBy1_Mirror_Cert.cer'
 GO

這些準備工作完成之後，就可以把備份出來的憑證複製到另外一台去，接下來我們就可以來處理權限的問題了。

首先在 Primary 這一台上，利用以下的程式碼來處理。透過由 Standby1 所複製過來的憑證建立一個可以連接 Primary 端點的帳號


-- PRIMARY
SELECT @@SERVERNAME

-- 建立登入
CREATE LOGIN StandBy1_login WITH 
	PASSWORD='!QAZ2wsx', 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF
GO

-- 建立使用者
CREATE USER StandBy1_User FOR LOGIN StandBy1_login;
GO

-- 建立憑證 ( 
CREATE CERTIFICATE StandBy1_Mirror_Cert
	AUTHORIZATION StandBy1_User
	FROM FILE = 'C:\Temp\StandBy1_Mirror_Cert.cer'
GO

 -- 賦予另外一台可以連接端點的權限
GRANT CONNECT On ENDPOINT::[鏡像] TO [StandBy1_login]
GO

用同樣類似的指令，我們在 Standby1 這一台上，則是另外由 Primary 所複製過來的憑證建立一個可以連接 Standby1 端點的帳號


-- STANDBY1
SELECT @@SERVERNAME

-- 建立登入
CREATE LOGIN Primary_login WITH 
	PASSWORD='!QAZ2wsx', 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF
GO

-- 建立使用者
CREATE USER Primary_User FOR LOGIN Primary_login;
GO

-- 建立憑證 
CREATE CERTIFICATE Primary_Mirror_Cert
	AUTHORIZATION Primary_User
	FROM FILE = 'C:\Temp\Primary_Mirror_Cert.cer'
GO

 -- 賦予另外一台可以連接端點的權限
GRANT CONNECT On ENDPOINT::[鏡像] TO [Primary_login]
GO

透過上述的方式，我們已經將兩台電腦可以有互相信任的帳號連接對方的端點，那麼就可以來進行資料庫上的處理了。因此這裡我們可以透過 GUI 或者是 T-SQL 的指令，將要設定 Mirroring 的資料庫給備份，並且將這些檔案複製到 Standby1 的電腦上。


-- 備份測試資料庫
BACKUP DATABASE MirrorDemo TO DISK='C:\Temp\MirrorDemo_20150616.BAK' 
	WITH COMPRESSION ;
GO

-- 備份測試資料庫
BACKUP LOG MirrorDemo TO DISK='C:\Temp\MirrorDemo_20150616.TRN' 
	WITH COMPRESSION ;
GO

複製過來之後，就可以在 Standby1 的這台上進行還原資料庫的處理，此時要記得在還原資料庫的時候，加入 NORECOVERY 的參數


-- STANDBY1
SELECT @@SERVERNAME

-- 還原資料庫
USE [master]
RESTORE DATABASE [MirrorDemo] FROM  
	DISK = N'C:\Temp\MirrorDemo_20150616.BAK' WITH  NORECOVERY;
GO

-- 還原交易紀錄
RESTORE LOG [MirrorDemo] FROM  
	DISK = N'C:\Temp\MirrorDemo_20150616.TRN' WITH  NORECOVERY;
GO

接下來就要設定最後一個步驟，設定兩邊的資料庫同步，可以透過指令或者是 GUI 來做，原本自己做的時候搞錯了一些設定，造成使用指令去處理會有問題，謝謝 Rock 的提醒，剛剛測試一下是沒有問題的，完全是自己耍白癡，只要記得先到 Standby1 進行設定，設定完再回到 Primary 設定就可以了。

先在 Standby1 的這台上進行設定和 Primary 同步


-- STANDBY1
SELECT @@SERVERNAME

-- 設定跟 Primary 同步
ALTER DATABASE [MirrorDemo]
    SET PARTNER = 'TCP://Primary:5022'
GO

接著在 Primary 這台上進行設定和 Standby1 同步


-- Primary
SELECT @@SERVERNAME

-- 設定跟 Standby1 同步
ALTER DATABASE [MirrorDemo]
    SET PARTNER = 'TCP://Standby1:5022'
GO

最後設定如果懶的處理，也可以用 SSMS 透過精靈來做，但似乎也省不了多少功夫。直接使用 SSMS 上來完成後續的設定，我們在要設定的資料庫上，選擇「工作」→「鏡像」，選擇透過精靈的方式來做設定。

因為測試環境中主要焦點是針對權限，因此我們只考慮兩台的環境來做處理

透過「連接」的按鈕設定鏡像伺服器的參數

設定好之後，因為相關端點和權限我們已經在前面預備的時候，就已經建立好了，因此這個部分就保持空白不用去設定。

安裝好之後 SSMS 就會詢問你是否要啟動鏡像，這裡我們就選擇「啟動鏡像」

按下「是」之後，過了幾秒鐘之後，就可以看到同步的訊息了。

而平常我們如果要查看他的運作狀況，也可以透過「資料庫鏡像監視器」，很方便的清楚目前鏡像的狀態。

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[SQL]在 Workgroup 環境下設定 SQL Server 資料庫鏡像 ( Database Mirroring )

系列文章

標籤雲