コンテンツに移動
データベース

Cloud SQL for SQL Server でのエラーログ メッセージのアラート設定

2022年5月17日
Google Cloud Japan Team

※この投稿は米国時間 2022 年 5 月 17 日に、Google Cloud blog に投稿されたものの抄訳です。

Cloud SQL for SQL Server を使用すると、既存のオンプレミスの SQL Server ワークロードを Google Cloud に取り込むことができます。Cloud SQL が、インフラストラクチャ、メインテナンス、パッチ適用を行うので、ユーザーはアプリケーションとユーザーに集中できます。アプリケーションを改善するための最良の方法は、デッドロック、ジョブの失敗、データベースの健全性の変化など、ユーザーに影響を与える可能性のある問題に対する SQL Server のエラーログをモニタリングすることです。

Cloud SQL for SQL Server と Cloud オペレーション スイート

Cloud オペレーション スイートを使用して、Cloud SQL for SQL Server のエラーログ メッセージのモニタリングとアラートを実行できます。オペレーション スイートは、お客様によるインフラストラクチャやアプリケーションの可視化を可能にする Google Cloud のオブザーバビリティ ソリューションです。Cloud オペレーション スイートを使用すると、複数のインスタンスのモニタリングとアラートを大規模に実装し、PagerDuty、Slack、メール、カスタム Webhook など、任意の方法でアラートを設定できます。

オペレーション スイートから使用するツールは Cloud Monitoring と Cloud Logging です。Cloud Logging では、アプリケーションやサービスからのログを表示でき、それらのログからカスタム指標を作成できます。Cloud Monitoring では、指標、ヘルスチェック結果、稼働時間チェックの結果が指定の基準に達したときに通知するようアラート ポリシーを作成できます。この仕組みを理解するために、Cloud SQL インスタンスでデッドロック検出を有効にし、デッドロックが検出されたときにモニタリングするログベースの指標を作成し、新しく作成したログベースの指標でアラート ポリシーを作成します。SQL Server のエラーログ メッセージをモニタリングするためのアーキテクチャは以下のとおりです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_CloudSQLErrorLog.max-1000x1000.jpg

Cloud SQL for SQL Server の構成

必要なもの

設定方法を詳しく説明します。まず、Cloud SQL for SQL Server インスタンスが必要なので、簡単に設定するための手順を以下に示します。

1. Google Cloud Console で、Cloud SQL の [インスタンス] ページに移動します。

2. [インスタンスを作成] をクリックします。

3. [SQL Server を選択] をクリックします。

4. [インスタンス ID] に名前を入力します。

5. SQL Server ユーザーのパスワードを入力します。

6. [構成オプションを表示] を開きます。

7. [フラグとパラメータ] に、次のトレースフラグを追加します。

a.1222

b.1204

8. [インスタンスを作成] をクリックします。

すでに Cloud SQL for SQL Server インスタンスがある場合は、その Cloud SQL for SQL Server インスタンスを編集する必要があります。

https://storage.googleapis.com/gweb-cloudblog-publish/images/2_CloudSQLErrorLog.max-2000x2000.jpg

編集画面の「フラグとパラメータ」で、SQL Server トレースフラグ 1204 と 1222 を追加し、有効にする必要があります。これらのフラグは、SQL Server エラーログへのデッドロック検出メッセージを有効にします。この変更後、インスタンスを再起動してください。Cloud SQL for SQL Server インスタンスの編集に関する詳細は、こちらで確認できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/3_CloudSQLErrorLog.max-1400x1400.jpg

Cloud SQL for SQL Server インスタンスを接続する

以下の手順を実行して、ローカルマシンから Cloud SQL for SQL Server インスタンスに接続します。

1. Google Cloud CLI をインストールします。Google Cloud CLI には、Cloud SQL や他の Google Cloud サービスを操作するための gcloud CLI が用意されています。gcloud CLI は Admin API を使用して Cloud SQL にアクセスするため、gcloud CLI を使用して Cloud SQL にアクセスする前に、Admin API を有効にする必要があります。

2. bash シェルのコマンド プロンプトまたは Windows PowerShell で次のコマンドを実行して、gcloud CLI を起動します。gcloud auth login

