FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...

Database Mirror Certificate Expired

Printer friendly

When certificates are created, if you didn’t specify the EXPIRY_DATE parameter with an expiry date, it will be set to one year. Hence the certificate will expire in one year. The following method can be used to specify an expiry date for a certificate.

CREATE CERTIFICATE cert_Mirror
WITH SUBJECT = 'database mirroring certificate',
EXPIRY_DATE = '12/31/2030';

Although you are not able to change the expiry date for an existing certificate there is a workaround that can be used.

Let's say MIR1 and MIR2 are the two database server instances where mirror is running.

1. Create a new certificate on MIR1 giving appropriate expiry date as specified earlier.
2. Export or backup the public key part of the certificate.
3. Restore the certificate on MIR2. Make sure that this certificate is restored under the same owner of the old certificate.
4. Alter the endpoint at MIR1 to user new certificate.

ALTER ENDPOINT endpointname FOR DATABASER_MIRRORING AUTHENTICATION = new certificate name

5. Drop old certificate for completeness.

You may need to carry out the same steps by changing MIR1 to MIR2 and vice-versa as the certificate on MIR2 also may have expired.

Comments:

Your Name

(Emails will not be displayed on the site or used for promotional purposes)

Comment

Type characters in the image

(case sensitive)