SQL Server image Certificate replacement Experience Summary (certificate expiration replacement) SQL Server image certificate expiration Processing

I have written an article about how to replace an expired image certificate: SQL Server image certificate expiration. This is a test preparation for replacing a certificate. In this article, I encountered some problems when I replaced a large number of server certificates in my work yesterday. I will analyze and summarize them here.

The following is the script used for operations in production:

-- Script 1
Select name, expiry_date from SYS. Certificates where issuer_name not like 'Ms _ %'
Go
Declare @ SQL varchar (max)
Set @ SQL =''
Select top 1 @ SQL = @ SQL + 'use master' + char (10) +
+ 'Alter databas' + (select top 1 name from SYS. databases where database_id> 4) + 'set partner suspend' + char (10) +
+ 'Create certificate' + name + '_ new with subject = ''mirror '', start_date ='' 2012-01-01 '', expiry_date = ''2099-06-01 ''; '+ char (10) +
+ 'Alter endpoint endpoint_sorting ing' + char (10) +
+ 'For database_authentication ing (authentication = certificate' + name + '_ new)' + char (10) +
+ 'Drop certificate '+ name + ''+ char (10) +
+ 'Backup certificate '+ name +' _ new to file = 'd: \ certificate \ '+ name +' _ new. Cer ';' + char (10) +
+ '/************************************* * **************/'+ Char (10)
From SYS. Certificates
Where issuer_name not like 'Ms _ % 'and pvt_key_encryption_type = 'mk'
Print (@ SQL)
-- Exec (@ SQL)
Go
Select name, expiry_date from SYS. Certificates where issuer_name not like 'Ms _ %'
Select database_id, mirroring_state_desc from SYS. database_locking ing

-- Script 2
Select name, expiry_date from SYS. Certificates where issuer_name not like 'Ms _ %'
Select database_id, mirroring_state_desc from SYS. database_indexing ing where mirroring_state_desc is not null
Go
Declare @ SQL varchar (max)
Set @ SQL =''
Select top 1 @ SQL = @ SQL
+ 'Create certificate' +. name + '_ new authorization' + B. name + 'From File = 'd: \ certificate \ '+. name + '_ new. CER ''; '+ char (10) +
+ 'Drop certificate' + A. Name + char (10) +
+ 'Waitfor delay' 00: 00: 05 ''' + char (10) +
+ 'Alter database' + (select top 1 name from SYS. databases where database_id> 4) + 'set partner resume '+ char (10) +
+ '/************************************* * **************/'+ Char (10)
From SYS. Certificates
Inner join SYS. database_principals B on B. principal_id = A. principal_id
Where issuer_name not like 'Ms _ % 'and pvt_key_encryption_type = 'na'
Print (@ SQL)
-- Exec (@ SQL)
Go
Select name, expiry_date from SYS. Certificates where issuer_name not like 'Ms _ %'
Select database_id, mirroring_state_desc from SYS. database_indexing ing where mirroring_state_desc is not null

This script can run on both the host and the image machine, but the alter database statement can only be executed on one machine. There are two definitions of @ SQL variables, which indicate that this script is run twice. The exec statement is commented out by me to prevent misoperation. The print script result of the image machine is as follows:

use master
alter database DNMembership set partner suspend
create certificate wha999m_new_new with subject = 'mirror',start_date='2012-01-01', expiry_date='2099-06-01';
alter endpoint endpoint_mirroring
for database_mirroring (authentication = certificate wha999m_new_new)
drop certificate wha999m_new
backup certificate wha999m_new_new to file = 'd:\certificate\wha999m_new_new.cer';
/*****************************************************/

create certificate wha999_new_new AUTHORIZATION wha999_user from file = 'D:\certificate\wha999_new_new.cer';
drop certificate wha999_new
WAITFOR DELAY '00:00:05'
alter database DNMembership set partner resume
/*****************************************************/

Detailed operation steps:

1. Execute the first script on the image machine: Pause the database image, create a new certificate, modify the image endpoint, delete the old certificate of the image machine, and back up the certificate (for host restoration)
2. Execute all scripts on the host: the first half of the operations are the same as above, and the second half is used to restore the image server certificate.
3. Execute the second script on the image machine: Restore the certificate of the host, delete the old certificate of the host, wait for 5 seconds to restore the database image (wait for several seconds or otherwise, an error will be reported when the database image is restored)

Note: before deleting a certificate or modifying an endpoint, you must pause the image. Otherwise, the image may become invalid. The above operations may encounter special problems: after the new certificate is created, the old certificate will be deleted immediately. If the certificate is lost or an operation error occurs during this process, the script is repeatedly run twice. Therefore, we recommend that you delete the old certificate after the image is restored.