--主機執(zhí)行：
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

--備機執(zhí)行：

USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

--主機執(zhí)行： 

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 ); 

 

--備機執(zhí)行：

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 );

--主機執(zhí)行：

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\SQLBackup\HOST_A_cert.cer'; 


--備機執(zhí)行：

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\SQLBackup\HOST_B_cert.cer';

--主機執(zhí)行：
CREATE LOGIN HOST_B_login WITH PASSWORD = '123456'; 
CREATE USER HOST_B_user FOR LOGIN HOST_B_login; 
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\SQLBackup\HOST_B_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; 
 
--備機執(zhí)行：
CREATE LOGIN HOST_A_login WITH PASSWORD = '123456'; 
CREATE USER HOST_A_user FOR LOGIN HOST_A_login; 
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\SQLBackup\HOST_A_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 

--在備機中執(zhí)行如下語句：

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.155:5022';

說明：shishan為數(shù)據(jù)庫名，需要根據(jù)實際進行修改。192.168.1.155為主機IP地址，需根據(jù)實際進行修改。

--主機執(zhí)行：

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.156:5022'; 

--切換主備 
use master;
alter database testdb set partner failover;
 
-- 備機強制切換
use master;
alter database testdb set partner force_service_allow_data_loss;
 
--恢復鏡像
use master;
alter database testdb set partner resume;
 
 
--取消見證服務器
ALTER DATABASE testdb SET WITNESS OFF ;
 
--取消鏡像
ALTER DATABASE testdb SET PARTNER OFF;
 
--設置鏡像數(shù)據(jù)庫還原為正常
RESTORE DATABASE testdb WITH RECOVERY;