SQL2008 2机镜像

时间：2020-12-25 15:13:00

清除设置

//删除端点

declare @sql varchar()

declare @mirrName varchar()

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 = '';

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= , 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 = '';  --主机上登录到备机的用户

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 = '';

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= , 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 = '';  --备机上登录到主机的用户

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

相关文章