Skip to main content

Need Help Using Spok Knowledge?

Certain site features may not be supported by older versions of Internet Explorer.
Chrome, Edge, Firefox, or Safari will provide the most optimal experience.
Spok

Replacing SQL Mirroring Certificates

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:

SQL_log.png

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.

Certificate_Details.png

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

  1. 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:
    Endpoint_Details.png
  2. 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:
    Port_Details.png
    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.
  3. 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:

  1. Replace the Endpoint and Listener_Port fields with the information that you retrieved in the Preparing to create new mirroring certificates section.
  2. Set the expiry_date field of each script to a future date.
  3. Ensure that the folder in which your certificates will be backed up exists and has appropriate permissions configured.
  1. Run Script_1_Principal on the principal server.
  2. Run Script_2_Mirror on the mirror server.
  3. Run Script_3_Witness on the witness server.
  4. 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.
  5. Run Script_4_Principal_Witness on the principal and witness servers.
  6. Run Script_5_Mirror_Witness on the mirror and witness servers.
  7. 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.

Database_mirroring_fixed.png

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.

  1. Query the sys.certificates catalog view to view the new certificates and expiry dates.
    Certificate_Details.png
    This view will also show the expired certificates. The expired certificates are no longer required and can be dropped.
  2. 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
    
  3. 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.
    Delete_Login.png
  4. 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.
    Delete_User.png
  5. Drop the unused login and user from the mirror and witness servers.