五餅二魚工作室

原本這一篇是要寫一些在 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 的函數，可以計算出迴圈執行前後的時間，看起來應該蠻正確的

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

因此有同事就提出他的想法，或許「真的 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

看起來這樣就正常了，因此測試放大迴圈到 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 ( 如果沒有該選項的話，則請先到微軟網站下載 )

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

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

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

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

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

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

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