はじめに

SQL Serverの監視設定として拡張イベントを設定することがあるかと思います。
本稿ではSQL Serverの設定としてよく行う拡張イベントの排出先にAzureStorageを選択した場合の設定方法を記載します。

 

手順

以下の手順で設定を行います。
なお、SQL ServerへのアクセスができるようにクライアントPCにはSSMS(SQL Server Management Studio)がインストールされている前提とします
①AzureStorageの準備(Blobストレージ設定およびSAS設定：AzurePortal)
②拡張機能設定(SSMS)

①AzureStorageの準備

拡張イベントの保存先となるAzureStorageを準備します。
今回はblogに格納するため格納先のディレクトリまで作成しておきます

この後使用するためためストレージアカウントのURLも確認しておきましょう
(https://<ストレージアカウント名>.blob.core.windows.net/<ディレクトリ名>)

また、この後私用するためSASトークンの情報も取得しておきましょう
ディレクトリを左クリックしてSASの生成から取得可能です
(SASトークンはセキュリティとネットワーク>Shared Access Signatureから設定可能です)

※SASが
?sv=2021-06-08&ss=bfqt&srt=s&sp=rwctfx&se=2 (以下略
といった形で表示されている場合は、控えるのは?を抜いた(赤文字以降)部分を控えてください

これでストレージアカウント側の準備は完了です。

②拡張機能設定(SSMS)

SSMSにてまずはAzureStorageにアクセスするための設定を行います。
今回はデータベース単位で取得するため設定を実施するデータベース上で以下のクエリを実行してください。

※<>で書かれている部分は環境に合わせて変更してください
※パスワードは基本的に明示的に使用する機会がないためパスワードの要件に沿って設定してください

USE [<DB名>]	
GO	
	

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)	
BEGIN	
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<任意のパスワード>'	
END	
GO	


DECLARE @StorageAccount nvarchar(255) = '<ストレージアカウントのURL>'	
DECLARE @SASToken nvarchar(255) = '<SASトークン>'	
DECLARE @sql nvarchar(max)	
IF  EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = @StorageAccount)	
BEGIN	
    SET @sql =	
    N'DROP DATABASE SCOPED CREDENTIAL [' + @StorageAccount + ']'	
    EXEC (@sql)	
END	
SET @sql =	
N'CREATE DATABASE SCOPED CREDENTIAL [' + @StorageAccount + ']	
WITH	
IDENTITY = ''SHARED ACCESS SIGNATURE'',	
SECRET = ''' + @SASToken + '''	
';	
EXEC (@sql)	

続いて、拡張イベントの設定を行います
以下のクエリを実施してください(sqlserver.attention,sqlserver.blocked_process_reportのみ設定しています)

ADD TARGET package0.event_fileの文はオプションが多数あるので以下のサイトを参考に適宜設定を変更・追加してください。
参考：https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-event-session-transact-sql?view=sql-server-ver16

CREATE EVENT SESSION [XEvent] ON DATABASE	
	ADD EVENT sqlserver.attention(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_hash,	
	sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
	
	ADD EVENT sqlserver.blocked_process_report(
	ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_hash,
	sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))

	ADD TARGET package0.event_file(SET filename=N'<ストレージアカウントのURL>/xevent.xel'
	,max_file_size=(2048))
WITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)	
GO	

これで拡張イベントの設定も完了です。
最後にイベントを実行するために以下のクエリを実行します

ALTER EVENT SESSION [XEvent] ON DATABASE STATE=START

※実行できない場合はSSMSを実行しているクライアントPCのネットワーク要件(AzureStorageとの通信に必要なポートが開いているか等)やストレージアカウントにきちんとフォルダが存在しているかなどをご確認ください。

以上で設定完了です

確認

ストレージアカウントを確認するとxevent-XXXX.xel(XXXはランダム)が追加されています
デフォルトでは常に書き込み状態となるためオプションを利用して適宜ファイルの管理を実施してください

まとめ

以上で拡張イベントの設定は完了です。
SQLdatabaseのイベントを保管するといった際には是非ご確認ください

おまけ

拡張イベントの確認用スクリプト

DECLARE @eventname nvarchar(255) = '<イベント名>' 
select name from sys.database_event_session_events
	where event_session_id=
		(select event_session_id from  sys.database_event_sessions 
	where name=@eventname)

select value from  sys.database_event_session_fields
	where event_session_id=
		(select event_session_id from  sys.database_event_sessions 
	where name=@eventname)

GO

拡張イベントの停止

ALTER EVENT SESSION [<イベント名>] ON DATABASE STATE=STOP