SQL Server 镜像证书过期处理
2016-07-11 16:43:43   来源:   评论:0 点击:

今天镜像中的主服务器进行维护重启,重启后发现镜像Disconnect,使用xp_readerrorlog 0, 1检查错误日志发现下列信息:Database mirrorin

今天镜像中的主服务器进行维护重启,重启后发现镜像Disconnect,使用xp_readerrorlog 0, 1检查错误日志发现下列信息:

Database mirroring connection error 5 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'

检查主机和备机的证书是否存在:

select name, start_date, expiry_date from sys.certificates where name not like '##%'

--主体数据库

--create a new cert for the endpoint

USE master;

CREATE CERTIFICATE [HOST_MASTER_cert_new]

WITH SUBJECT = 'mirroring cert',

START_DATE='08/27/2012', --make sure this is a day prior to the current date

EXPIRY_DATE='08/27/2099'; --make sure this is set out 10-20 years

GO

--backup the cert for the endpoint

BACKUP CERTIFICATE [HOST_MASTER_cert_new] TO FILE = 'c:\HOST_MASTER_cert_new.cer';

GO

--set mirroring to use the new cert 注意修改端点名称

ALTER ENDPOINT mirr

FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [HOST_MASTER_cert_new])

GO

--finally delete the old cert for the endpoint

DROP CERTIFICATE [HOST_MASTER_cert]

GO

--复制证书HOST_MASTER_cert_new.cer到镜像数据库服务器

相关热词搜索:sql

上一篇:MySql提示:The server quit without updating PID file(…)失败
下一篇:自动监控从MySQL同步的脚本