How to replace expired certificates used in database mirroring

原址：http://jeremycarterblog.com/2010/09/how-to-replace-expired-certificates-used-in-database-mirroring/

 

1. 在principal,mirror 实例上用以下script查询出enpoint,certificate,login_name

SELECT e.name [endpoint_name], c.name [cert_name], c.start_date, c.expiry_date
FROM sys.database_mirroring_endpoints e
INNER JOIN sys.certificates c
    ON e.certificate_id=c.certificate_id

go

SELECT
sp.name AS [login_name],
e.name AS [endpoint_name],
c.name AS [cert_name],
c.start_date,
c.expiry_date
FROM
sys.server_permissions AS prmssn
INNER JOIN sys.endpoints AS e
    ON e.endpoint_id = prmssn.major_id
    and prmssn.class = 105
INNER JOIN sys.server_principals AS sp
    ON sp.principal_id = prmssn.grantee_principal_id
INNER JOIN sys.database_principals AS dp
    ON sp.sid=dp.sid
INNER JOIN sys.certificates c
    ON dp.principal_id=c.principal_id
WHERE e.type=4
go

 

3. 在principal 实例上建新的certificate

USE
master;
CREATE CERTIFICATE [principal_new_cert]  
WITH SUBJECT = 'mirroring cert',    
START_DATE='07/11/2012', --必须是当前的日期之前   
EXPIRY_DATE='07/12/2020'; --当前日期后10-20年
GO
BACKUP CERTIFICATE  [mirror_new_cert]     TO FILE = 'C:\principal_new_cert .cer';

 

4.在Mirror 实例上建新的certificate

USE
master;
CREATE CERTIFICATE [mirror_new_cert]  
WITH SUBJECT = 'mirroring cert',    
START_DATE='07/11/2012', --必须是当前的日期之前   
EXPIRY_DATE='07/12/2020'; --当前日期后10-20年
GO

BACKUP CERTIFICATE  [mirror_new_cert]     TO FILE = 'C:\mirror_new_cert .cer';

把这个文件拷贝到mirror服务器上

 

5.在pricipal实例上

CREATE CERTIFICATE mirror_new_cert
   AUTHORIZATION <MirrorServerUser> -- 在第一步查询里得到的login_name
   FROM FILE = 'C:\mirror_new_cert .cer'
GO

 

6. 在mirror实例上

CREATE CERTIFICATE pricipal_new_cert
   AUTHORIZATION <PrincipalServerUser> -- 在第一步查询里得到的login_name
   FROM FILE = 'C:\pricipal_new_cert .cer'
GO

 

7.在pricipal实例上
ALTER ENDPOINT <endpoint name>
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE  [pricipal_new_cert])
go

8.在mirror实例上

ALTER ENDPOINT <endpoint name>
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE  [mirror_new_cer])
go

 

9. 在pricipal实例上
ALTER ENDPOINT <endpoint name> STATE=STOPPED and then STATE=STARTED

 

10.在principal,mirror 实例上drop old certificate

DROP CERTIFICATE [old_mirror_cert]