SQLServer2012高可用映象資料庫

實施方案(非域環境)

目錄

SQLServer2012 高可用映象資料庫 ... 1

實施方案 ( 非域環境 ) 1

一：環境準備 ... 2

1.1 伺服器、資料庫準備 ... 2

1.2 建立目錄 ... 2

1.3 環境檢查 ... 2

二：執行相關指令碼 ... 2

2.1 建立主資料庫金鑰 ... 3

2.2 建立證照 ... 3

2.3 建立端點 ... 3

2.4 備份並分發證照 ... 3

2.5 建立登陸賬號 ... 3

2.6 建立使用者 ... 3

2.7 授權使用者 ... 3

2.8 授權埠 ... 3

三：資料初始化 ... 3

3.1 備份資料庫 ( 主 ) 3

3.2 還原資料庫 ( 映象 ) 3

四：配置映象 ... 4

五：故障轉移測試 ... 8

5.1 檢視主庫、映象庫狀態 ... 8

5.2 手動切換 ... 9

5.3 自動切換 ... 12

六：檢視資料庫映象監視器 ... 15

七：搭建過程中遇到的問題 ... 16

八：參考 ... 16

九：應用客戶端連線資料庫 ... 17

十：指令碼附件 ... 17

 

一：環境準備

                                             

主資料庫、映象資料庫、見證伺服器之間的身份驗證方式

一：域帳戶

二：證照

本次採用證照驗證方式；

所有資料庫映象會話都只支援一臺主體伺服器和一臺映象伺服器。

1.1 伺服器、資料庫準備

IP 、主機、密碼等敏感資訊已脫敏

伺服器角色	主機名	版本	IP
主體伺服器	principal	Windows server 2012	192.168.100.101
映象伺服器	mirror	Windows server 2012	192.168.100.102
見證伺服器	witness	Windows server 2012	192.168.100.103

 

1.2 建立目錄

三臺伺服器本地，分別建立 D:\sharedir 目錄，並設定共享 , 許可權 everyone 可讀寫；

三臺伺服器本地，分別建立 D:\certifications 目錄，用於匯出證照；

1.3 環境檢查

(1) 檢查三臺伺服器網路和埠是否測通；

(2) 檢查三臺伺服器防火牆是否關閉，或 1023,5022 等埠是否放開；

(3) 檢查三臺伺服器上資料庫版本和補丁版本是否一致；

二：執行相關指令碼

具體指令碼內容見最後 十：指令碼附件

2.1 建立主資料庫金鑰

2.2 建立證照

2.3 建立端點

2.4 備份並分發證照

2.5 建立登陸賬號

2.6 建立使用者

2.7 授權使用者

2.8 授權埠

三：資料初始化

3.1 備份資料庫 ( 主 )

主庫備份資料庫和日誌，並將備份檔案備註到映象資料庫伺服器上

use master

BACKUP DATABASE testdb

TO DISK = 'D:\sharedir\testdb0730.bak'

WITH COMPRESSION

GO

use master

BACKUP LOG testdb

TO DISK = 'D:\sharedir\testlog0730.trn'

GO

3.2 還原資料庫 ( 映象 )

還原資料庫和日誌 (RESTORE WITH NORECOVERY)

四：配置映象

在主資料庫上操作

五：故障轉移測試

5.1 檢視主庫、映象庫狀態

主庫

映象庫

5.2 手動切換

在主庫上執行

映象資料庫 102 ，已經切換成主庫

102 切換回來映象資料庫

5.3 自動切換

101 主故障，測試自動切換

101 啟動防火牆， 1023 和 5022 不通

use  testdb

102

use testdb

select count (*) from a0730 ;

--101 關閉防火牆

use testdb

---102

use testdb

select * from test0730 ;

select count (*) from a0730 ;

insert into test0730 values ( 7 );

update test0730 set id = 100 where id = 1 ;

手動將主切回 101

---101

use testdb

select * from test0730 ;

六：檢視資料庫映象監視器

七：搭建過程中遇到的問題

問題一：

伺服器網路地址 :TCP://192.168.100.102:5022 無法訪問或不存在。

資料庫 testdb 的夥伴伺服器例項和見證伺服器例項均不可用。

問題原因：

主伺服器上啟用了防火牆，並且 1023 和 5022 埠沒有開放。

 

解決方案：

關閉防火牆，重新點選 開始映象

八：應用客戶端連線資料庫

Microsoft .NET Data Provider for SQL Server 提供了對資料庫映象會話的客戶端連線支援。

九：參考

https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

資料庫映象 (SQL Server)

https://blog.csdn.net/dba_huangzj/article/details/27652857

第三篇——第二部分——第四文 配置 SQL Server 映象——非域環境

十：指令碼附件

---101 主伺服器

-- 建立主金鑰

USE master ;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否則不要設定這麼簡單的密碼

GO

/*

-- 刪除主金鑰

USE master;

DROP  MASTER KEY

*/

--- 建立證照

USE master ;

CREATE CERTIFICATE HOST_A_cert

   WITH SUBJECT = 'HOST_A certificate' -- 在主例項上建立證照，命名為HOST_A_cert，這個選項是描述證照

   , EXPIRY_DATE = '2999-1-1' ; -- 證照過期時間，可以適當設定長一點，具體按實際需要設定

