配置完整步骤:
##### slave上创建证书 #####
use master
select * from sys.certificates
-- 创建证书
create master key encryption by password='Sql123456789#';
create certificate mirror_master_cert with subject = 'mirror',
start_date = '2016-1-9',
expiry_date='2199-12-31'
go
-- 创建监听节点
create endpoint [mr-test]
state = started as tcp(listener_port=5022,listener_ip=all)
for database_mirroring
(authentication = certificate mirror_master_cert,
encryption = required algorithm aes,role = all)
go
-- 创建镜像同步账号
create login mirror_account with password = 'Sql12345';
create user mirror_logger for login mirror_account
grant connect on endpoint::[mr-test] to mirror_account
go
-- 备份证书
backup certificate mirror_master_cert to file = 'D:\DBCertificate\mirror_master_cert.cer'
go
##### master上安装证书 #####
use master
create certificate mirror_slave_cert authorization mirror_user
from file = 'D:\DBCertificate\mirror_slave_cert.cer'
##### slave上安装证书 #####
create certificate mirror_master_cert authorization mirror_user
from file = 'D:\DBCertificate\mirror_master_cert.cer'
##### 启动master #####
alter database [Testbackup] set partner = 'TCP://10.63.230.210:5022';
##### 启动slave #####
alter database [Testbackup] set partner = 'TCP://10.41.223.141:5022';
#### 修改监听节点证书 证书可以先不急着创建 ####
alter endpoint [mr-test]
state = started as tcp(listener_port=5022,listener_ip=all)
for database_mirroring
(authentication = certificate mirror_master_cert,
encryption = required algorithm aes,role = all)
go
配置前需要删除原先的配置:
use master
--先删除镜像配置
--删除镜像过程
--删除端点
select * from sys.endpoints;
drop endpoint Endpoint_Mirroring_Test;
--删除证书
select * from sys.certificates
drop certificate mirror_m_cert;
drop certificate mirror_s_cert;
--删除master key
select * from sys.symmetric_keys ;
DROP MASTER KEY;
select * from sys.syslogins ;
select * from sysusers;
--删除登陆和用户
DROP LOGIN mirror_login
drop user mirror_login
--取消镜像
alter database Testbackup set partner off;
关键点:
1. 非域环境下,必须要配置证书
2. 先创建证书和监听节点的绑定,之后哪些数据库做镜像,都可以在这个证书和监听节点上配置