Replacing SQL Mirroring Certificates

Last updated
Save as PDF

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.

Creating New Mirroring Certificates

The SQL and Spok Mobile Services don't need to be stopped and started to prepare for creation of new mirroring certificates. There will be no Impact to customers. Primary DB Server will continue to serve the users. Data Sync will not happen at the time of certificate renewal. Once the certification renew is completed data sync will happen between the primary and secondary servers.

Step 1: Prepare to create a new mirroring certificate

Before creating new certificates, query the sys.endpoints catalog 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.endpoints
```
The 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_endpoints 
```
This will query the sys.tcp_endpoints catalog 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.

Step 2: Create the new mirroring certificate

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.

Step 3: 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.certificates catalog 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.