3. 次のコマンドを実行して、gcloud CLI を認証します。gcloud auth login

4. Cloud SQL Auth プロキシをダウンロードしてインストールします(Cloud SQL Auth プロキシのインストールをご覧ください)。次の手順で Cloud SQL Auth プロキシを実行するので、Cloud SQL Auth プロキシの場所をメモします。

5. bash シェルのコマンド プロンプト(または Windows PowerShell)を使用して Cloud SQL Auth Proxy を実行します。具体的には、次のコマンドを実行します。Instance-connection-name は、Google Cloud Console の [概要] タブ(該当するインスタンス)の対応する値に置き換えます)。./cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:1433

6. Azure Data Studio で、新しい接続を作成します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_CloudSQLErrorLog.max-2000x2000.jpg

7. [サーバーへの接続] ダイアログに次の値を入力します。

a.[サーバーの種類] に「Microsoft SQL Server」と入力します。

b.[サーバー] には、SQL Server インスタンスの IP アドレスとして「127.0.0.1」と入力します。

  • TCP 接続の場合、Cloud SQL Auth Proxy はデフォルトで localhost(127.0.0.1)をリッスンします。Cloud SQL Auth Proxy を使って Azure Data Studio と Cloud SQL インスタンスを接続するので、この IP アドレスを使用することが必要です。

c. [認証] に、「SQL Login」と入力します。

d. [ログイン] に「sqlserver」と入力します。

e. [パスワード] に、インスタンスの作成時に使用したパスワードを入力します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/5_CloudSQLErrorLog.max-1000x1000.jpg

8. [接続] をクリックします。

デッドロックを作成する

Azure Data Studio に接続されたので、以下の T-SQL コードを実行して、SQL Server インスタンス上に一時テーブルを作成します。

読み込んでいます...

次に、デッドロックを作成するために、Azure Data Studio で 2 つのクエリ セッションを開き、ここで指定した順序で各コマンドを 1 ステップずつ実行します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/6_CloudSQLErrorLog.max-1300x1300.jpg

セッションのいずれかがデッドロックしたというエラーが発生するはずです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/7_CloudSQLErrorLog.max-2000x2000.jpg

ログ エクスプローラにエラーログを表示する

これで、Google Cloud コンソールの Cloud Logging から、SQL Server エラーログを表示できるようになりました。ロギングは、ナビゲーション バーの [オペレーション] セクション、または Google Cloud コンソールの検索バーに「ロギング」と入力すると見つかります。

https://storage.googleapis.com/gweb-cloudblog-publish/images/8_CloudSQLErrorLog.max-700x700.jpg

次に、Cloud Logging ログ エクスプローラのセクションで、適切な結果をフィルタリングするためのクエリを作成します。

リソースは、→ Cloud SQL データベース → Cloud SQL For SQL Server インスタンス名にする必要があります。

ログは、→ Cloud SQL ログ → sqlserver.err である必要があります。

これで、ログ内でデッドロック メッセージが表示できるようになったはずです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/9_CloudSQLErrorLog.max-2000x2000.jpg

ログベースのカスタム指標とアラート ポリシーを作成する

カスタム指標に使用するデッドロック メッセージを特定するために、ログ エクスプローラでカスタムクエリのフィルタを作成する必要があります。ログ エクスプローラに以下のクエリを入力します。

読み込んでいます...

<YourGoogleCloudProject> は Cloud SQL インスタンスが存在するプロジェクト名、<YourCloudSQLInstance> は Cloud SQL for SQL Server インスタンスの名前です。

これで、単一のデッドロック ログエントリが表示されます。クエリ結果のセクションには、右側に [アクション] ボタンがあります。[アクション] をクリックして「指標を作成」を選択します。
https://storage.googleapis.com/gweb-cloudblog-publish/images/10_CloudSQLErrorLog.max-700x700.png

これにより、[ログの指標の作成] という新しいページが開きます。ここでは、カスタム指標に名前と説明を指定できます。カウンタ指標のまま、単位も 1 のままにしてください。任意のラベルを追加し、[指標を作成] をクリックします。ラベルは Key-Value ペアで、Google Cloud のリソースを整理する際に役立ちます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/11_CloudSQLErrorLog.max-1600x1600.png

