Amazon CloudWatch と AWS Systems Manager のカスタムメトリクスを使用して Microsoft SQL Server を監視する

Microsoft SQL Server ベースのアプリケーションの監視は、アプリケーションのパフォーマンスと正常性を確保するための重要なステップです。Microsoft SQL Server の動作についてより深い洞察を得るには、カスタムメトリクスを収集して異常がないか監視およびアラートする必要があります。Amazon EC2 Windows インスタンスでホストされている Microsoft SQL Server のカスタムメトリクスは、監視やアラートの準備が最初から整っているわけではありません。Amazon CloudWatch は、AWS でリアルタイムに実行されている SQL Server ワークロードに対して定義したカスタムメトリクス、イベント、ログを用いてモニタリングを行う AWS のサービスです。Amazon EC2 Windows インスタンス、または CloudWatch エージェントと SSM エージェントが稼働しているオンプレミスサーバーのいずれかで、監視およびアラートを目的として、カスタムメトリクスを収集して Amazon CloudWatch に発行できます。

その他の AWS のサービス

AWS Systems Manager を使用すると、AWS 上のインフラストラクチャを可視化し、制御することができます。

AWS Systems Manager パラメータストアは、設定データ管理と機密管理のための安全な階層型ストレージを提供します。パスワード、データベース文字列、ライセンスコードなどのデータをパラメータ値として保存することができます。パラメータストアは追加料金なしで提供されます。

このブログ記事では、Amazon EC2 Windows インスタンスで CloudWatch エージェントを設定して、Windows パフォーマンスモニターから SQL Server のカスタムメトリクスを取得する方法を説明します。また、これらのカスタムメトリクスを公開する方法と Amazon CloudWatch コンソールで監視する方法も紹介します。CloudWatch エージェントがこれらのメトリクスをキャプチャし、同様のメトリクスが必要な複数の SQL Server インスタンスで同じ設定を再利用するために用いられるAWS Systems Manager パラメータストアにカスタム設定を保存する方法についても説明します。

CloudWatch エージェントは、次の 64 ビット Windows OS バージョンをサポートしています。

Windows Server 2016、Windows Server 2012、Windows Server 2008 など

AWS Systems Manager を使用して Amazon CloudWatch 用の Amazon EC2 Windows インスタンスを設定するための以下の高度な手順を説明します。

Amazon EC2 Windows インスタンスの IAM ロールを設定する
Amazon EC2 Windows インスタンスで CloudWatch エージェントを設定する
CloudWatch エージェントの設定ファイルを作成する
AWS Systems Manager パラメータストアに CloudWatch エージェントの設定を保存する
インスタンスに SQL Server カスタムメトリクスの設定を適用する
Amazon CloudWatch で SQL Server メトリクスを監視する

ステップ 1: Amazon EC2 Windows インスタンスの IAM ロールを設定する

CloudWatch エージェント設定の使用方法に応じて、2 つの異なるポリシーを持つ AWS Identity and Access Management (IAM) ロールを Amazon EC2 Windows インスタンスに設定できます。ポリシーが CloudWatchAgentServerPolicy のロールでは、CloudWatch エージェントをサーバーにインストールしてメトリクスを Amazon CloudWatch に送信することができます。ポリシーが CloudWatchAgentAdminPolicy のもう 1 つのロールは、CloudWatch エージェント設定を AWS Systems Manager パラメータストアに保存するために必要になります。これにより、複数のサーバーで同じ CloudWatch エージェント設定を再利用できます。パラメータストアに保存されている設定を再利用するために読み取り専用アクセスのみが必要な場合は、CloudWatchAgentServerPolicy を使用してそれらを設定することをお勧めします。「Create IAM Roles and Users for Use with CloudWatch Agent」をご覧ください。

AWS Systems Manager を使用して CloudWatch エージェントをインストールまたは設定するには、IAM ポリシー AmazonEC2RoleforSSM を前述のポリシーとは別にロールにアタッチする必要があります。このロールにより、インスタンスは Systems Manager とやり取りできるようになります。既存のインスタンスにロールをアタッチする方法の詳細については、「Attaching an IAM Role to an Instance」をご覧ください。

ステップ 2: Amazon EC2 Windows インスタンスで CloudWatch エージェントを設定する

他の CloudWatch メトリクスと同様に、CloudWatch エージェントで収集したメトリクスを Amazon CloudWatch に保存して表示できます。

Windows Server を実行しているサーバーでは、CloudWatch エージェントをインストールすると、Windows Performance Monitor のカウンターに関連付けられているシステムレベルのメトリクスと SQL Server のメトリクスを収集できます。

