转自:https://www.cnblogs.com/trams/archive/2012/01/13/2321637.html
SQL Server 镜像证书过期处理
今天镜像中的主服务器进行维护重启,重启后发现镜像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_datefrom sys.certificateswhere name not like '##%'
检查后发现主机的证书expiry_date时间是2011-01-01,而现在已经是13号了,可以得到一个结论,证书虽然过期但是如果镜像没有断开的话证书还是可以持续使用,直到镜像断开。
于是证书替换,主机,重新创建新证书、修改endpoint并备份给备机:
use master go--principal_cert_new如果不进行endpoint删除的话这里需要新的证书名称create certificate principal_cert_new with subject = 'mirror',start_date='2012-01-01', expiry_date='2099-06-01'; --为N年后更换证书选个日子(6.1已过)goalter endpoint endpoint_mirroringfor database_mirroring (authentication = certificate principal_cert_new)godrop certificate principal_certgobackup certificate principal_cert to file = 'd:\certificate\principal_cert.cer';
备机,还原主机的证书并回复镜像:
use master gocreate certificate principal_cert_new AUTHORIZATION mirror_user from file = 'D:\certificate\principal_cert.cer';goalter database mydb set partner resume