28-SQLServer带见证服务器的镜像搭建
一、注意点
1.数据库的模式要是完整模式。
2.要对数据库完整备份和事务日志备份，分别还原到镜像库上，使用NORECOVERY模式。
3.镜像数据库是不允许删除和操作，即便查看属性也不行。
4.先删除端点，再删除证书，再删除主密钥。
5.见证服务器不需要还原主体上的数据库。
6.该文档中主体服务器、镜像服务器、见证服务器都没有加入到域中。
7.主体服务器、镜像服务器、见证服务器的操作系统可以不一样，但是SQL版本得一致
二、搭建步骤
1、创建主密钥（主库、镜像库、认证服务器上都执行）
命令：
use master
go
create master key encryption by password='$a123456'
go
查看主密钥
select * from sys.key_encryptions
创建主密钥之前：
创建主密钥之后：
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
--见证服务器上执行
use master
go
create certificate mirror_witness_cert with subject='mirrorWitness certificate',expiry_date='2099-1-1'
go
查看证书：
select * from sys.certificates
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
--见证服务器上执行
use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate mirror_witness_cert, encryption = required algorithm aes, role = all )
go
SSMS查看创建的端点
4、备份证书（主体服务器、镜像服务器、见证服务器都备份，并互相拷贝过去，保证每个服务器上都有3个证书）
命令：
主库上执行：
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
--见证服务器上执行
use master
go
backup certificate mirror_witness_cert to file = 'D:\cert\mirror_witness_cert.cer'
go
5、创建登录名（这个要和证书关联，所以主体服务器、镜像服务器、见证服务器都要创建除自己以外的另外2个用户）
命令：
主库上执行：
--创建镜像库上的证书关联用户
use master
go
create login mirror02_login with password='abc@123456'
go
--创建见证库上的证书关联用户
use master
go
create login mirror_witness with password='abc@123456'
go
镜像库上执行：
--创建主库上的证书关联用户
use master
go
create login mirror01_login with password='abc@123456'
go
--创建见证库上的证书关联用户
use master
go
create login mirror_witness with password='abc@123456'
go
见证服务器执行
--创建主库上的证书关联用户
use master
go
create login mirror01_login with password='abc@123456'
go
--创建镜像库上的证书关联用户
use master
go
create login mirror02_login with password='abc@123456'
go
6、创建使用该登录名的用户（主体服务器、镜像服务器、见证服务器都要创建）
命令：
主库上执行
--创建镜像库上的证书关联用户
use master
go
create user mirror02_user for login mirror02_login
go
--创建见证库上的证书关联用户
use master
go
create user mirror_witness_user for login mirror_witness
go
镜像库上执行
--创建主库上的证书关联用户
use master
go
create user mirror01_user for login mirror01_login
go
--创建见证库上的证书关联用户
use master
go
create user mirror_witness_user for login mirror_witness
go
见证服务器执行
--创建主库上的证书关联用户
use master
go
create user mirror01_user for login mirror01_login
go
--创建镜像库上的证书关联用户
use master
go
create user mirror02_user for login mirror02_login
go
7、证书与用户关联
命令：
主库上执行
--使镜像库上的证书与用户关联
use master
go
create certificate mirror02_cert
authorization mirror02_user
from file='D:\cert\mirror02_cert.cer'
go
--使见证库上的证书与用户关联
use master
go
create certificate mirror_witness_cert
authorization mirror_witness_user
from file='D:\cert\mirror_witness_cert.cer'
go
镜像库上执行
--使主库上的证书与用户关联
use master
go
create certificate mirror01_cert
authorization mirror01_user
from file='D:\cert\mirror01_cert.cer'
go
--使见证库上的证书与用户关联
use master
go
create certificate mirror_witness_cert
authorization mirror_witness_user
from file='D:\cert\mirror_witness_cert.cer'
go
见证服务器执行
--使主库上的证书与用户关联
use master
go
create certificate mirror01_cert
authorization mirror01_user
from file='D:\cert\mirror01_cert.cer'
go
--使镜像库上的证书与用户关联
use master
go
create certificate mirror02_cert
authorization mirror02_user
from file='D:\cert\mirror02_cert.cer'
go
8、授予对远程数据库端点的登录名的CONNECT权限(每个上面2个用户都要有端点的权限)
命令：
主库上执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];
go
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror_witness];
go
镜像库上执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];
go
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror_witness];
go
见证服务器执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];
go
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];
go
9、对需要做镜像的数据库做完整备份和事务日志备份，然后在镜像库上做还原（先还原完整备份，再还原事务日志），使用NORECOVERY模式，这里不做阐述，最后还原后的数据库状态如下：
10.连接镜像(先在镜像库上操作,然后在主库上操作，不需要在见证数据库上操作)
注：CCAS21、CCAS22、JF-RLZY-DB1是3台机器的机器名
命令：
镜像库上执行 (做完这个操作后,数据库的状态:正在恢复)
use master
go
ALTER DATABASE test SET PARTNER = 'TCP://CCAS21:5022';
go
主库上执行
--连接镜像库
use master
go
ALTER DATABASE test SET PARTNER = 'TCP://CCAS22:5022';
go
--连接见证服务器
use master
go
ALTER DATABASE test SET WITNESS = 'TCP://JF-RLZY-DB1:5022';
go
11.查看数据库的状态
（1）主库的状态
（2）镜像库的状态
三、用到的SQL
--1.删除端点
drop endpoint Endpoint_Mirroring
--2.删除证书(所有证书都删除)
drop certificate mirror01_cert
--3.查看证书
select * from sys.certificates
--4.删除主秘钥
drop master key
--5.查询数据库的状态
命令：
select
mirroring_role_desc, --数据库在镜像会话中的角色
mirroring_state_desc, --镜像当前状态
mirroring_safety_level_desc, --镜像运行模式
mirroring_witness_state_desc --与见证服务器的连接情况
from sys.database_mirroring where database_id=DB_ID(N'test')
或
select
mirroring_role_desc, --数据库在镜像会话中的角色
mirroring_state, --镜像数据库的状态，0-已挂起，1-于其他伙伴断开，2-正在同步，3-挂起故障转移，4-已同步
mirroring_state_desc, --镜像当前状态
mirroring_safety_level_desc, --镜像运行模式
mirroring_safety_level , --数据库的同步模式，1是异步，2是同步
mirroring_connection_timeout, --镜像连接超时时间,默认是10秒
mirroring_failover_lsn --最新事务日志记录的日志序列号
from sys.database_mirroring where database_id=DB_ID(N'APPROVE')
主库上执行
镜像库上执行
6、监控的语句
命令：
select d.name as dbname, m.mirroring_state from sys.databases d
inner join sys.database_mirroring m
on d.database_id = m.database_id
where mirroring_state='1' and m.database_id >4
注：mirroring_state的值为1表示与另一伙伴断开连接
