MackerelによるSQL Server監視の概要と、取得不可メトリクスの補完方法

こんにちは、SCSKの嶋谷です。

弊社が提供している監視サービスではSQL Serverを監視したいというお客様が一定数います。
Mackerelでは、SQL Serverのキャッシュヒット率や接続ユーザ数といった基本的な情報を監視することができます。
ただし、これらの情報だけでは把握しきれないポイントもあります。（後述）
そのため、Mackerelを利用してSQL Serverの監視サービスを提供する場合、取得できるデータが限られた形での提供となってしまいます。

これまでにMackerelでのOracle Databaseの監視について紹介しましたが、今回はSQL Serverを対象に同様の方法を試してみました。SQL Serverからデータを取得し、それをMackerelに連携することで監視できるのか、実装を通して検証しています。

MackerelでOracle Databaseを監視してみた – TechHarmony

MackerelでのSQL Server監視

Oracle Database監視に関する記事を投稿した際に、MackerelではSQL Serverの状態を監視する機能がないと記載しておりましたが誤りでした。メトリックプラグインを利用してSQL Serverの状態を監視することができます。
ただし、プラグインはオンプレミスサーバやEC2上のサーバに自前でSQL Serverを構築している場合に有効です。

メトリックプラグイン – mackerel-plugin-mssql – Mackerel ヘルプ

AWS RDSで構築したSQL Serverを監視する場合は、AWSインテグレーションの機能を利用して監視することができます。
AWSインテグレーション – Mackerel ヘルプ

プラグインで取得できるデータには限りがあり、DBインデックス断片化率などのデータは取得不可です。
また、プラグインがサポートするSQL Serverのバージョンは下記となっており、古いバージョンではプラグインも利用不可です。
・Microsoft SQL Server 2017以降
・Microsoft SQL Express 2017以降

今回は、「プラグインでのデータ取得」と「プラグインで取得できないデータや古いバージョンでのデータ取得」の2つの方法を検証してみました。

プラグイン利用を利用したSQL Server監視

今回はSQL Serverのライセンス込みのAMIを利用して、AWS EC2上にSQL Serverを構築して検証を実施しました。
これにより、SQL Serverがインストールされた状態でサーバが起動するため、即時利用が可能となります。

取得可能データ

まずは、プラグインで取得可能なデータの一覧を下記に記載します。

グラフ名	メトリック	説明
MSSQL Buffer	Cache Hit Ratio	ディスクから読み出すことなくバッファーキャッシュで見つかったページの割合
	Page Life Expectancy	ページが参照されないままバッファープールに留まった秒数
	Checkpoint Pages	チェックポイントや、すべてのダーティページをフラッシュする必要がある他のオペレーションによって、1秒間にディスクにフラッシュされたページ数
MSSQL Stats	Batch Requests	1 秒間に受信した Transact-SQL コマンドバッチの数
	SQL Compilations	1 秒あたりの SQL コンパイル数
	SQL Re-Compilations	1秒あたりのステートメント再コンパイル数
	Connections	SQL Server に現在接続しているユーザー数
	Lock Waits	1 秒あたりの File IO Provider のロックの待機数
	Procs Blocked	現在ブロックされているプロセスの数
MSSQL Access	Page Splits	インデックスページがオーバーフローした結果、1 秒間に発生したページ分割の数

設定方法

mackerel-agent.confに下記を記載し、mackerel-agentのサービスを再起動するのみです。
(前提として、mackerel-agentがインストールされている必要があります。)

[plugin.metrics.プラグイン名] 
command = ["mackerel-plugin-mssql", "-instance", "SQLEXPRESS"]

※インスタンス名(-instance)はデフォルトのインスタンス名として設定されるSQLSERVERとSQLEXPRESSの2つのみに対応しているため、ユーザ固有のインスタンス名の場合はデータが取得できません。
メトリックプラグイン – mackerel-plugin-mssql – Mackerel ヘルプ

データの一例

Mackerelコンソール上に表示されたデータの一例を示します。

今回はMSSQL Bufferのグラフを示します。3つのデータを個別・複合的に分析することでDBの傾向を読み取ることができます。

スクリプトを利用したSQL Server監視

プラグインで取得不可のデータに対しては、下記の流れをPowerShellスクリプトで実装し、データ取得・連携をしています。
①SQL Serverに接続
接続にはSQL認証を利用しているため、SQL認証を許可する必要があります。(設定の詳細は割愛いたします。)
②SQL文を実行してデータ(数値)を取得
③取得データをエージェントを利用してMackerelに投稿

スクリプト概要

今回はスクリプトに修正を加えることを減らすために、接続情報やSQL文を別のファイルとして用意しました。
修正が必要な場合には、スクリプトではなくsqlファイルやiniファイルを修正することを想定しています。