Windows Performance Monitor を使用して収集できないカスタムメトリクスについては、StatsD プロトコルを使用して取得できます。CloudWatch エージェントはプロトコルのデーモンとして機能します。標準的な StatsD クライアントを使用して、メトリクスをポート 8125 の CloudWatch エージェントに送信できます。詳細については、「Retrieve Custom Metrics with StatsD」をご覧ください。利用できるいくつかの StatsD クライアントについて詳しくは、「StatsD client page on GitHub」をご覧ください。

CloudWatch エージェントで Windows Performance Monitor から収集したたカスタムメトリクスは、カスタムメトリック名、単位、およびディメンションで上書きできます。リソースが Windows パフォーマンスオブジェクトの一部として存在する場合、CloudWatch エージェントは異なるリソースにまたがって集約されたメトリクスも収集します。

CloudWatch エージェントによって収集されたメトリクスはカスタムメトリクスとして課金されます。CloudWatch メトリクスの料金設定の詳細については、「Amazon CloudWatch Pricing」をご覧ください。

AWS Systems Manager を使用して、実行中の Amazon EC2 Windows インスタンスに CloudWatch エージェントをインストールできます。AWS Systems Manager を使用して CloudWatch エージェントをインストールするには、「Getting Started: Installing the CloudWatch Agent on Your First Instance」をご覧ください。

注: CloudWatch エージェントでは、インスタンスが SSM Agent バージョン 2.2.93.0 以降を実行している必要があります。そうする上で最も簡単な方法は、Run Command を使用して最新バージョンにアップデートすることです。詳細な手順については、「Update SSM Agent by Using Run Command」をご覧ください。

ステップ 3: CloudWatch エージェント設定ファイルを作成する

CloudWatch エージェント設定ファイルは、エージェントが収集するメトリクスとログを指定する JSON ファイルです。

これから、CloudWatchAgentAdminPolicy を持ったロールが適用された Amazon EC2 Windows インスタンスを紹介します。CloudWatch エージェント設定がどのように作成されるのか、また AWS Systems Manager パラメータストアにどのように保持されるのかについて説明したいと思います。まず、SSM Agent と CloudWatch エージェントの両方がインストールされている Amazon EC2 Windows インスタンスに接続し、以下の注のセクションで説明されているように適切な IAM ロールを適用して設定します。

注: インスタンスが CloudWatch エージェントをインストールして実行し、メトリクスを Amazon CloudWatch に発行するだけでよい場合は、ポリシー CloudWatchAgentServerPolicy を持ったロールで十分です。

Navigate to C:\Program Files\Amazon\AmazonCloudWatchAgent and create a file something like config.json.命名制限はありません。

以下の設定例では、パフォーマンス関連の問題をトラブルシューティングするために頻繁に使用される重要なメトリクスがいくつかあります。これは、DBA やシステム管理者に役立つでしょう。設定例では、SQL Server メトリクス、システムメトリクス、および SQL Server エラーログをすべて 1 か所にまとめています。発行は、Amazon CloudWatch に定期的に 5 秒間隔で行われます。Amazon CloudWatch で、たとえば CPU Utilization にシステムメトリクスがある場合、また Batch Requests/秒に SQL Server メトリクスがある場合、それらを使用してメトリクスを相互監視し、オーバーレイすることで、処理する Batch Requests/秒の低減を引き起こす CPU Utilization しきい値の増加のような問題を特定できます。SQL Server のワークロードに合った適切なカスタムメトリクスについては、Windows Performance Monitor を通じて利用でき、公開されているすべてのメトリクスに関する Microsoft のドキュメントをご覧ください。

単位、ディメンションの設定、ポーリング間隔の設定、およびカウンターの名前の変更については、「CloudWatch Agent Configuration File Schema Definition」をご覧ください。

