SqlServer2005数据库镜像实践

来源：互联网发布：西部数据域名批量注册编辑：程序博客网时间：2024/06/04 18:25

个人数据库镜像实践的代码

参考

http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html

http://kb.cnblogs.com/page/45937/

====================== 主機執行

====================== 備機執行

====================== 見證服務器執行

======================================================================

環境：

數據庫：

主體服務器IP:192.168.25.192 Microsoft SQL Server 2005 Standard Edition sp3 /Windows server2003

備份服務器IP:192.168.25.165 Microsoft SQL Server 2005 Standard Edition sp3 /Windows server2003

見證服務器IP:192.168.25.105 Microsoft SQL Server 2005 Express Edition Sp3 /Windows XP

步驟提示：

1.創建完證書后,需要A,B,C三臺機器互相交換證書

2.執行到創建完證書后，需要在備機附加主機數據庫的完全備份

======================================================================

創建證書

--查看數據庫版本

select @@version;

--修改mirror數據庫的備份模式

USE master;

ALTER DATABASE mirror

SET RECOVERY FULL;

--備份mirror數據庫

BACKUP DATABASE mirror

TO DISK = 'c:/mirror/mirror.bak'

WITH FORMAT

drop CERTIFICATE HOST_A_cert

drop MASTER KEY

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jijunwu123';

--為此服務器創建一個證書

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,

START_DATE = '11/17/2009';

----------------------------------------------------------------------------------------------------------------------

drop CERTIFICATE HOST_B_cert

drop MASTER KEY

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jijunwu123';

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',

START_DATE = '11/17/2009';

----------------------------------------------------------------------------------------------------------------------

drop CERTIFICATE HOST_C_cert

drop MASTER KEY

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jijunwu123';

CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate',

START_DATE = '11/17/2009';

======================================================================

創建鏡像端點

drop ENDPOINT Endpoint_Mirroring

--使用證書為服務器創建一個鏡像端點

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL);

----------------------------------------------------------------------------------------------------------------------

drop ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

----------------------------------------------------------------------------------------------------------------------

drop ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = WITNESS );

======================================================================

備份證書

備份證書后需要三臺機器互相交換證書，每臺機器都包括證書A,B,C

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:/mirror/HOST_A_cert.cer';

----------------------------------------------------------------------------------------------------------------------

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:/mirror/HOST_B_cert.cer';

----------------------------------------------------------------------------------------------------------------------

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C:/mirror/HOST_C_cert.cer';

======================================================================

創建關聯用戶

drop CERTIFICATE HOST_B_cert

drop LOGIN HOST_B_login

drop USER HOST_B_user

--在HOST上為HOST_B創建一個登錄名

CREATE LOGIN HOST_B_login WITH PASSWORD = 'jijunwu123';

--創建一個使用該登錄名的用戶

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

--使證書與該用戶關聯

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:/mirror/HOST_B_cert.cer';

--授予对远程镜像端点的登录名的 CONNECT 权限

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

drop CERTIFICATE HOST_C_cert

drop LOGIN HOST_C_login

drop USER HOST_C_user

CREATE LOGIN HOST_C_login WITH PASSWORD = 'jijunwu123';

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'C:/mirror/HOST_C_cert.cer';

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

----------------------------------------------------------------------------------------------------------------------

drop CERTIFICATE HOST_A_cert

drop LOGIN HOST_A_login

drop USER HOST_A_user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'jijunwu123';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:/mirror/HOST_A_cert.cer';

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

drop CERTIFICATE HOST_C_cert

drop LOGIN HOST_C_login

drop USER HOST_C_user

CREATE LOGIN HOST_C_login WITH PASSWORD = 'jijunwu123';

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'C:/mirror/HOST_C_cert.cer';

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

----------------------------------------------------------------------------------------------------------------------

drop CERTIFICATE HOST_A_cert

drop LOGIN HOST_A_login

drop USER HOST_A_user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'jijunwu123';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:/mirror/HOST_A_cert.cer';

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

/**

drop CERTIFICATE HOST_B_cert

drop LOGIN HOST_B_login

drop USER HOST_B_user

CREATE LOGIN HOST_B_login WITH PASSWORD = 'jijunwu123';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:/mirror/HOST_B_cert.cer';

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

======================================================================

創建登錄用戶

主機執行

USE master;

select sid,name from syslogins;

--查詢結果如圖,如果在程序中使用test登錄數據庫

備機執行

USE master;

exec sp_addlogin

@loginame = 'test',

@passwd = 'test用戶的密碼',

@sid = 0xC091ADF7CF65C84183FD67EB206CD835 ;

======================================================================

設置伙伴服務器

備機中執行

ALTER DATABASE mirror SET PARTNER = 'TCP://192.168.25.192:5022';

----------------------------------------------------------------------------------------------------------------------

主機中執行

--在主體中設置備份服務器為伙伴，使備份機做為初始鏡像服務器

ALTER DATABASE mirror SET PARTNER = 'TCP://192.168.25.165:5022';

--設置見證服務器

ALTER DATABASE mirror SET WITNESS = 'TCP://192.168.25.105:5022';

======================================================================

查看狀態

在主/備服務器中分別執行

select mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance,mirroring_witness_name,mirroring_witness_state_desc from sys.database_mirroring

where database_id= (select database_id from sys.databases where name='mirror')

在見證服務器執行

比較重要的，查看 mirroring_witness_state_desc 是否是 CONNECTED

如果不是，可以嘗試從新啟動見證服務器鏡像端點

--重新启动,鏡像端點

ALTER ENDPOINT Endpoint_Mirroring

STATE = STOPPED

-- 等5 秒钟

WAITFOR DELAY '00:00:05'

ALTER ENDPOINT Endpoint_Mirroring

STATE = STARTED

======================================================================

鏡像角色的修改

--主備互換，主機執行

ALTER DATABASE mirror SET PARTNER FAILOVER

======================================================================

服務器故障處理

--備機執行

--主服務器(主體)與見證服務器同時Down掉，備機緊急啟動并且開始服務

USE master;

ALTER DATABASE mirror SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

======================================================================

主機恢復切換狀態

--備機執行

--原來主服務器恢復，可以繼續工作，需要從新設定鏡像

USE master;

--恢復鏡像

ALTER DATABASE BIMS_OFFLINE SET PARTNER RESUME;

--切換主備

ALTER DATABASE BIMS_OFFLINE SET PARTNER FAILOVER;

======================================================================

程序中連接字符串的寫法

Data Source=192.168.25.105;Failover Partner=192.168.25.165;