SQL Server 镜像证书过期处理

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_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到镜像数据库服务器

--镜像数据库
--drop the old cert for the principal login
use master
DROP CERTIFICATE [HOST_MASTER_cert]
GO

--create the new cert using the backup you made on the principal server
CREATE CERTIFICATE [HOST_MASTER_cert_new] AUTHORIZATION HOST_MASTER_user
FROM FILE = 'c:\HOST_MASTER_cert_new.cer'
GO

--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [HOST_SLAVE_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 new cert for the endpoint
BACKUP CERTIFICATE [HOST_SLAVE_cert_new] TO FILE = 'c:\HOST_SLAVE_cert_new.cer';
GO

--set mirroring to use the new cert  注意修改端点名称
ALTER ENDPOINT mirr
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [HOST_SLAVE_cert_new])
GO

--finally delete the old cert for the endpoint
DROP CERTIFICATE [HOST_SLAVE_cert]

--复制证书HOST_SLAVE_cert_new.cer到主体数据库服务器

--主体数据库
USE master;
CREATE CERTIFICATE [HOST_SLAVE_cert_new] AUTHORIZATION HOST_SLAVE_user
FROM FILE = 'c:\HOST_SLAVE_cert_new.cer'

DROP CERTIFICATE [HOST_SLAVE_cert]

--镜像数据库
alter database gyxq set partner resume;