本投稿が公開されているタイミングで、de:code 2017 で まかせて! Azure SQL Database に登壇させていただいています。
この中でデモでお見せした、SQL Database の リアルタイムダッシュボードの構成について説明させていただきます。
デモ環境の構成は、以下のようになっています。
仕組みとしては、以下のようになっています。
- Function Apps から定期的に SQL Database に対してクエリを実行
- 実行したクエリの結果を Power BI のストリーミングデータにリクエストを送信
- ダッシュボードにストリーミングデータを使用したタイルをピン止め
ダッシュボードの内容は、以下の DMV の内容を表示するようにしています。
Premium / Premium RS を使用している場合は、「VIEW DATABASE STATE」の権限を付与することで、sys.dm_os_performance_counters を検索することができるのですが、Basic / Standard の場合は、管理者権限が必要となるので、その対応のために、SQL Database の管理者として、Azure Active Directory 管理者の機能を使用して、AAD のグループを登録し、複数のユーザーに管理者権限を付与できるようにしています。
実際に実行しているクエリについては、https://github.com/MasayukiOzawa/SQLDatabase-Util/blob/master/Database/usp_LiveMonitor.sql で公開している、ストアドプロシージャを使用しています。
このクエリを実行することで、1 行で SQL Database の各種情報を取得できるようにしています。
Function Apps については、PowerShell のスクリプトを実行するようにしており、以下の内容を設定しています。
$endpoint = $ENV:APPSETTING_PowerBI_URI
$datetime = (Get-Date ([System.TimeZoneInfo]::ConvertTimeFromUtc((Get-Date).ToUniversalTime(), ([System.TimeZoneInfo]::FindSystemTimeZoneById("Tokyo Standard Time")))) -format s)
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $ENV:SQLAZURECONNSTR_tpch
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = "usp_LiveMonitor"
$da = New-Object System.Data.SqlClient.SqlDataAdapter
$da.SelectCommand = $cmd
$ds = New-Object System.Data.DataSet
$count = $da.Fill($ds)
$tmp = $ds.Tables[0] | select $ds.Tables[0].Columns.columnname | ConvertTo-Json | ConvertFrom-Json
$tmp.'Counter Date' = $datetime
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($tmp) -Compress)
$ds.Dispose()
$con.Close()
$con.Dispose()
Power BI のストリーミングデータの URL や、SQL Database の接続文字列については、Function Apps のアプリケーション設定として登録してあります。
PowerShell でアプリケーション設定を取得する際には、環境変数から取得することができるため、PowreShell 内の記載については、「$ENV」から取得するようにしています。
これを、タイマートリガーとして実行しています。
{
"bindings": [
{
"name": "myTimer",
"type": "timerTrigger",
"direction": "in",
"schedule": "*/3 * * * * *"
}
],
"disabled": false
}
これで、Power BI に連携できるような設定になりました。
Power BI 側については、以下のようなストリーミングデータセットを設定しています。
[
{
"Counter Date" :"2017-05-14T13:59:58.010Z",
"Database Name" :"AAAAA555555",
"DTU CPU %" :98.6,
"DTU DATA IO %" :98.6,
"DTU LOG Write %" :98.6,
"DTU Memory Usage %" :98.6,
"CPU Usage %" :98.6,
"Buffer Cache Hit %" :98.6,
"Plan Cache Hit %" :98.6,
"Page life expectancy" :98.6,
"Database Cache Memory (MB)" :98.6,
"Plan Chache Memory (MB)" :98.6,
"Free Memory (MB)" :98.6,
"Total Server Memory (MB)" :98.6,
"Target Server Memory (MB)" :98.6,
"Granted Workspace Memory (MB)" :98.6,
"Memory Grants Outstanding" :98.6,
"Batch Requests/sec" :98.6,
"Page lookups (MB)/sec" :98.6,
"Readahead pages (MB)/sec" :98.6,
"Page reads (MB)/sec" :98.6,
"Page writes (MB)/sec" :98.6,
"Checkpoint pages (MS)/sec" :98.6,
"Background writer pages (MB)/sec" :98.6,
"Log Flushes/sec" :98.6,
"Log MBytes Flushed/sec" :98.6,
"Log Flush Waits/sec" :98.6,
"Log Flush Wait Time" :98.6,
"SQL Compilations/sec" :98.6,
"SQL Re-Compilations/sec" :98.6
}
]
これで、準備が完了です。
取得したデータを使用して、グラフを表示するレポートを作成しておき、それをダッシュボードにピン止めしておきます。
Function Apps から Power BI のストリーミングデータセットに対してデータが追加が行われると Power BI のダッシュボードに設定した、グラフがリアルタイムに更新されていきます。
SQL Datbase は、アラートでWebhook との連携ができますので、Funciton App と組み合わせることで、アラートの閾値に達した場合の仕組みなども簡単に作れるかと。