本投稿が公開されているタイミングで、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 と組み合わせることで、アラートの閾値に達した場合の仕組みなども簡単に作れるかと。