1.数据库的模式要是完整模式。
2.要对数据库完整备份和事务日志备份,分别还原到镜像库上,使用NORECOVERY模式。
3.镜像数据库是不允许删除和操作,即便查看属性也不行。
4.先删除端点,再删除证书,再删除主密钥。
5.只有是同步模式的时候,才能手动故障转移,异步模式不能手动故障转移。
二、搭建步骤
1、创建主密钥(主库和镜像库上都执行)
命令:
use master
go
create master key encryption by password=\'$a123456\'
go
查看主密钥
创建主密钥之前:
创建主密钥之后:
2、创建主库和镜像库的证书(分别在主库和镜像库上执行)
命令:
主库上执行:
use master
go
create certificate mirror01_cert with subject=\'mirror01 certificate\',expiry_date=\'2099-1-1\'
go
镜像库上执行:
use master
go
create certificate mirror02_cert with subject=\'mirror02 certificate\',expiry_date=\'2099-1-1\'
go
3、创建主库和镜像库的端点
命令:
主库上执行:
use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate mirror01_cert, encryption = required algorithm aes, role = all )
go
镜像库上执行:
use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate mirror02_cert, encryption = required algorithm aes, role = all )
go
SSMS查看创建的端点
4、备份证书(主库和镜像的库的都备份,并互相拷贝过去,保证每个服务器上都有2个证书)
命令:
主库上执行:
use master
go
backup certificate mirror01_cert to file = \'D:\cert\mirror01_cert.cer\'
go
镜像库上执行:
use master
go
backup certificate mirror02_cert to file = \'D:\cert\mirror02_cert.cer\'
go
5、创建登录名(这个要和证书关联,所以1创建2的,2创建1的)
命令:
主库上执行:
use master
go
create login mirror02_login with password=\'abc@123456\'
go
镜像库上执行:
use master
go
create login mirror01_login with password=\'abc@123456\'
go
6、创建使用该登录名的用户
命令:
主库上执行:
use master
go
create user mirror02_user for login mirror02_login
go
镜像库上执行:
use master
go
create user mirror01_user for login mirror01_login
go