SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

de:code 2017 DI02 で使用したリアルタイムダッシュボードの構成

leave a comment

本投稿が公開されているタイミングで、de:code 2017 で まかせて! Azure SQL Database に登壇させていただいています。

この中でデモでお見せした、SQL Database の リアルタイムダッシュボードの構成について説明させていただきます。

デモ環境の構成は、以下のようになっています。

image

仕組みとしては、以下のようになっています。

  • 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」から取得するようにしています。

image

これを、タイマートリガーとして実行しています。

{
  "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 のダッシュボードに設定した、グラフがリアルタイムに更新されていきます。

imageimage

SQL Datbase は、アラートでWebhook との連携ができますので、Funciton App と組み合わせることで、アラートの閾値に達した場合の仕組みなども簡単に作れるかと。

Written by masayuki.ozawa

5月 23rd, 2017 at 3:40 pm

Posted in SQL Database

Tagged with

Leave a Reply

*