[Azure][SQL]使用 Azure SQL Audit 相關經驗紀錄

[Azure][SQL]使用 Azure SQL Audit 相關經驗紀錄

 

原本這一篇是要寫一些在 Azure SQL Database 服務上,開始稽核 ( Audit ) 上的一些經驗分享,但後來在整個測試過程中,發覺自己錯的很離譜,因此決定改標題,當成自己耍白癡的紀錄,提醒自己不要在犯錯了。而先把結論寫出來,就是「 Azure SQL Database 服務的速度是非常快的,而且開啟稽核對效能的影響非常的小,使用者可以很輕易地透過 Excel 內的 Power Query 來讀取這些稽核紀錄,非常的方便」。


 

一開始我為了測試 Azure 上的讀取效能,是否會因為開啟稽核而有所影響,因此先將在地端的 SQL Server 資料庫上的一段測試程式,拿到 Azure 上來進行測試。


DECLARE @Random INT
DECLARE @MaxCount INT
DECLARE @Time DATETIME
SET @MaxCount = 1000
SET @Time = GETDATE() ;
PRINT GETDATE() ;
SET NOCOUNT ON
WHILE @MaxCount > 0
BEGIN
	SET @Random = Round(RAND() * 1000000, 0) ;
	SELECT * FROM [dbo].[BigTable] WHERE A1 = @Random ;
	SET @MaxCount -= 1 ;
END
PRINT GETDATE() ;
PRINT DATEDIFF( ss ,@Time, GETDATE() )

 

上面這一段程式在地端的 SQL Server 執行結果如下,看起來執行需要花上一段時間,透過 DATEDIFF 的函數,可以計算出迴圈執行前後的時間,看起來應該蠻正確的

image

 

但同樣的指令拿到連接 Azure SQL Database 的時候,狀況就有一點不同的,明明要花上一段時間執行的,怎麼我在指令碼內用 DATEDIFF 所計算出來的時間是 0 呢 ? 因此我把這個反覆執行 10 次,有六次算出來的是 1 秒,四次算出來的是 0 秒 ? 實在想不懂哪裡出了問題。

image

 

因此有同事就提出他的想法,或許「真的 Azure SQL Database 的速度那個快,因此算出來的東西只是卡在 Buffer 內慢慢傳輸到前端 」,因此我們嘗試一個方法,將上述指令做個修改,把抓出來的資料先放到一個 Table 變數內,最後在一次取回,來看看執行的時間


DECLARE @Random INT
DECLARE @MaxCount INT
DECLARE @Time DATETIME2
DECLARE @TABLE TABLE ( A1 INT, A2 NVARCHAR(10), A3 VARCHAR(10), A4 DATETIME )
SET @MaxCount = 1000
SET @Time = GETDATE() ;
PRINT GETDATE() ;
SET NOCOUNT ON
WHILE @MaxCount > 0
BEGIN
	SET @Random = Round(RAND() * 1000000, 0) ;
	INSERT INTO @TABLE
	SELECT *,GETDATE() FROM [dbo].[BigTable] WHERE A1 = @Random ;
	SET @MaxCount -= 1 ;
END
PRINT GETDATE() ;
PRINT DATEDIFF( ss ,@Time, GETDATE() )
SELECT * FROM @TABLE

image

 

看起來這樣就正常了,因此測試放大迴圈到 100,000,大約也只需要 23 秒的時間,但整個傳回來到地端則需要 50 秒的時間,因此看起來 Azure SQL Database 的速度算蠻快的。而上述的測試看起來雖然執行很多次,但實際在稽核做紀錄的時候,實際上也只存一筆紀錄,因此上述的指令看來是需要調整一下,因此改成如下的指令來進行


DECLARE @Random INT
DECLARE @MaxCount INT
DECLARE @TABLE TABLE ( A1 INT, A2 NVARCHAR(10), A3 VARCHAR(10), A4 DATETIME )
SET @MaxCount = 100000
SET NOCOUNT ON
BEGIN
	SET @Random = Round(RAND() * 1000000, 0) ;
	INSERT INTO @TABLE
	SELECT *,GETDATE() FROM [dbo].[BigTable] WHERE A1 = @Random ;
	SET @MaxCount -= 1 ;
END
GO 1000

這樣的狀況測試下來,不管有沒有開啟稽核,時間大約都落在 1分15秒左右了,因此看起來不會因為要多記錄下執行的狀況,就對效能來說會有所影響。

 


 

 

而當這些稽核紀錄被存在 Azure Storage 的 Table 內之後,那麼接下來就可以透過 Excel 來搭配 Power Query 來使用了。

 

開啟 Excel 之後,可以選擇上方的 Power Query ( 如果沒有該選項的話,則請先到微軟網站下載 )

image

 

接著選擇「從 Azure」→「從 Microsoft Azure 資料表儲存體

image

 

接著分別輸入「帳戶」和「帳號金鑰」後,就可以連接到 Azure Storage 的資料表了

image

image

 

挑選所要查看的資料表,就可以透過預覽看到大智的資料了。

image

 

image

 

選擇「載入」之後,就可以在 Excel 內看到資料,此時可以透過點選畫面右邊的查詢結果,設定你所要過濾的資料和欄位

image

 

調整好之後就可以選擇「關閉並載入」,有些欄位因為是日期時間的格式,在 Excel 上預設看起來是個數字,因此可以透過格式的設定,設定為時間,這樣看起來會比較清楚一點。除此之外,因為在 Azure 都是採用 UTC 的時間,因此需要自行轉換成為本地時間。

image

 


 

前面步驟所設定的 Excel 表格,可以直接儲存起來,下次在開啟之後,選擇「查詢」→「重新整理」,就可以按照之前所設定好的格式和欄位,重新取得最新的資料了。

image

image

 


 

從上面的測試過程中看起來,使用 SQL Server 稽核可以取代沒有辦法使用 SQL Profile 的困擾,也不會因為加上了他,而使效能有所影響。當這樣的稽核資料存放在 Azure Storage 的 Table 中,也可以搭配 Excel 的 Power Query,用來分析 Azure SQL Database 的服務狀況,看來算是很不錯的搭配組合。