{
   "agent":{
      "metrics_collection_interval":5,
      "logfile":"c:\\ProgramData\\Amazon\\AmazonCloudWatchAgent\\Logs\\amazon-cloudwatch-agent.log"
   },
   "metrics":{
      "namespace": "sqlserver",
      "metrics_collected":{
         "LogicalDisk":{
            "measurement":[
              {
                 "name":"% Free Space",
                 "rename":"FreeStorageSpaceInPercent",
                 "unit":"Percent"
              },
              {
                 "name":"Free Megabytes",
                 "rename":"FreeStorageSpaceInMB",
                 "unit":"Megabytes"
              }
            ],
            "resources":[
               "C:",
               "D:",
               "_Total"
            ]
         },
         "Processor":{
            "measurement":[
               {
                  "name":"% Processor Time",
                  "rename":"CPUUtilization",
                  "unit":"Percent"
               }
            ],
            "resources":[
               "_Total"
            ]
         },
         "Memory":{
            "metrics_collection_interval":5,
            "measurement":[
              {
                 "name":"Available MBytes",
                 "rename":"FreeableMemory",
                 "unit":"Bytes"
              },
              {
                 "name":"Pages/Sec",
                 "rename":"PagesRetreivedPerSecFromDisk",
                 "unit":"Count/Second"
              }
            ]
         },
         "SQLServer:SQL Statistics":{
            "measurement":[
              {
                 "name":"SQL Compilations/sec",
                 "rename":"SQLCompilationsPerSec",
                 "unit":"Count/Second"
              },
              {
                 "name":"SQL Re-Compilations/sec",
                 "rename":"SQLReCompilationsPerSec",
                 "unit":"Count/Second"
              },
              {
                 "name":"Batch Requests/sec",
                 "rename":"BatchRequestsPerSec",
                 "unit":"Count/Second"
              }
            ]
         },
         "SQLServer:Access Methods":{
            "measurement":[
              "Page Splits/sec",
              "Forwarded Records/sec",
              "Full scans/sec"
            ]
         },
         "SQLServer:General Statistics":{
            "measurement":[
              "Processes blocked",
              {
                 "name":"User Connections",
                 "rename":"DatabaseConnections",
                 "unit":"Count"
              }
            ]
         },
         "SQLServer:Buffer Manager":{
            "measurement":[
              "Page life expectancy",
              "Page writes/sec",
              "Page reads/sec",
              "Buffer cache hit ratio",
              "Checkpoint pages/sec"
            ]
         },
         "SQLServer:Memory Manager":{
            "measurement":[
              "Memory Grants Pending"
            ]
         }
      }
   },
   "logs": {
     "logs_collected": {
       "files": {
         "collect_list": [
           {
             "file_path": "c:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG",
             "log_group_name": "sql-error.log",
             "timezone": "UTC",
             "log_stream_name":"sql_error_log_stream",
             "timestamp_format":"%H:%M:%S %y %b %-d"
           }
         ]
       },
       "windows_events": {
         "collect_list": [
           {
             "event_name": "System",
             "event_levels": [
               "INFORMATION",
               "ERROR"
             ],
             "log_group_name": "System",
             "log_stream_name": "System",
             "event_format": "xml"
           },
           {
             "event_name": "Application",
             "event_levels": [
               "WARNING",
               "ERROR"
             ],
             "log_group_name": "Application",
             "log_stream_name": "Application",
             "event_format": "xml"
           }
         ]
       }
     },
     "log_stream_name": "windows_log_stream"
   }
}

ステップ 4: CloudWatch エージェント設定を AWS Systems Manager パラメータストアに保存する

SQL Server をクラスタに設定したり、分散アベイラビリティーゾーンに展開したり、新しいノードを起動して既存のフリートに追加することができます。AWS Systems Manager パラメータストアに保存されている設定を使用して、すべての SQL Server ノードに同様の設定をシームレスに適用することは難しくありません。

Windows 上で AWS CLI を使用して設定をパラメータストアに保存できます。AWS CLI が Amazon EC2 Windows インスタンスにインストールされていない場合は、「Install the AWS Command Line Interface on Microsoft Windows」をご覧ください。フリート内のインスタンスの 1 つに特権を付与でき、それを通じて設定を AWS Systems Manager パラメータストアに保持できるようにします。この設定は後でパラメータストアから他のフリート全体に適用するために使用できます。

AWS Configure コマンドを実行し、設定を保存したいリージョンを指定することで、AWS CLI を特定のリージョンに設定します。アクセスキーとシークレットキーを入力する必要はありません。Amazon EC2 Windows インスタンスには、設定を AWS Systems Manager パラメータストアに発行するために CloudWatchAgentAdminRole がすでに適用されているためです。

注: JSON ファイルには 4096 文字の制限があります。未処理の JSON を使用するためにはスペースを削除することをお勧めします。

aws ssm put-parameter –name “parameter name” –type “String” –value file://configuration_file_pathname

注: パラメータ名はプレフィックス「AmazonCloudWatch-」で始まる必要があります。

コマンドを正常に実行した後、設定は AWS Systems Manager パラメータストアに保存されます。

AWS マネジメントコンソールでは、AWS Systems Manager コンソールに移動し、左側のナビゲーションペインで [Parameter Store] を選択します。以下のスクリーンショットのように、新しいパラメータ「AmazonCloudWatch-config」が表示されるはずです。

ステップ 5: インスタンスに SQL Server カスタムメトリクスの設定を適用する

AWS Systems Manager を使用して CloudWatch エージェントを起動または再起動し、前の手順で AWS Systems Manager パラメータストアに保存されている設定を適用できます。CloudWatch と統合するには、Run Systems か、または AWS Systems Manager の State Manager 機能を選択できます。このブログ記事では、State Manager を使用して CloudWatch と統合する方法を紹介します。

