[SQL]如何在 AlwaysON 環境下設定資料庫稽核

透過指定 AUDIT_GUID 讓 AlwaysON 可以支援稽核功能

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

狀況一:有客戶在使用 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 的指令碼。