搭建SQL Server數(shù)據(jù)庫鏡像（證書）

本次實驗進行SQL server數(shù)據(jù)庫鏡像的搭建，采用證書的搭建模式

以下是搭建SQL server數(shù)據(jù)庫鏡像的環(huán)境設(shè)置

以192.168.210.125為主體服務(wù)器，192.168.210.126為鏡像服務(wù)器，192.168.210.127為見證服務(wù)器。

還要對三個環(huán)境進行防火墻的設(shè)置，需要打開端口1433和5022

2.1創(chuàng)建證書：

2.1.1先對主體服務(wù)器進行以下操作:

USE master;??

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#';?

GO??

--為主體服務(wù)器 host_A 實例制作一個證書。?

?

--DROP CERTIFICATE host_A_cert?

CREATE CERTIFICATE host_A_cert??

WITH SUBJECT = 'host_A certificate',START_DATE = '6/19/2019',EXPIRY_DATE = '01/01/2099';?

GO??

?

--使用該證書為服務(wù)器實例創(chuàng)建一個鏡像端點。?

?

--DROP ENDPOINT Endpoint_Mirroring?

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 = PARTNER?

);?

GO?

?

--備份host_A 證書，并將其復(fù)制到鏡像服務(wù)器 host_B 和見證服務(wù)器 host_C?

?

BACKUP CERTIFICATE host_A_cert TO FILE = 'c:\DbMirror\host_A.cer';?

GO?

2.1.2在對鏡像服務(wù)器進行操作：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#';

GO

?

--為鏡像服務(wù)器 host_B 實例制作一個證書。

CREATE CERTIFICATE host_B_cert

WITH SUBJECT = 'host_B certificate',START_DATE = '6/19/2015',EXPIRY_DATE = '01/01/2099';

GO

?

--在 host_B 中為服務(wù)器實例創(chuàng)建一個鏡像端點。

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE host_B_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = PARTNER

);

GO

?

--備份 host_B 證書，并將其復(fù)制到主體服務(wù)器 host_A 和見證服務(wù)器 host_C 上

BACKUP CERTIFICATE host_B_cert TO FILE = 'c:\DbMirror\host_B.cer';

GO

2.1.3再對見證服務(wù)器進行操作：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'hzmc321#';

GO

?

?

--為此服務(wù)器實例制作一個證書。

CREATE CERTIFICATE host_C_cert

WITH SUBJECT = 'host_C certificate',START_DATE = '3/12/2015',EXPIRY_DATE = '01/01/2099';

GO

?

--使用該證書為服務(wù)器實例創(chuàng)建一個鏡像端點。

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 = WITNESS

);

GO

?

--備份 host_C 證書，并將其復(fù)制到主體服務(wù)器 host_A 和鏡像服務(wù)器 host_C 上

?

BACKUP CERTIFICATE host_C_cert TO FILE = 'c:\dbmirror\host_C.cer';

GO

2.2配置三者之間的連接：

2.2.1主體服務(wù)器：

--在 host_A 上為鏡像服務(wù)器 host_B 創(chuàng)建一個登錄名。

?

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_B_user FOR LOGIN host_B_login;

GO

?

--使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_B_cert

AUTHORIZATION host_B_user

FROM FILE = 'c:\DbMirror\host_B.cer'

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

?

--在主體服務(wù)器 host_A 上為見證服務(wù)器 host_C 創(chuàng)建一個登錄名。

USE master;

CREATE LOGIN host_C_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_C_user FOR LOGIN host_C_login;

?

GO

?

--使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_C_cert

AUTHORIZATION host_C_user

FROM FILE = 'c:\DbMirror\host_C.cer'

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];

GO

?

--主體服務(wù)器上創(chuàng)建用于本機的登錄名(MS可以省略)

USE master;

CREATE LOGIN host_A_login WITH PASSWORD= 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_A_user FOR CERTIFICATE host_A_cert;

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

2.2.2對鏡像服務(wù)器進行操作

--在鏡像服務(wù)器 host_B 上為主體服務(wù)器 host_A 創(chuàng)建一個登錄名。

?

USE master;

CREATE LOGIN host_A_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_A_user FOR LOGIN host_A_login;

GO

?

--使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_A_cert

AUTHORIZATION host_A_user

FROM FILE = 'c:\Dbmirror\host_A.cer'

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

?

