Detecting expired certificates
When SQL mirroring certificates expire, mirroring will fail and the following alerts and errors will appear in the SQL log of the mirrored server:
To check the certificate details, run the query below:
select name,expiry_date,* from sys.certificates
This query will display all certificates in
sys.certificates in catalog view. Expired certificates will have an
expiry_date in the past.
You must replace any expired certificates with new certificates whose expiry dates are in the future.
If you do not specify an expiry date when creating a certificate, the expiry date will default to 1 year in the future.
To minimize the need to replace certificates, Spok recommends setting expiry dates to 3 years in the future. Spok also recommends creating reminders for upcoming expiry dates, in order to minimize downtime.
Preparing to create new mirroring certificates
- Before creating new certificates, query the
sys.endpointscatalog view to get details about the endpoint that will be used for database mirroring. You will need this information later. Run the following query to get the endpoint details:
select * from sys.endpointsThe output from this query looks like the following:
- To get the ports that will be used for database mirroring on the principal and mirror servers, run the following command on the principal, mirror, and witness servers:
SELECT name,type_desc,port, * FROM sys.tcp_endpointsThis will query the
sys.tcp_endpointscatalog view. Here is an example of the output that will be generated when this query is run on the principal server:
Note that in this example, port 5022 is being used on the principal server. Repeat the query on the mirror and witness servers to get port details from those servers as well. You will need this information later.
- Ensure that the folder that you will use to back up your certificates exists and that the appropriate permissions have been configured for it.
Creating new certificates
To create new certificates for SQL server database mirroring, perform the following steps.
In each of the following scripts, do the following:
- Replace the Endpoint and Listener_Port fields with the information that you retrieved in the Preparing to create new mirroring certificates section.
- Set the expiry_date field of each script to a future date.
- Ensure that the folder in which your certificates will be backed up exists and has appropriate permissions configured.
- Run Script_1_Principal on the principal server.
- Run Script_2_Mirror on the mirror server.
- Run Script_3_Witness on the witness server.
- Verify that the certificates that were backed up in steps 1, 2, and 3 were copied across all servers. All certificates should be present on each server in the appropriate folder.
- Run Script_4_Principal_Witness on the principal and witness servers.
- Run Script_5_Mirror_Witness on the mirror and witness servers.
- Run Script_6_Principal_Mirror on the principal mirror servers.
After you perform these steps, your database mirroring should be functioning. You can confirm this by performing a failover.
Cleaning up old mirroring certificates
The following steps will have no impact on your database mirroring, and can be performed as part of maintenance. These steps are not mandatory, but will help keep your servers organized.
- Query the
sys.certificatescatalog view to view the new certificates and expiry dates.
This view will also show the expired certificates. The expired certificates are no longer required and can be dropped.
- Run the following script on both the principal and mirror servers:
drop certificate principal_certificate -- Provide the expired certificate name drop certificate mirror_certificate -- Provide the expired certificate name drop certificate witness_certificate -- Provide the expired certificate name
- Delete the old logins that were used by the mirroring endpoints on both the principal and mirror server. For example, in the Object Explorer on the principal server, right-click on the login entry and select Delete.
- On the master database, delete the users that were associated with the logins that you just deleted. For example, in the Object Explorer, right-click on a user and select Delete.
- Drop the unused login and user from the mirror and witness servers.