alwaysOn 會處理證書嗎？我必須做些什麼嗎？

在作為可用性組的一部分的數據庫中（總是），在目前的主伺服器中，我創建一個證書並使用它簽署一些儲存過程。

到目前為止一切正常。

use mydatabase
go


-- check if certificate exist, create otherwise
IF NOT EXISTS(SELECT 1 FROM SYS.CERTIFICATES WHERE NAME = 'dyntsqlcert')
BEGIN
   CREATE CERTIFICATE dyntsqlcert   
   FROM FILE = 'C:\Certificates\dyntsqlcert.cer'   
   WITH PRIVATE KEY (FILE = 'C:\Certificates\dyntsqlKey.pvk',
   ENCRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa', 
   DECRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa');  
END


--check if a user for the certificate exist, create otherwise
IF NOT EXISTS(SELECT * FROM SYS.database_principals where name ='dyntsql')
BEGIN
   CREATE USER [dyntsql] FOR CERTIFICATE [dyntsqlcert];
END

-- Granting permissions to the tables
       GRANT SELECT ON [app].[applicant] TO [dyntsql];
       GRANT SELECT ON [usr].[user] TO [dyntsql];
       GRANT SELECT ON [usr].[userCountry] TO [dyntsql];
       GRANT SELECT ON [app].[country] TO [dyntsql];
       GRANT SELECT ON [app].[application] TO [dyntsql];
       GRANT SELECT ON [upl].[applicationDocument] TO [dyntsql];
       GRANT SELECT ON [upl].[document] TO [dyntsql];
       GRANT SELECT ON [upl].[applicationDocumentImages] TO [dyntsql];
       GRANT SELECT ON [upl].[ref_documentType] TO [dyntsql];
       GRANT SELECT ON [app].[program] TO [dyntsql];
       GRANT SELECT ON [app].[ref_applicationStatus] TO [dyntsql];
       GRANT SELECT ON [app].[ref_CPIStatus] TO [dyntsql];
       GRANT SELECT ON [app].[applicationItemGroupChange] TO [dyntsql];
       GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
       --GRANT SELECT ON [APIA_Repl_Sub].[repl].[flightChangesHistory] TO [dyntsql];
       GRANT SELECT ON [app].[applicationstatechange] TO [dyntsql];
       GRANT SELECT ON [app].[applicationPlacementInfo] TO [dyntsql];
       GRANT SELECT ON [app].[applicationPreDepartureQuestions] TO [dyntsql];
       GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
       GRANT SELECT ON [app].[ref_visaStatus] TO [dyntsql];
       GRANT SELECT ON [agy].[agency] TO [dyntsql];

--Adding Signature to SPS'
IF NOT EXISTS
(
   SELECT 1
   FROM sys.crypt_properties cp
   JOIN sys.objects o ON cp.major_id = o.object_id
   LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint AND
   cp.crypt_type IN ('SPVC', 'CPVC')
   LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint AND
   cp.crypt_type IN ('SPVA', 'CPVA')
   WHERE o.name = 'usp_sel_workqueue_applicationsWithNewMedia'
)
BEGIN 
   ADD SIGNATURE TO [app].usp_sel_workqueue_applicationsWithNewMedia
   BY  CERTIFICATE [dyntsqlcert] WITH PASSWORD = 'oH8F*%G9pnyqfa';
END

問題是，如果是 a failover，噹噹前伺服器成為輔助伺服器及其 c:\ 驅動器時，證書是否仍然有效？

我應該測試這個而不是問，但我手頭沒有一個好的測試環境。

由於證書和權限都包含在數據庫級別，因此沒有理由重新創建證書。Always On 將負責將數據傳輸到輔助節點，如果它已同步或同步但匹配 DMV 中的日誌塊編號，則它應該在那裡。

如果您希望證書“存在”在“C:\Certificates”文件夾中並不斷使用它（從問題中不清楚為什麼），那麼它需要在每個副本上。我還要說這會很糟糕，因為密鑰與數據庫位於同一台伺服器上，這有點違背了目的。

引用自：https://dba.stackexchange.com/questions/190223