--在鏡像服務(wù)器 host_B 上為見證服務(wù)器 host_C 創(chuàng)建一個登錄名。

?

USE master;

CREATE LOGIN host_C_login WITH PASSWORD = 'hzmc321#';

GO

?

----創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_C_user FOR LOGIN host_C_login;

GO

?

----使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_C_cert

AUTHORIZATION host_C_user

FROM FILE = 'c:\Dbmirror\host_C.cer'

GO

?

----授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];

GO

?

--在鏡像服務(wù)器 host_B 上為本機創(chuàng)建一個登錄名(MS可以省略)

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_B_user FOR CERTIFICATE host_B_cert;

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

2.2.3見證服務(wù)器：

--在見證服務(wù)器 host_C 上為主體服務(wù)器 host_A 創(chuàng)建一個登錄名。

?

USE master;

CREATE LOGIN host_A_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_A_user FOR LOGIN host_A_login;

GO

?

--使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_A_cert

AUTHORIZATION host_A_user

FROM FILE = 'c:\DbMirror\host_A.cer'

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];

GO

?

--在見證服務(wù)器 host_C 上為鏡像服務(wù)器 host_B 創(chuàng)建一個登錄名。

?

USE master;

CREATE LOGIN host_B_login WITH PASSWORD = 'hzmc321#';

GO

?

--創(chuàng)建一個使用該登錄名的用戶。

CREATE USER host_B_user FOR LOGIN host_B_login;

GO

?

--使證書與該用戶關(guān)聯(lián)。

CREATE CERTIFICATE host_B_cert

AUTHORIZATION host_B_user

FROM FILE = 'c:\DbMirror\host_B.cer'

GO

?

--授予對遠程鏡像端點的登錄名的 CONNECT 權(quán)限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];

GO

2.3配置數(shù)據(jù)庫登陸信息

可以自己創(chuàng)建一個新的用于數(shù)據(jù)庫鏡像的登錄名，也可以使用原本有的。

查詢用戶名和對應(yīng)的sid

USE master;

select sid,name from syslogins;

在鏡像數(shù)據(jù)庫執(zhí)行(見證做)

USE master;

exec sp_addlogin

@loginame = 'testname',

@passwd = 'hzmc321#',

@sid = xxxxxxxxxxxxxxxxxxxxxxxxx ;

2.4在主體數(shù)據(jù)庫備份數(shù)據(jù)庫

USE MASTER;

GO

BACKUP DATABASE xxx（目標數(shù)據(jù)庫）

TO DISK = 'c:\DbMirror\DB.bak'

WITH INIT

GO

BACKUP LOG xxx

TO DISK = 'c:\DbMirror\DB_log.bak'

WITH INIT

GO

?

?

2.5在鏡像數(shù)據(jù)庫

還原的時候必須把數(shù)據(jù)庫和事務(wù)日志以NoRecovery的形式還原

再重新還原一次事務(wù)日志

同樣以NoRecovery的形式還原

3.配置伙伴服務(wù)器

執(zhí)行順序為 鏡像------主體-------見證

在鏡像服務(wù)器配置

ALTER DATABASE xxx（目標數(shù)據(jù)庫）

SET PARTNER = 'TCP://192.168.210.125:5022';

GO

在主體服務(wù)器配置

?ALTER DATABASE xxx

SET PARTNER = 'TCP://192.168.210.126:5022';

GO

注釋：這個時候可能會報以下錯誤：

消息 1412，級別 16，狀態(tài) 0，第 1 行

數(shù)據(jù)庫 "test" 的遠程副本尚未前滾到包含在數(shù)據(jù)庫日志的本地副本中的時間點。

解決辦法：重新從主體數(shù)據(jù)庫備份事務(wù)日志并且在鏡像數(shù)據(jù)庫還原。

在主體服務(wù)器配置

ALTER DATABASE [xxx]

SET PARTNER = 'TCP://192.168.210.127:5022';

GO

?

完成階段：數(shù)據(jù)庫處于以下狀態(tài)說明數(shù)據(jù)庫鏡像基本搭建成功

?

創(chuàng)建Sql server 維護計劃：

在下圖紅框區(qū)域內(nèi)選擇或者修改計劃備份的時間

文章來源地址http://www.zghlxwxcb.cn/news/detail-822170.html

• 維護計劃
• 實驗階段
• 環(huán)境設(shè)置