GO

/*

-- 刪除證照

USE master;

DROP CERTIFICATE HOST_A_cert

*/

--- 建立端點

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (

      LISTENER_PORT = 5022   -- 使用5022埠，這個埠可以改成未被使用的埠，但是映象過程中的所有合作者都應該使用相同的埠

      , LISTENER_IP = ALL

   )

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE HOST_A_cert    -- 使用證照來授權端點

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE = ALL         -- 表示這個端點可以作為任何角色，包括主伺服器、映象伺服器、見證伺服器。具體可看聯機叢書。

   );

GO

/*

-- 刪除映象端點

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')

DROP ENDPOINT [Endpoint_Mirroring]

GO

*/

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\certifications\HOST_A_cert.cer' ;

GO

 

-- 在主例項上建立一個登入名給Mirror例項

USE master ;

CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_B_cert

   AUTHORIZATION HOST_B_user

   FROM FILE = 'D:\Certifications\HOST_B_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;

GO

/*

-- 刪除賬號

DROP LOGIN HOST_B_user

*/

 

-- 在主例項上建立一個登入名給見證例項

USE master ;

CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_C_cert

   AUTHORIZATION HOST_C_user

   FROM FILE = 'D:\Certifications\HOST_C_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;

 

---102 映象伺服器

-- 建立主金鑰

USE master ;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否則不要設定這麼簡單的密碼

GO

/*

-- 刪除主金鑰

USE master;

DROP  MASTER KEY

*/

USE master ;

CREATE CERTIFICATE HOST_B_cert

   WITH SUBJECT = 'HOST_B certificate' -- 在Winess例項上建立證照，命名為HOST_C_cert，這個選項是描述證照

   , EXPIRY_DATE = '2999-1-1' ; -- 證照過期時間，可以適當設定長一點，具體按實際需要設定

GO

/*

-- 刪除證照

USE master;

DROP CERTIFICATE HOST_B_cert

*/

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (

      LISTENER_PORT = 5022     -- 使用5022埠，這個埠可以改成未被使用的埠，但是映象過程中的所有合作者都應該使用相同的埠

      , LISTENER_IP = ALL

   )

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE HOST_B_cert    -- 使用證照來授權端點

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE = ALL          -- 表示這個端點可以作為任何角色，包括主伺服器、映象伺服器、見證伺服器。具體可看聯機叢書。

   );

GO

/*

-- 刪除映象端點

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')

DROP ENDPOINT [Endpoint_Mirroring]

GO

*/

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\certifications\HOST_B_cert.cer' ;

GO

 

-- 在映象例項上建立一個登入名給Principal例項

USE master ;

CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_A_cert

   AUTHORIZATION HOST_A_user

   FROM FILE = 'D:\Certifications\HOST_A_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;

GO

/*

-- 刪除賬號

DROP LOGIN HOST_A_user

*/

 

USE master ;

CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_C_cert

   AUTHORIZATION HOST_C_user

   FROM FILE = 'C:\Certifications\HOST_C_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;

 

 

---103 見證伺服器

-- 建立主金鑰

USE master ;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否則不要設定這麼簡單的密碼

GO

/*

-- 刪除主金鑰

USE master;

DROP  MASTER KEY

*/

USE master ;

CREATE CERTIFICATE HOST_C_cert

   WITH SUBJECT = 'HOST_C certificate' -- 在Winess例項上建立證照，命名為HOST_C_cert，這個選項是描述證照

   , EXPIRY_DATE = '2999-1-1' ; -- 證照過期時間，可以適當設定長一點，具體按實際需要設定

GO

/*

-- 刪除證照

USE master;

DROP CERTIFICATE HOST_C_cert

*/

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (

      LISTENER_PORT = 5022    -- 使用5022埠，這個埠可以改成未被使用的埠，但是映象過程中的所有合作者都應該使用相同的埠

      , LISTENER_IP = ALL

   )

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE HOST_C_cert    -- 使用證照來授權端點

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE = ALL       -- 表示這個端點可以作為任何角色，包括主伺服器、映象伺服器、見證伺服器。具體可看聯機叢書。

   );

GO

/*

-- 刪除映象端點

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')

DROP ENDPOINT [Endpoint_Mirroring]

GO

*/

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\certifications\HOST_C_cert.cer' ;

GO

 

--- 在見證伺服器上為主體、映象伺服器建立以證照為驗證的賬號、使用者名稱及端點。

-- 在Witness例項上建立一個登入名給Principal例項

USE master ;

CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_A_cert

   AUTHORIZATION HOST_A_user

   FROM FILE = 'D:\certifications\HOST_A_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;

GO

/*

-- 刪除賬號

DROP LOGIN HOST_A_user

*/

-- 在Witness例項上建立一個登入名給Mirror例項

USE master ;

CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;

GO

-- 建立一個用於給這個登入名

CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;

GO

-- 讓該帳號使用證照授權

CREATE CERTIFICATE HOST_B_cert

   AUTHORIZATION HOST_B_user

   FROM FILE = 'D:\certifications\HOST_B_cert.cer'

GO

-- 授予這個新賬號連線端點的許可權

GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;

GO

/*

-- 刪除賬號

DROP LOGIN HOST_B_user

*/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2652387/，如需轉載，請註明出處，否則將追究法律責任。