服务镜像搭建_SQLServer带见证服务器的镜像搭建

一、注意点

1、数据库的模式要是完整模式。

2、要对数据库完整备份和事务日志备份，分别还原到镜像库上，使用NORECOVERY模式。

3、镜像数据库是不允许删除和操作，即便查看属性也不行。

4、先删除端点，再删除证书，再删除主密钥。

5、见证服务器不需要还原主体上的数据库。

6、该文档中主体服务器、镜像服务器、见证服务器都没有加入到域中。

7、主体服务器、镜像服务器、见证服务器的操作系统可以不一样，但是SQL版本得一致

二、搭建步骤

1、创建主密钥(主库、镜像库、认证服务器上都执行)

命令：

use mastergocreate master key encryption by password='$a123456'go

查看主密钥

select * from sys.key_encryptions

创建主密钥之前：

创建主密钥之后：

2、分别在主体服务器、镜像服务器、见证服务器上创建证书

命令：

主库上执行：use mastergocreate certificate mirror01_cert with subject='mirror01 certificate',expiry_date='2099-1-1'go镜像库上执行：use mastergocreate certificate mirror02_cert with subject='mirror02 certificate',expiry_date='2099-1-1'go见证服务器上执行use mastergocreate certificate mirror_witness_cert with subject='mirrorWitness certificate',expiry_date='2099-1-1'go

查看证书：

select * from sys.certificates

 

3、分别在主体服务器、镜像服务器、见证服务器上创建端点

命令：

主库上执行：use mastergocreate endpoint Endpoint_Mirroringstate=startedas tcp ( listener_port = 5022,listener_ip = all )for database_mirroring ( authentication = certificate mirror01_cert, encryption = required algorithm aes, role = all )go镜像库上执行：use mastergocreate endpoint Endpoint_Mirroringstate=startedas tcp ( listener_port = 5022,listener_ip = all )for database_mirroring ( authentication = certificate mirror02_cert, encryption = required algorithm aes, role = all )go见证服务器上执行use mastergocreate endpoint Endpoint_Mirroringstate=startedas 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 mastergobackup certificate mirror01_cert to file = 'D:\cert\mirror01_cert.cer'go镜像库上执行：use mastergobackup certificate mirror02_cert to file = 'D:\cert\mirror02_cert.cer'go见证服务器上执行use mastergobackup certificate mirror_witness_cert to file = 'D:\cert\mirror_witness_cert.cer'go

5、创建登录名(这个要和证书关联，所以主体服务器、镜像服务器、见证服务器都要创建除自己以外的另外2个用户)

命令：

主库上执行：--创建镜像库上的证书关联用户use mastergocreate login mirror02_login with password='abc@123456'go--创建见证库上的证书关联用户use mastergocreate login mirror_witness with password='abc@123456'go镜像库上执行：--创建主库上的证书关联用户use mastergocreate login mirror01_login with password='abc@123456'go--创建见证库上的证书关联用户use mastergocreate login mirror_witness with password='abc@123456'go见证服务器执行--创建主库上的证书关联用户use mastergocreate login mirror01_login with password='abc@123456'go--创建镜像库上的证书关联用户use mastergocreate login mirror02_login with password='abc@123456'go

6、创建使用该登录名的用户(主体服务器、镜像服务器、见证服务器都要创建)

命令：

主库上执行--创建镜像库上的证书关联用户use mastergocreate user mirror02_user for login mirror02_logingo--创建见证库上的证书关联用户use mastergocreate user mirror_witness_user for login mirror_witnessgo镜像库上执行--创建主库上的证书关联用户use mastergocreate user mirror01_user for login mirror01_logingo--创建见证库上的证书关联用户use mastergocreate user mirror_witness_user for login mirror_witnessgo见证服务器执行--创建主库上的证书关联用户use mastergocreate user mirror01_user for login mirror01_logingo--创建镜像库上的证书关联用户use mastergocreate user mirror02_user for login mirror02_logingo

7、证书与用户关联

命令：

主库上执行--使镜像库上的证书与用户关联use mastergocreate certificate mirror02_certauthorization mirror02_userfrom file='D:\cert\mirror02_cert.cer'go--使见证库上的证书与用户关联use mastergocreate certificate mirror_witness_certauthorization mirror_witness_userfrom file='D:\cert\mirror_witness_cert.cer'go镜像库上执行--使主库上的证书与用户关联use mastergocreate certificate mirror01_certauthorization mirror01_userfrom file='D:\cert\mirror01_cert.cer'go--使见证库上的证书与用户关联use mastergocreate certificate mirror_witness_certauthorization mirror_witness_userfrom file='D:\cert\mirror_witness_cert.cer'go见证服务器执行--使主库上的证书与用户关联use mastergocreate certificate mirror01_certauthorization mirror01_userfrom file='D:\cert\mirror01_cert.cer'go--使镜像库上的证书与用户关联use mastergocreate certificate mirror02_certauthorization mirror02_userfrom file='D:\cert\mirror02_cert.cer'go

8、授予对远程数据库端点的登录名的CONNECT权限(每个上面2个用户都要有端点的权限)

命令：

主库上执行use mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];gouse mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror_witness];go镜像库上执行use mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];gouse mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror_witness];go见证服务器执行use mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];gouse mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];go

 9、对需要做镜像的数据库做完整备份和事务日志备份，然后在镜像库上做还原(先还原完整备份，再还原事务日志)，使用NORECOVERY模式，这里不做阐述，最后还原后的数据库状态如下：

 10、连接镜像(先在镜像库上操作,然后在主库上操作，不需要在见证数据库上操作)

注：CCAS21、CCAS22、JF-RLZY-DB1是3台机器的机器名

命令：

镜像库上执行 (做完这个操作后,数据库的状态:正在恢复)use mastergoALTER DATABASE test SET PARTNER = 'TCP://CCAS21:5022';go

主库上执行--连接镜像库use mastergoALTER DATABASE test SET PARTNER = 'TCP://CCAS22:5022';go--连接见证服务器use mastergoALTER DATABASE test SET WITNESS = 'TCP://JF-RLZY-DB1:5022';go

 11.查看数据库的状态

(1)主库的状态

 (2)镜像库的状态

三、用到的SQL

1.删除端点drop endpoint Endpoint_Mirroring2.删除证书(所有证书都删除)drop certificate mirror01_cert3.查看证书select * from sys.certificates4.删除主秘钥drop master key5.查询数据库的状态命令：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')或selectmirroring_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 mon d.database_id = m.database_idwhere mirroring_state='1' and m.database_id >4

注：mirroring_state的值为1表示与另一伙伴断开连接