--==================================================
--查找快过期的证书
USE [master]
GO
SELECT 
name AS CertificateName,
expiry_date AS ExpiryDate
FROM [master].[sys].[certificates]
WHERE expiry_date<‘2020-01-01‘
AND name NOT LIKE ‘##%‘
GO
--查找镜像服务器
SELECT DB_NAME(database_id) AS DatabaseName,
mirroring_partner_name
FROM [master].[sys].[database_mirroring]
WHERE mirroring_partner_name IS NOT NULL

--==================================================
--在主库上创建证书并修改镜像端点
USE master
GO
CREATE CERTIFICATE HOST_cert_3_1 WITH SUBJECT = ‘HOST_cert_3_1‘ ,
START_DATE = ‘01/01/2010‘ , EXPIRY_DATE = ‘01/01/2099‘;
GO
BACKUP CERTIFICATE HOST_cert_3_1 TO FILE = ‘D:\HOST_cert_3_1.cer‘
GO
ALTER ENDPOINT Endpoint_Mirroring
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_cert_3_1)
GO
--==================================================
--在从库上还原证书并授权用户
USE [master]
GO
CREATE LOGIN [MirrorUser] WITH PASSWORD=N‘MirrorUser@123‘,
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [MirrorUser] FOR LOGIN [MirrorUser]
GO
CREATE CERTIFICATE HOST_cert_3_1
AUTHORIZATION [MirrorUser]
FROM FILE=‘D:\HOST_cert_3_1.cer‘
GO
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO [MirrorUser]
--==================================================
--在主库上恢复数据库镜像
USE [master]
GO
ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME
GO
 
--清除过期证书
DROP CERTIFICATE HOST_cert_3_1_old