证书上的服务器名错误_SQLServer使用证书认证的方式搭建数据库镜像

一、总结

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

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

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

4.删除的时候要先删除端点，再删除证书，再删除主密钥。

5.只有是同步模式的时候，才能手动故障转移，异步模式不能手动故障转移。

二、搭建步骤

1、创建主密钥(主库和镜像库上都执行)

命令：

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

查看主密钥

创建主密钥之前：

创建主密钥之后：

 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

 

 

  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

SSMS查看创建的端点

 4、备份证书(主库和镜像的库的都备份，并互相拷贝过去，保证每个服务器上都有2个证书)

命令：

主库上执行：

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

 

 5、创建登录名(这个要和证书关联，所以1创建2的，2创建1的)

命令：

主库上执行：

use mastergocreate login mirror02_login with password='abc@123456'go

镜像库上执行：

use mastergocreate login mirror01_login with password='abc@123456'go

 6、创建使用该登录名的用户

命令：

主库上执行：

use mastergocreate user mirror02_user for login mirror02_logingo

镜像库上执行：

use mastergocreate user mirror01_user for login mirror01_logingo

 7、证书与用户关联

命令：

主库上执行：

use mastergocreate certificate mirror02_certauthorization mirror02_userfrom file='D:\cert\mirror02_cert.cer'go

镜像库上执行：

use mastergocreate certificate mirror01_certauthorization mirror01_userfrom file='D:\cert\mirror01_cert.cer'go

 

8、授予对远程数据库端点的登录名的CONNECT权限

命令：

主库上执行：

use mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];go

镜像库上执行：

use mastergoGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];go

 

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

 10.连接镜像(先在镜像库上操作,然后在主库上操作)

注：sqlmirror01和sqlmirror02是2台机器的机器名

命令：

镜像库上执行：(做完这个操作后,数据库的状态:正在恢复)

use mastergoALTER DATABASE test SET PARTNER = 'TCP://sqlmirror01:5022';go

主库上执行：(做完这个操作,镜像就搭建好了)

use mastergoALTER DATABASE test SET PARTNER = 'TCP://sqlmirror02:5022';go

此时主库的状态：

 镜像库的状态：

 镜像的状态：(只能在主库上右键-->属性查看)

 注：如果状态不对，先刷新一下SSMS

11.查看的选项

(1)启用数据库监视器查看镜像是否正常(主库上查看)

 (2)查看镜像服务器是否授权主库服务器

 12 .主库宕机后，怎么恢复镜像库为可用状态

1、第一步:脱离镜像alter database test set partner off2、第二步;修改数据库的状态为recoveryrestore database test with recovery

三、涉及到的系统SQL

1、查看所有的主秘钥select * from sys.key_encryptions--删除主秘钥(要先删除使用秘钥的证书)drop master key2、查看所有的证书select * from sys.certificates--删除证书(要先删除使用证书的端点)drop certificate sqlmirror02_cert3、查看所有的端点(endpoint)select * from sys.endpoints--删除端点drop endpoint Endpoint_Mirroring4、修改镜像的运行模式为同步(高安全) alter database test set safety full5、修改镜像的运行模式为异步(高性能) alter database test set safety off6、镜像的故障转移alter database test set partner failover 

四、遇到的错误

1.数据库镜像监视器的报错

(1)错误截图

 (2)解决办法

注：这是有可能连接镜像服务器的认证失效了，比如windows认证的administrator或SQLServer认证的sa密码改了

 

 再次查看已经OK了