AWS Systems Manager の State Manager 機能は、マネージド型インスタンスを定義済みの状態に保つプロセスを自動化します。State Manager を適用できるようにする分野が 2 つあります。(1) 誰かが手動でインスタンス上で設定を変更しても、CloudWatch エージェントの設定は変わりません (または所望の設定にリセットされます)。(2) State Manager を使用すると、タグとの関連付け (とそれによる設定) をターゲットにすることができます。アカウント内のすべてのマネージド型インスタンスをタグでターゲット設定することもできます。それにより、新しいインスタンスが起動すると、所望の設定もそこに適用されるようにします。AWS Systems Manager の Run Command を使用して設定を行うことを覚えておく必要はありません。

次のスクリーンショットに示すように、パラメータストアに保存されている設定「AmazonCloudWatch-config」は、AWS Systems Manager コンソールのタグ「SQLCustomMetricsConfigAppliedServer」を使用して複数のインスタンスに適用されます。その後、設定はフリート全体に展開されます。関連付け「SQLServerCustomMetricsConfiguration」は、ドリフトを識別するためにインスタンス間でメトリクス設定をそのまま保持し、変更が行われると元にリセットします。

AWS Systems Manager の Run Command を使用して CloudWatch エージェントを再起動するには、「Starting the CloudWatch Agent」をご覧ください。

ステップ 6: Amazon CloudWatch コンソールで SQL Server メトリクスを監視する

他の Amazon CloudWatch メトリクスと同様に、CloudWatch エージェントで収集したメトリクスを Amazon CloudWatch に保存して表示できます。

Amazon CloudWatch コンソールに移動して、左側のナビゲーションペインで [Metrics] を選択します。[All Metrics] タブの下で、Agent 設定ファイルに記載されているネームスペース「sqlserver」を選択します。設定ファイルにネームスペースがリストされていない場合、デフォルトのネームスペースは「CWAgent」です。これで、さまざまなディメンションの下にいくつかのメトリクスが一覧表示されます。

注: 単一のメトリクスを使用して SQL Server のパフォーマンスの問題をトラブルシューティングすることはほとんど不可能です。

次の例を見てみましょう。データベース管理者が、レポートの実行時間が 2 時間ほど遅くなったという苦情を受け取りましたが、今は正常に見えます。管理者は直ちに Amazon CloudWatch コンソールの CPU 使用率の急上昇を調査し、明らかにレポートの実行が遅くなっている期間を観察します。

調整できる期間にわたって、Amazon CloudWatch コンソールの CPU Utilization メトリクスに SQL Server Batch リクエスト/秒のような他のメトリクスをオーバーレイすることで、問題はすぐに明らかになります。サーバー上の CPU Utilization が 100％急上昇したのは、サーバー上で実行中のプロセスが原因で、それにより SQL Server が CPU サイクルを使い果たし、最終的に 1 秒あたりに処理できるレポートクエリが遅くなっていたのです。

DBA はこのデータを使用して、SQL Server 上のその時間範囲でトラブルシューティングを行い、CPU Utilization の急上昇を引き起こした本当の原因となったプロセスを探すことができます。

メトリクスを検索するために使用できるその他のオプションについては、「Using Amazon CloudWatch Metrics」をご覧ください。

オプションで、Amazon CloudWatch Logs に発行された SQL Server ログとロググループ「sql-error.log」を確認することもできます。

結論

このブログ記事では、CloudWatch エージェントを Amazon EC2 Windows インスタンスにインストールし、カスタム SQL Server メトリクスとともにシステムメトリクスと SQL Server ログを Amazon CloudWatch に発行して監視する方法を学びました。カスタム SQL Server メトリクスをシステムメトリクスと一緒に Amazon CloudWatch に発行することで、選択した期間にわたってさまざまなメトリクスを比較し、それらをオーバーレイすることができます。これにより、DBA やシステム管理者にとって診断分析がはるかに簡単になります。さらに、AWS Systems Manager を使用して、SQL Server が実行されているインスタンス全体にわたって同様の CloudWatch エージェント設定をロールアウトすると、一貫したモニタリングを保証できます。これにより、データベース管理者やシステム管理者はより簡単にトラブルシューティングできます。

モニタリングの可視性を向上させるために発行されているメトリクスに加えて、それぞれが個別のビューを提供する複数のメトリクスを持つさまざまなカスタムダッシュボードを Amazon CloudWatch に構築できます。

ブログ投稿に記載されている通り、カスタムメトリクスとは別にログおよび他のカウンターを送信する方法について詳しくは、「Sending Logs, Events, and Performance Counters to Amazon CloudWatch」をご覧ください。

著者について

Nithin Reddy Cheruku は、AWS プロフェッショナルサービスのクラウドインフラストラクチャアーキテクトで、 DevOps と人工知能を専門にしています。彼はテクノロジーを使って現実世界で起きる日常的な問題を解決するのが好きです。仕事以外では、Nithin はクリケットと卓球が趣味です。

Amazon Web Services ブログ