SQL2008 2机镜像

清除设置

//删除端点
declare @sql varchar(100)
declare @mirrName varchar(30)
select @mirrName=name from sys.database_mirroring_endpoints
set @mirrName=isnull(@mirrName,'')
if @mirrName<>''
begin
 select @sql='drop endpoint '+@mirrName 
 exec(@sql)
end


//关闭镜像
alter database ZDCtlDB SET PARTNER OFF  

//开启镜像
alter database ZDCtlDB set partner='TCP://192.168.9.6:5022'
//结束正在恢复状态
restore database ZDCtlDB with recovery

 主机上的SQL

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '2017-01-01';

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 = 'C:\working\HOST_A_cert.cer';


CREATE LOGIN HOST_B_login WITH PASSWORD = '123';  --主机上登录到备机的用户
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\working\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];


ALTER DATABASE ZDCtlDB SET PARTNER = 'TCP://192.168.9.1:5022';

镜像机上的SQL

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '2017-01-01';

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 = 'C:\working\HOST_B_cert.cer';


CREATE LOGIN HOST_A_login WITH PASSWORD = '123';  --备机上登录到主机的用户
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\working\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

ALTER DATABASE ZDCtlDB SET PARTNER = 'TCP://192.168.9.6:5022';

 

注意 

1.数据库需要运行在完全模式

2.备份一次数据库，然后再镜子像机上还原，还原选项时，【覆盖】跟【不对数据库执行任何操作，不回滚未提交事物。还可以还原其他事务日志】--即第二项

3.将主备机上的秘钥文件互相copy

 

参考:http://blog.csdn.net/jiajinhao/article/details/8009711