ファイル名	説明
mackerel_sqlserver_connect.ps1	SQL Serverに接続してデータを取得後、Mackerelに連携するスクリプト
mackerel_sqlserver_connect.sql	実行するSQL文を記述したファイル
mackerel_sqlserver_connect.ini	SQL Serverへの接続情報などを記載したファイル

以下が作成したスクリプトです。

# PowerShell スクリプト例: SQL Server 接続とクエリ実行
param( 
　[string]$sql_file, # 実行するSQLを記述したファイル
　[int]$item_number,
　[string]$figure_name,
　[string]$metric_name,
　[string]$failure_status,
　[string]$alert_monitorname
)

function Create_Json($HOSTID, $alert_monitorname, $failure_status, $mackerel_output_error, $unixtime, $maxCheckAttempts) {
　# JSONにしたいデータを「オブジェクト」で作る
　$bodyObject = @{
　　reports = @(
　　　@{
　　　　source = @{
　　　　type = "host"
　　　　hostId = [string]$HOSTID
　　　}
　　　name = [string]$alert_monitorname
　　　status = [string]$failure_status
　　　message = [string]$mackerel_output_error
　　　occurredAt = [long]$unixtime
　　　maxCheckAttempts = [int]$maxCheckAttempts
　　　}
　　)
　}
　# JSON文字列へ
　$POSTJSON = $bodyObject | ConvertTo-Json -Depth 10

　return $POSTJSON
}

