个人数据库镜像实践的代码
参考
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
AS
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
AS
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
AS
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
GO
-- 等5 秒钟
WAITFOR DELAY '00:00:05'
GO
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;