これにより、デッドロックをモニタリングして追跡するための新しいユーザー定義の指標が用意されます。[ユーザー定義の指標] セクションで、カスタム指標名の右側にある 3 つの点をクリックします。[Metrics Explorer で表示する][指標に基づいて通知を作成する] のオプションが表示されます。Metrics Explorer で指標を表示するには、データを表示するための新しいデッドロックをトリガーする必要があります。

それでは、アラート ポリシーを作成しましょう。[指標に基づいて通知を作成する] をクリックして、新しいデッドロック指標のためのアラート ポリシーを定義します。
https://storage.googleapis.com/gweb-cloudblog-publish/images/12_CloudSQLErrorLog.max-700x700.jpg

[指標に基づいて通知を作成する] のリンクをクリックすると、アラート ポリシー UI に直接移動します。ここで、アラートを作成し、そのアラートが配信されるべき特定の条件を特定できます。条件セクションでは、すでにカスタム指標が選択されているはずです。すべてをデフォルトのままにして、[通知と名前] を選択できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/13_CloudSQLErrorLog.max-2000x2000.jpg

次に、このアラートがトリガーされたときに通知する宛先を設定します。その前に、通知チャンネルを設定する必要があります。通知チャンネルは、メールアドレスでも、Slack や PagerDuty などのさまざまな統合ツールでも構いません。通知する宛先を選択したら、アラートに名前を付け、アラートの問題の解決方法に関する手順を追加します。アラートを保存したら完了です。別のデッドロックを強制的に発生させて、新しいアラートをテストすることをおすすめします。お疲れさまでした。SQL Server エラーログ メッセージに基づいてアラートを設定する方法を学びました。

この種のアラートは、デッドロック以外にも作成できます。クラッシュ ダンプ、接続問題、破損など、エラーログに表示される他のメッセージをモニタリングするアラートを設定できます。  また、SQL Server エージェント ログ メッセージに基づいたアラートも作成できます。  以下に、カスタム指標のテキスト ペイロードとして使用できる、SQL エラーログの文字列を含むいくつかの例を示します。

  • Agent XPs の無効化:

    • SQL Server エラーログに含まれる文字列: "Configuration option 'Agent XPs' changed from 1 to 0"

  • SQL Server エージェントのステータス:

    • SQL Server エージェントのログに含まれる文字列: "SQLServerAgent terminated"

  • ジョブの失敗:

    • SQL Server エージェントのログには、"SQL Server Scheduled Job 'demo' (0xB83611A22D4FD74B8900ADDFDC9CDD9C) - Status: Failed - Invoked on:" と同様の文字列が含まれます。

    • 以下のオプションは、tsql を使用して確認または設定する必要があります。

https://storage.googleapis.com/gweb-cloudblog-publish/images/14_CloudSQLErrorLog.max-700x700.jpg
  • データベースのステータス:

    • SQL Server エラーログには、次のいずれかの文字列が含まれます。

      • Database % cannot be opened. It has been marked SUSPECT

      • Database % database is in emergency or suspect mode

      • database % is marked EMERGENCY_MODE

      • Database % cannot be opened because it is offline.

      • Setting database option OFFLINE to ON for database

  • カスタム エラー メッセージ

  • Using SQL エージェント ジョブを使用して、ログコマンドで RAISERROR を使用すると、SQL エラーログにカスタム メッセージを書き込むことができます。これは、アプリケーションやデータベースの条件が満たされたときにトリガーされる可能性があります。一つの方法として、SQL エージェント ジョブを作成し、以下のような簡単なクエリでジョブの手順を定義してこれを行うことができます。

読み込んでいます...

そして、これを数分おきに実行するようにスケジュール設定します。これにより、以下の画像のようなエラーログ メッセージと textPayload が生成されます。アラートとモニタリングに対しても、上で説明した通知と同じ手順で行うことができます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/15_CloudSQLErrorLog.max-700x700.png

最後までお読みいただきありがとうございました。

- Google Cloud、カスタマー エンジニアおよびデータ管理担当 Latav Dudley

Google Cloud、SQL Server データベース エンジニア Bryan Hamilton

投稿先