五餅二魚工作室

最近遇到的問題都剛好會有兩三個不同的狀況，但湊巧解法也都是同樣的處理方式，就順手整理一下囉。

狀況一：有客戶在使用 AlwaysON 的 AG，因為要管控特定人員修改資料，因此啟動了資料庫稽核，但只要發生容錯移轉( Failover )之後，Primary 切換到其他台的時候，資料庫稽核就沒有了，但切回來之後就正常。

狀況二：客戶原本使用資料庫稽核，後來因為環境有問題需要重灌主機，主機重灌完之後，設定好稽核並且把資料庫還原回去，稽核就失效了。

這兩種看似不同的狀況，但實際上問題都是一樣的，主要是「資料庫稽核規格」( Database Audit Specification )建立的時候，雖然會指定「稽核」 ( Server Audit ) 的名稱，但實際上記錄的是稽核的 GUID。以狀況一 AlwaysON 的問題來說，資料庫稽核規格是記錄在資料庫內，一開始雖然在 Primary 上去建立稽核和資料庫稽核規格的時候雖然都沒有問題，但我們還是需要到 Secondary 上面，去建立一個稽核，且這個稽核的 Guid 要跟在資料庫內資料庫稽核規格中所記錄的 Guid 是相同的。這樣當進行容錯移轉的時候，才能讓資料庫稽核規格可以正確地記錄到稽核內來使用。

---

我們實際來做個範例測試一下，我先用以下的語法來建立一組稽核

USE [master]
GO

CREATE SERVER AUDIT [Audit-20191119]
TO FILE 
(	FILEPATH = N'D:\Audit'
	,MAXSIZE = 1 GB
	,MAX_ROLLOVER_FILES = 1024
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT [Audit-20191119]
WITH
(	STATE = ON
) 
GO

接著我在 AlwaysON 建立一個測試用資料庫 , AutoHa-Sample 下面，也去建立一組資料庫稽核規格

USE [AutoHa-sample]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20191119]
FOR SERVER AUDIT [Audit-20191119]
ADD (INSERT,UPDATE, DELETE,EXECUTE ON DATABASE::[AutoHa-sample] BY [public])
WITH
	(STATE=ON)
GO

建立好之後我們用兩個系統檢視來做查詢，分別是 sys.server_audits 和 sys.database_audit_specifications，從下面的資料中我們看到，在資料庫稽核規格中這裡會去記錄稽核的 guid 值，因此當 AlwaysON 的 Secondary 或者是重新安裝的 Server ，在建立稽核的時候，就要特別指定稽核的 Guid 

以上述 AlwaysON 這個例子來說，只要到 AlwaysON 的 Secondary 上面去建立稽核的時候，記得指定這個 Guid 就沒有問題了，因此我們調整一下 SQL 語法來使用，在建立時候中間加入一行 AUDIT_GUID，指定原本在 Primary 上面建立稽核的 Guid 值

USE [master]
GO

CREATE SERVER AUDIT [Audit-20191119]
TO FILE 
(	FILEPATH = N'D:\Audit'
	,MAXSIZE = 1 GB
	,MAX_ROLLOVER_FILES = 1024
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '273C5645-0077-4AE4-A3F6-469D4DF3F34E'
)
GO

ALTER SERVER AUDIT [Audit-20191119]
WITH
(	STATE = ON
) 
GO

建立好之後，我就進行容錯移轉，將原本的 Secondary 切換成為 Primary，此時將資料做異動之後，就可以看到稽核內有相關的紀錄了

---

PS. 其實還有更懶惰的方式，連指令都不要去記了，您也可以直接用 SSMS ，選擇到您原本的稽核，然後按下滑鼠右鍵選擇產生指令碼，也可以產生包含 Audit_Guid 的指令碼。