SQL 2008 配置镜像执行SQL

  主机执行:

  

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ics2012%Qaz.';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
EXPIRY_DATE = '01/01/2280';

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

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

CREATE LOGIN HOST_B_login WITH PASSWORD = 'ics2012%Qaz.';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'E:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];


ALTER DATABASE ICSDB SET PARTNER = 'TCP://192.168.5.14:5022';


--DROP  ENDPOINT Endpoint_Mirroring 
--DROP CERTIFICATE HOST_A_cert
--DROP CERTIFICATE HOST_B_cert
--DROP USER HOST_A_user
--DROP MASTER KEY
--SELECT * FROM sys.database_principals

  

  备机执行:

 

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ics2012%Qaz.';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
EXPIRY_DATE = '01/01/2280';

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


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

CREATE LOGIN HOST_A_login WITH PASSWORD = 'ics2012%Qaz.';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];



--DROP  ENDPOINT Endpoint_Mirroring 
--DROP CERTIFICATE HOST_A_cert
--DROP CERTIFICATE HOST_B_cert
--DROP USER HOST_A_user
--DROP MASTER KEY
--SELECT * FROM sys.database_principals

 

  如果主机做个镜像，必须取消镜像，同时执行以下命令： 

--DROP  ENDPOINT Endpoint_Mirroring
--DROP CERTIFICATE HOST_A_cert
--DROP CERTIFICATE HOST_B_cert
--DROP USER HOST_A_user
--DROP MASTER KEY

最后删除用户：HOST_A_user，HOST_B_user

 

  如果提示无法配置成功，可能是5022端口被封，可以使用其他未用的端口，如：8080等。