#APIキーの取得
function Get-MackerelApiKey {
　param (
　　[string]$filePath
　)

　$configContent = Get-Content -Path $filePath
　foreach ($line in $configContent) {
　　if ($line -match "apikey\s*=\s*`"(.+)`"") {
　　　return $matches[1]
　　}
　}
　return $null
}

#TLS1.2を強制
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# APIキーを取得
$MACKEREL_APIKEY = Get-MackerelApiKey -filePath ".\mackerel-agent.conf"

# hostIDの取得
$HOSTID = Get-Content -Path ".\id"

# ヘッダーの設定
$headers = @{
　"X-Api-Key" = $MACKEREL_APIKEY
　"Content-Type" = "application/json; charset=UTF-8"
}

# 現在時刻をエポック秒で取得
$currentUtcTime = [DateTime]::UtcNow
$unixtime = [int][double]((Get-Date $currentUtcTime -UFormat %s))

# ユーザ自身で接続情報(ユーザ名やパスワード)とSQL文を別のファイルに準備していただき、そのファイルを読み込み
$filePath = "mackerel_sqlserver_connect.ini"
$PARAM = @{}

try {
　Get-Content $filePath -ErrorAction Stop | % { $PARAM += ConvertFrom-StringData $_ -ErrorAction Stop }
}
catch {
　Write-Error "File not exists: $filePath"
　exit 0
}
# 取得した接続情報を各変数に代入
$Server = $PARAM.Server
$UserName = $PARAM.UserName
$Pass = $PARAM.Pass
$maxCheckAttempts = $PARAM.maxCheckAttempts

try {
　# 接続文字列（SQL認証）
　$connectionString = "Server=$Server;
　User Id=$UserName;
　Password=$Pass;
　"
　# 接続とコマンド実行
　$connection = New-Object System.Data.SqlClient.SqlConnection
　$connection.ConnectionString = $connectionString
　$connection.Open()
　$command = $connection.CreateCommand()
　$Query = Get-Content -Path $sql_file -Raw -Encoding UTF8
　$command.CommandText = $query

　$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
　$dataset = New-Object System.Data.DataSet
　$adapter.Fill($dataset) | Out-Null

　#数値結果が取得できている場合、エラーが解消されていると判断してSQLServer関連のエラーがあればクローズ
　$alert_response = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/alerts" -Method Get -Headers $headers 
　# 取得したアラート($alert_response)にはMackerel発生しているアラートすべてが含まれている
　# アラートからSQLServerに関するアラートだけを取得し、ホストIDとアラート内容でクローズを判定
　$response_removes = @()
　$response_removes += $alert_response.alerts | Where-object {
　　# メッセージが存在し、指定のエラーパターンに一致
　　($_.message -ne $null) -and ($_.message -match "SQLServer Error") -and
　　# 同一ホストIDのみを対象
　　($_.hostId -eq $HOSTID)
　}
　if ($response_removes.Length -ne 0) {
　　#Write-Host "not null"
　　$body = @"
{
　　　"reason": "solve"
}
"@

　#アラートをクローズ
　$utf8Bytes = [System.Text.Encoding]::UTF8.GetBytes($body)
　for ($i = 0; $i -lt $response_removes.Length; $i++) {
　　$return = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/alerts/$($response_removes[$i].id)/close" -Method POST -Headers $headers -Body $utf8Bytes
　　#Write-Output $return 
　　}
　}
　else {
　　#Write-Output "null"
　}

　# 結果表示
　$dataset.Tables[0] | Format-Table -AutoSize
　$connection.Close()

　#返り値を配列に格納(レコードごとではなく、1データずつ)
　$table = $dataset.Tables[0]

　$resultRow = $table.Rows | ForEach-Object { ,$_.ItemArray} 
　$sql_result = @($resultRow | ForEach-Object { $_ } | ForEach-Object { $_ })

　Write-Host $$resultRow[0]
　Write-Host $sql_result[$item_number-1]

　#Mackerelにデータ投稿される形式で出力
　Write-Host "sqlserver.$($figure_name).$metric_name`t$($sql_result[$item_number-1])`t$unixtime"
　exit 0
}
catch {
　#エラー発生時にチェック監視としてアラートを投稿
　$exception_error = "SQLServer Error_net : " + $_.Exception.Message
　Write-Host $exception_error
　$POSTJSON = Create_Json $HOSTID $alert_monitorname $failure_status $exception_error $unixtime $maxCheckAttempts
　$utf8Bytes = [System.Text.Encoding]::UTF8.GetBytes($POSTJSON)
　Write-Host $POSTJSON
　$response = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/monitoring/checks/report" -Method Post -Headers $headers -Body $utf8Bytes
　Write-Host $response

　exit 1
}

以下にスクリプトの簡単な仕様を記載します。
(1)必要情報の取得
スクリプト実行における必要な情報を取得します。
・MackerelAPIKey
・ホストID
・DB接続情報(サーバ名・ユーザ名・パスワード)
・Mackerelに表示されるグラフ名、メトリック名
・SQL文

(2)SQL Serverに接続してデータを取得
DB接続情報、SQL文をもとにSQL Serverに接続してデータを取得。
データが取得できない場合は、エラー内容を文字列で取得し、アラートとしてMackerelに連携します。
エラーの原因は下記が考えられます。
・DB接続情報の設定ミス
・SQL文の記述ミス
・ネットワークエラー　など
Mackerelの仕様上、一度投稿されたアラートはMackerel上に表示され続けます。
そのため数値データを取得できた場合は、アラートが解決されたと判断してSQL Serverに関する投稿済みのアラートを自動でクローズします。

(3)データの連携
取得したデータをMackerelに連携します。連携したデータはグラフとして可視化されるので、(1)の処理で取得したグラフ名やメトリック名も同時に連携します。

利用手順

(1)以下のファイルをmackerel-agent.confが格納されているフォルダに配置
・mackerel_sqlserver_connect.ps1
・mackerel_sqlserver_connect.sql
・mackerel_sqlserver_connect.ini

(2)mackerel_sqlserver_connect.iniにDB接続情報を記述
・接続するサーバ
・ユーザ名
・パスワード

(3)mackerel_sqlserver_connect.sqlにSQL文を記述

(4)mackerel-agent.confに以下を追記
[plugin.metrics.プラグイン名]
command = [“powershell”, “-File”, “mackerel_sqlserver_connect.ps1”, “-sql_file”, “mackerel_sqlserver_connect.sql”, “-item_number”, “連携するクエリ結果の列番号”, “-figure_name”, “グラフ名”, “-metric_name”, “メトリック名”, “-failure_status”, “WARNINGもしくはCRITICAL”, “-alert_monitorname”, “アラート名”]
※プラグイン名・グラフ名・メトリック名・アラート名は任意で設定可能です。

(5)mackerel-agentの再起動

データを取得できた場合

データを取得できた場合、Mackerelに連携されて下記のグラフのように可視化されることを確認しました。
このグラフはセッション総数を可視化しています。
今回はセッション総数のグラフを可視化してみましたが、SQLを実行して取得できる数値データはすべて連携できます。

データを取得できない場合

データを取得できない場合、以下のようなアラートがMackerel上に表示されることを確認しました。

DB接続情報の設定ミス時のエラー

SQL文の記述ミス時のエラー

上記のエラー内容は一例ですが、エラー内容を連携することでアラートの原因を一目で理解できます。

まとめ

今回はSQL ServerからSQL文を用いてデータを取得して、Mackerelに連携するスクリプトを作成しました。

Oracle Databaseを対象とした開発を経験していたこともあり、比較的簡単に実装することができました。

現在のスクリプトには課題が多くあり、機能として提供するには程遠い状態です。

自分一人の力では限界があるので、部署の方と協力して提供レベルにまで高度化していきたいです。

最後までご覧いただきありがとうございました！