SQLServer 鏡像認證更換經驗總結（認證到期替換）SQL Server 鏡像認證到期處理

之前有寫過一篇：SQL Server 鏡像認證到期處理  這是之前為替換認證做的測試準備，介紹了如何替換到期的鏡像認證。而這篇文章則是對昨天的工作中進行了大量伺服器的認證替換的時候碰到了一些問題，這裡進行分析和總結。

下面是在生產中操作的使用指令碼：

--指令碼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 database '+(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_mirroring' + CHAR(10) +
+'for database_mirroring (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_mirroring

--指令碼2
select name, expiry_date from sys.certificates where issuer_name not like 'MS_%'
select database_id, mirroring_state_desc from sys.database_mirroring where mirroring_state_desc is not null
go
declare @sql varchar(max)
set @sql = ''
select top 1 @sql = @sql
+'create certificate '+a.name+'_new AUTHORIZATION '+b.name+' from file = ''D:\certificate\'+a.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 a
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_mirroring where mirroring_state_desc is not null

這個指令碼可以同在主機和鏡像機器上跑，但是對於alter database語句的話只允許在一台機器上執行。其中有兩個@sql變數的定義，說明這段指令碼是分成兩次跑的。exec語句被我注釋掉防止誤操作。鏡像機的print指令碼結果如下：

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
/*****************************************************/

詳細的操作步驟：

1. 在鏡像機上執行第一段指令碼：暫停資料庫鏡像、建立新認證、修改鏡像端點、刪除鏡像機老認證、備份認證（用於主機還原）
2. 在主機上執行所有指令碼：前半部分操作同上，後半部分進行鏡像機認證的還原
3. 在鏡像機上執行第二段指令碼：還原主機的認證、刪除主機老認證、等待5秒有還原資料庫鏡像（比較進行幾秒鐘的等待否則資料庫鏡像恢複會報錯）

註：操作刪除認證和修改端點前一定要把鏡像暫停否則有可能導致鏡像失效 上述操作才特殊情況下可能碰到的問題：　　在新認證建立後就立即把老認證刪除掉，如果在這個過程中出現認證丟失或者操作錯誤導致重複跑了兩次指令碼的情況下會很悲劇。所以建議可以在鏡像重新恢複後確定沒有問題再進行老認證的刪除。