//create certificate 
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Abc123';
CREATE CERTIFICATE server9_cert WITH SUBJECT = 'mirror certificate' , 
START_DATE = '01/01/2010'; 


//create endpoint 
CREATE ENDPOINT Mirroring 
STATE = STARTED 
AS 
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR 
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE server9_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); 


//backup certificate to disk 
BACKUP CERTIFICATE mirror_cert TO FILE = 'D:\Backup\server9_cert.cer';   


//set remote endpoint login username and password,certificate,remote perfix should change to actural certificate's name 
CREATE LOGIN remote_login WITH PASSWORD = 'Abc123';
CREATE USER remote_user FOR LOGIN remote_login; 
CREATE CERTIFICATE remote_cert AUTHORIZATION remote_user FROM FILE = 'D:\Backup\remote_cert.cer';
GRANT CONNECT ON ENDPOINT::Mirroring TO [remote_login];                         

//query endpoint or mirroring if command execute success 
SELECT * FROM sys.database_mirroring_endpoints 
SELECT * FROM sys.database_mirroring  

//if something fail,can clear previous operation 
Drop endpoint Mirroring  
Drop login principal_login   
drop certificate principal_cert 
drop user principal_user 
Drop MASTER KEY;  


//enable database mirroring 
ALTER DATABASE testmirror SET PARTNER = 'TCP://macau-itech.com:5022'    



//if primary database fail,change the mirror database become to primary database 
user master 
ALTER DATABASE testmirror SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;


//if the original primary database recover,switch the mirror database to original state 
user master 
ALTER DATABASE testmirror SET PARTNER RESUME; 
ALTER DATABASE testmirror SET PARTNER FAILOVER;