MSSQL Log Collector (Lazylog) 使用ガイド

Classic/VPC環境で利用できます。

紹介

MSSQLログ収集機(LazyLog)概要および必須内容

ログ収集機の概要 [必読]

SQL サーバーを運用する時、モニタリング対象サーバーの設定や、プログラムのクエリー性能、サーバーのイベントログ、性能カウンターのベースラインを収集しておくことは非常に重要です。
このようなベースラインデータがなければ、現在のサービスが正常かどうか(昨日と違うのか)を判断するのに長い時間がかかる可能性があります。このため、 NAVERクラウドではSQLサーバーログ収集プログラムを配布しています。収集されたデータは障害分析や性能分析用の必須データ(ウィンドウ性能カウンター、ウィンドウイベントログ、SQLエラーログAgentログ、クエリー構文、クエリー実行計画、待機、ロックなど必須DMVスナップショット)です。

ログ収集コンソールアプリケーション(LazyLog)はSQL Server 2008、2012、2014、2016をサポートします。 なお、サーバーリソースが許す限り無制限の対象サーバー、多重SQLインスタンス、多重データベースをサポートします。 ログ収集機プログラムは、一つのサーバーで同時に複数動作させることができます。この時はフォルダを分離して別々に実行すれば問題ありません。同じようなサーバーグループを束ねてモニタリングすると、サーバーの設定やリソース使用量が比較できるため、正常であるかどうかがより容易に判断できます。このため、対象を適切に分離して構成することをお勧めします。様々なモニタリング対象SQLサーバーを一つのグループにモニタリングするなら、ウィンドウサービスプログラムでも使用できます。 (設定完了後、Setup.exeを起動させて設定ファイルを修正した後、サービスを開始)。

LazyLogは、ユーザ定義値(SqlLogManager)ベースで、対象サーバーにアクセスして特定のデータを収集し、保存期間の過ぎたデータは自動的に削除します。
収集周期を過度に短く設定すると、多くのログを保存してデータ保存用のデータベースが非常に大きくなったり、収集プログラムが誤動作することがありますので、収集周期のコントロールやデータ保管周期を適切に設定する必要があります。 さらに、プログラムが実行されるため、OSの余裕メモリを少なくとも3GB以上残すことを勧告します。 (この勧告余裕メモリはSQL Server運用時にOSに残しておくべきメモリに追加的に確保するメモリです。）ログ収集プログラムは、複数のモニタリング対象サーバーから受け取ったデータをメモリに臨時保管してSQL Server 保存先のデータベースに大量に入力(Bulk Insert)します。

管理対象となるSQLサーバーが非常に重要であれば、該当SQLサーバーにアプリケーションが設置されないことが望ましいでしょう。LazyLogは すべてのデータが遠隔で収集できるように開発されており、ローカルSQLサーバーを対象にしてもモニタリング可能です。 このプログラムは数TB容量と毎秒数万Batch Requestsを遂行するSQLサーバー数十機を対象にログ収集及び分析をしています。 遠隔ログオンおよびデータ収集のために、ウィンドウ偽装アカウント（Windows Impersonation）とSQL認証がサポートされます。 性能カウンターとイベントログを遠隔から読み込むために適切な権限とファイアウォールの開放が必要です。

ウィンドウ性能カウンターとイベントログ収集のために、必ずウィンドウ認証(Active Directory or WorkGroup)を使用しなければならないため、SQL Server状態のモニタリングのためにWindows認証とSQL認証をサポートします。 (SQL認証をお勧めしますが、基本的にSQL認証が設定されています。)アカウントの暗号設定は必ずLazyLog.exeプログラムで暗号化された値(安全な暗号保管のために)を作成して使わなければなりません。設定は二ヵ所に保存されますが、最初のLazyLog.exe.configというXMLファイルにはグローバル設定情報と情報設定のデータベースアクセス情報、収集情報保存用データベース設定情報が保存されます。二番目のSqlLogManMangerというデータベースに収集対象となるサーバーの接続情報が入力されなければなりません。

ログ収集機アプリケーションプログラム(LazyLog.exe)、あるいはサービスアプリケーションプログラムが設置されるサーバで(1)のモニタリング対象となるサーバーの情報が入っているデータベース(SqlLogManManger)と収集データ保存用のデータベース(SqlLogManDmvRepository、SqlLogManPerfmonRepository)サーバーにアクセスできるか、(2)のモニタリングするサーバーを対象に性能カウンターモニタリングができるか、(3)のモニタリング対象となるSQL Serverにアクセスできるかなど、アカウントや権限のテストを先に行ってから設置すれば、迅速に設定を終えることができます。

収集・保存データベースが遅くなると、ログ収集プログラムメモリが過度に使用される可能性があるので、ログ収集プログラムはデータベースサーバと切り離して作動させることが望まれます。 多数のサーバーをモニタリングする場合、保存先ストレージとしてSSDをおすすめします。 少数のサーバーをモニタリングしてみてから、ゆっくりと対象を増やしていき、収集・運営サーバー環境においてログ収集機プログラムのリソース使用量を判断し、モニタリング対象となるサーバーを増やします。 ログ収集プログラムエラー情報は、Logフォルダに日別・アイテム別に作成されることになっていますので、収集に問題があれば参考します。

構成ガイド

MSSQLログ収集機(LazyLog)のインストール構造

ログ収集機の構成方法

ログ収集機は遠隔収集が可能なので、収集対象のサーバーと別途に設置することを推奨しますが、収集対象のサーバーに直接インストールすることも可能です。 以下の幾つかの例示を挙げてインストール可能な構成を説明します。 本インストールガイドではファイアウォールとアカウント設定に制限がないローカルの設置(構成案1)を基本に説明します。 ローカル構成をまずしてみて、LazyLogアプリケーションプログラムの構成と使い方を身につけてから、遠隔収集を試みます。

構成案 1

構成案1は最も簡単な構成であり、LazyLogの使い方を身につけるための構成です。

この構成はファイアウォールの設定が必要ではありません。 ログ収集プログラムをモニタリング対象となるSQL サーバーに直接インストールし、モニタリングデータベースも同じSQLサーバーに作成して使用する方法です。 この場合、ネットワークを介しないため、性能カウンターやイベントログの収集速度は速いが、ログ収集プログラムが異常作動したり(一般に、Agentプログラムが運営サーバーで作動する場合の問題)SQLモニタリングデータベースの容量が増える場合、運用サーバーに影響を与える可能性があるので、勧告はしません。

1台のサーバーのみモニタリングする場合は、DMVや性能カウンタ情報だけをデータベースに保存し、イベントログはデータベースに保存しなくても問題ありません。 (直接イベントログを照会したほうが便利なこともあるためです。) しかし、ある特定のイベントのアラームを送ろうとする時はイベントログをSqlLogManPerfmonRepository(基本保存先の名称であり変更可能です。)に保存すれば、より便利に管理できます。

構成案 2

構成案2はほとんどのモニタリングサーバーに推奨されるモデルです。

ユーザサーバにはいかなるプログラムもインストールせずに、モニタリングサーバを別途に構成し、該当サーバにSQL ServerをインストールしてManagerDB(SqllLogManManager)、DMV Repository(SqlLogManDmvRepository)、Perfmon EventLog(SqlLogManPerfmonRepository)を作り、ログ収集プログラムはモニタリング対象となるサーバの情報を遠隔で収集して、SQL モニタリングデータベースにデータを保存します。

モニタリングサーバーを別途に構成し、多数のサーバーをモニタリングすると、性能カウンター収集にネットワークの帯域幅に負担がかかることがあります。ManagerデータベースのConfigテーブルにPerfmon項目を適切に修正して使います。 (配布されたプログラムのデフォルト値は、同一のIDC内にある遠隔サーバーをモニタリングするのに適切な値に設定されています。)

モニタリングサーバーに問題が生じたり、モニタリング対象となるサーバーの問題がモニタリングによって発生したかを検証するためには、モニタリング対象となるサーバーで、モニタリングサーバーに設定されたアカウント情報を遮断したり収集プログラムを停止してみると、モニタリングが該当データベースに問題を起こしているかどうかが簡単に検証できるというメリットがあります。

このモニタリングモデルは、モニタリング対象となるサーバーのリソースがほとんどなく、(対象サーバーのCPU 100%など)モニタリングクエリーも作動しない場合、データを収集できない可能性があるという問題はあります。この問題は遠隔モニタリングモデルにおいては避けて通れない問題です。 構成案1を適用しても完全なリソース不足の状況ではモニタリングデータが保存できないことができます。

一部のISP会社では445ポート開放が不可能なので、モニタリングサーバをモニタリング対象となるサーバと同じIDCにインストールすることを推奨します。 このモデルは独立SQL Serverがモニタリングにのみ使用されるため、ライセンス費用が負担になることがあります。 適切なCALライセンスモデルなら役立てることができます。

構成案 3

SQL Serverライセンス問題があれば、サーバの使用量が低いサーバでモニタリングプログラムとモニタリングデータベースを一緒にインストールして運営することができます。

構成案 4

ログ収集プログラムのリソース使用量が負担になる場合は、モニタリングプログラムのみ他のサーバーを利用して構成することができます。 また、上の構成案1、2、3、4クソンを適切に混合して構成可能です。
ただし、構成案4と一緒にSQLモニターリングデータベースが他のインスタンスやサーバで構成されれば、あらかじめ構成された同意語2つを適切に修正することで正常なモニタリングが可能になります。
(同義語はSqlLogManManagerデータベースにSN_CounterDetailsdとSqlLogManPerformanceRepositoryデータベースにCounterDetailsAutoUpdatedがあります。)

同意語 : https://msdn.microsoft.com/ja-jp/library/ms187552.aspx

収集データ

MSSQLログ収集機(LazyLog)で収集可能なデータ。

性能カウンター

ウインドウ性能カウンターの収集が可能であり、SqlLogManManager.dbo.CounterDetailsFilterinfoテーブル情報を基盤にどのカウンターがサーバーにあるのかを読み込み、読み込んだカウンターはSqlLogManager.dbo.CounterDetailsAutoUpdatedに保存されます。
SqlLogManManager.dbo.CounterDetailsFilterinfoのFilterType [IN]は収集対象となるオブジェクトであり、FilterType [NOTIN]は収集対象から除外されるオブジェクトです。
この値はインストール後、ユーザーが直接修正することができます。

SqlLogManManager.dbo.CounterDetailsFilterInfo

SqlLogManManager.dbo.CounterDetailsAutoUpdated

このテーブルの構成は、ウインドウ性能カウンタースキーマと同じであり、上記のフィルター情報に該当するカウンター情報全てを保存しています。 IsEnabledYNフィールドのみ追加されましたが、このフィールドがYであることだけがSqlLogManPerfmonRepositoryデータベースに15秒ごとに保存されます。 (保存間隔の制限:ローカルは少なくとも5秒、リモートは少なくとも15秒)
実際に保存されるテーブルは、SqlLogManPerfmonRepositoryのCounterData, CounterDetails, DisplayToIDです。 基本的に収集されるY項目を修正したいのであれば、インストールフォルダのResourcesでUpdateCounterDetailsY.sqlを修正してアップデートすればよいです。 元のカウンター項目にはないディスクボリュームごとのFree、Total、Used Megabytesを計算して入力できるようなデザインとなっていますので、役に立ててください。

この3つのテーブルのスキーマは https://msdn.microsoft.com/ja-jp/library/aa371915(VS.85).aspxを参考にします。

ディスクボリューム別Free、Total、Used Megabytes

収集が正常に行われる場合、下記のようなデータが照会可能です。

SQLCMD コマンド [サーバー]

select * from SqlLogManPerfmonRepository.[dbo].[DisplayToID]
select * from SqlLogManPerfmonRepository.[dbo].[CounterDetails]
select * from SqlLogManPerfmonRepository.[dbo].[CounterData]

正常に実行中のデータをSSMSで照会した結果

ウィンドウ実行でodbcad32を実行(ODBCデータソース管理者(64ビット)および設定の遂行後、ウィンドウ搭載の性能カウンタプログラムに連結すると、保存された記録が見られます。 (ウィンドウアカウントのみ可能ですので、該当ウィンドウアカウントがモニタリング収集サーバーにログイン及びデータ_reader権限がなければなりません。)

ODBC 構成

LazyLogで収集されたデータをPerfmonアプリケーションプログラムで照会

イベントログ

サーバのApplication, Security, Setup, System イベントログの読み込みが可能であり、特定の情報をフィルタして保存することも可能です。 このイベント情報のサーバー別のフィルターは、SqlLogManManager.dbo.CollectItemテーブルのItemOptionに定義されています。

この情報はSqlLogManManager.dbo.CollectItemTemplateテーブルを基準にSqlLogManManager.dbo.usp_register_CollectItemプロシージャを通じて入力され、個別の修正や全体の修正が可能です。

SQLCMD コマンド

select * from SqlLogManPerfmonRepository.[dbo].[EventLogApplication]
select * from SqlLogManPerfmonRepository.[dbo].[EventLogSecurity]
select * from SqlLogManPerfmonRepository.[dbo].[EventLogSetup]
select * from SqlLogManPerfmonRepository.[dbo].[EventLogSystem]

LazyLogを通じてApplicationログを保存した場合の照会結果

保存されたイベントログは、直接 ウィンドウズ の基本 eventvwrアプリケーションプログラムでの読み込みと同様に保存されています。 SMS伝送のためにIsCheckedYNフィールドを追加しておきましたので、
特定のイベントにメッセージを送る際に有効に使ってください。 例題でEventLogSecurity、EventLogSetup、 EventLogSystemは基本モニタリングにすぐ’追加できる項目です。

DMV (Dynamic Management View)

Always On Availabilityの実行状況、現状、クエリー別のリソース使用量、クエリー、クエリープラン、ロック、待機分析、ファイル別のI/O量、インデックス構造、統計日付、サーバメモリ現況、Agent実行記録などを収集し保存しています。
LazyLogはSQL Serverの運営、問題解決に役立てるほとんどの情報を収集しています。 詳しいモニタリング項目は以下の表を参照します。

重要度はLazyLog開発者がSQL Serverのモニタリングをする際に主にチェックしなければならない内容を1としており、特殊な問題解決やその重要度が低いのは3と設定しました。

特に問題がなければ、すべての設定を活性化してモニタリングします。 保存スペースの問題があれば、アイテムの収集周期を変更するか、削除または限界時間を調節すればいいです。

データベーススキーマ

MSSQLログ収集機(LazyLog)データベース構造

SqlLogManManager

収集対象となるサーバー情報を保存するテーブルと、収集設定に関するすべての設定が入っているデータベースです。 (このデータベースとlazylog.exe.configファイルさえバックアップすれば、いつでも収集サーバーが復旧できます。)

SqlLogManDmvRepository

DMV収集結果データが保存されるデータベースです。

SqlLogManPerfmonRepository

性能カウンターとイベントログを保存するデータベースです。

設置

MSSQLログ収集機(LazyLog)のインストールガイド

プログラムインストール

概要

1. 暗号を平文で保存すると、ハッキングやセキュリティに問題があるので、暗号を再度暗号化して使用しなければなりません。 [必須]
2. 性能カウンターとイベントログをモニタリング対象となるサーバーで読み込むため、対象サーバーでウィンドウアカウント設定、権限設定、必須サービス開始、ファイアウォールの設定が必要です。
3. モニタリング対象となるサーバーを対象に、モニターサーバーで性能カウンター、イベントログ、DMVが照会できるか先に確認が必要です。
4. 保存先のデータベース3個を作成します。
5. lazylog.exe.config ファイルには、項目のグローバル設定と収集機の設定が保存されるデータベース、収集結果が保存されるデータベース情報が記録されます。
6. SqlLogManManagerデータベースに収集対象となるサーバーの接続情報を保存します。
7. SqlLogManManagerデータベースにサーバー別の収集アイテムを登録します。 (性能カウンターを収集しているかどうか、イベントログの各項目を収集しているかどうか、DMV各項目を収集しているかどうかなど)
   • 設定が複雑で登録スクリプトを作成してくれるプロシージャが提供されるので、スクリプトを作成してSQLCMD やSSMSで実行します。
8. 基本収集と拡張収集がありますが、拡張収集の場合
   • SqlLogManDmvRepository.dbo.sysdatabases_bの収集が完了しなければなりません。 (基本アイテムの中からsysdatabasesを収集しなければならない)
9. SqlLogManDmvRepositoryデータベースには、SQL Server関連のモニタリング項目が保存されます。
10. SqlLogManPerfmonRepositoryデータベースには、性能カウンターとイベントログ収集結果が保存されます。

コンソールモード設置

LazyLog.zipファイルの圧縮を解凍すると、以下のようなファイルがあります。

探索機の表示オプションでファイル拡張名にチェックをしないと、LazyLog.exeはLazyLog、LazyLog.exe.configはLazyLog.exeに見えますので、LazyLog.exe.configとLazyLog.exeを混同しないように注意します。

暗号作成

LazyLog で使用する暗号は、LazyLog を介して作成した、暗号化された文字列を使用しなければなりません。 暗号化された文字列の作成は、cmd で LazyLog 「暗号文字列」 を入力して作成します。 下記は3つの暗号を作成した例題です。 テスト設定の場合、すべて同じ暗号を設定するのも良い方法です。 しかし、実際のモニタリング環境ではサーバーごとの暗号が全て異なることがあります。
テストの場合、暗号もガイドと同じように作成して実習すればさらに簡単に設定 できます。

モニタリング対象となるサーバーの設定

1. サーバー設定
   lazyNtUserは、SqlLogManManagerデータベースに設定されるウィンドウの仮想の認証アカウントです。 下記は、モニタリング対象となるサーバーで実行すべきコマンドであり、管理者モードで実行される必要があります。 モニタリングの保存先となるサーバーと、モニタリング対象となるサーバーが同じである場合、下記の赤いコマンドは実行しなくてもいいです。

• ウィンドウCMDコマンド [クライアント]

  sc config remoteregistry start=auto
  net start remoteregistry
  netsh advfirewall firewall set rule name="Netlogon サービス(NP-In)" new enable=yes
  net user lazyNtUser P@ssw0rd2 /ADD
  net localgroup "Performance Log Users" lazyNtUser /add
  net localgroup "Performance Monitor Users" lazyNtUser /add
  net localgroup "Event Log Readers" lazyNtUser /add
  

2. SQL Server 設置
   lazyClientというアカウントは、SqlLogManagerデータベースに設定されるアカウント情報です。 SQL Server 管理者で下記の命令を SQLCMD モードで実行します。 SQLScriptサブフォルダにL03_Account_lazyClient.sqlファイルを開きます

   • SQLCMD コマンド [クライアント]

     -- 変数修正
     :setvar AccountName lazyClient
     :setvar AccountPassword P@ssw0rd1
     -- 変数修正完了
     

   • lazyClient SQL認証アカウントがP@ssw0rd1という暗号で作成されます。

本ガイドのすべてのSQLクエリー文はSQLCMDモードで作動させる。 SSMSでSQLCMDモードで作動させるため、以下の説明を参照します。

SQLCMDモード変換方法	一般モード	SQLCMDモード(変数の部分が反転している)

モニタリングの保存先となるデータベースの作成

1. 保存先のデータベースが作成されるフォルダを作成します。

   • ウィンドウCMDコマンド

     mkdir c:\lazylog
     

     フォルダが作成されれば問題ありません。 (データベース名は自由に変更可能です。 後で該当フォルダにデータベースが作成されます。) 初めての実習ではそのまま維持します。

2. SQLScriptサブフォルダにL01_InitScript.sqlファイルを開きます。

   • SQLCMD コマンド

     -- 変数修正
     :setvar SqlLogManManagerDatabase SqlLogManManager
     :setvar SqlLogManManagerMdfPath c:\lazylog
     :setvar SqlLogManManagerLdfPath c:\lazylog
     :setvar SqlLogManDmvRepositoryDatabase SqlLogManDmvRepository
     :setvar SqlLogManDmvRepositoryMdfPath c:\lazylog
     :setvar SqlLogManDmvRepositoryLdfPath c:\lazylog
     :setvar SqlLogManPerfmonRepositoryDatabase SqlLogManPerfmonRepository
     :setvar SqlLogManPerfmonRepositoryMdfPath c:\lazylog
     :setvar SqlLogManPerfmonRepositoryLdfPath c:\lazylog
     -- 変数修正完了
     

     このスクリプトはSqlLogManManager、SqlLogManDmvRepository、SqlLogManPerfmonRepository 3つのデータベースを作るスクリプトです。 変数修正の部分を確認し、SQLCMDモードで実行します。 以下のようにデータベース3つが作成されます。

モニタリング保存先となるアカウントの作成

上記の段階で作成したデータベースにアクセスするアカウントを作成します。

1. SQLScriptサブフォルダにL02_Account_lazyServer.sqlファイルを開きます。

   • SQLCMD コマンド

   -- 変数修正
   :setvar AccountName lazyServer
   :setvar AccountPassword P@ssw0rd3
   -- 変数修正完了
   

2. 変数を修正し、SQLCMDモードで実行します。
   lazylogアプリケーションが起動するところでip、port、アカウントにSSMSでモニタリング保存先となるサーバにログインできるか必ずテストします。 (SSMSでログインする場合は元の暗号P@ssw0rd3でアクセス)GUIに設定する場合、オプションによって暗号を変更することでログインできる場合があります。 lazyServerの暗号P@ssw0rd3はLazylog.exe.configに入れる時は qcWa7DCqnN7vqMZ6b9ilzw== で暗号化された値を入力します。

3. 作成したアカウントとIPパスワードでLazyLog.exeファイルが起動するサーバーで、保存先となるデータベースサーバーにIPとポートでアクセスできるかをテストします。

LazyLog.exe.config 設定

このファイルは、LazyLogアプリケーションが上記の段階で作成したデータベースにアクセスする情報を記入する段階です。

notepadで修正すればいいです。 IP、Port、アカウント名、暗号化されたアカウントパスワードを適切に修正します。 127.0.0.1、1433にアクセスする場合、SSMSでアクセスできるかテストした後に設定します。

<appSettings>
  <add key="FileLogYN" value="Y" />
  <add key="UsePerfmonYN" value="Y" />
  <add key="UseEventmonYN" value="Y" />
  <add key="UseSqlmonYN" value="Y" />
  <add key="UseLocalKey" value="Y" />
  <add key="NClavisKey" value="" />

  <add key="PerfmonItemName" value="BaselineCollect" />

  <add key="SqlLogManManagerInstanceName" value="AD01047147" />
  <add key="SqlLogManManagerIP" value="127.0.0.1" />
  <add key="SqlLogManManagerPort" value="1433" />
  <add key="SqlLogManManagerDatabase" value="SqlLogManManager" />
  <add key="SqlLogManManagerIsNtAuthYN" value="N" />
  <add key="SqlLogManManagerUserName" value="lazyServer" />
  <add key="SqlLogManManagerPassword" value="qcWa7DCqnN7vqMZ6b9ilzw==" />

  <add key="SqlLogManDmvRepositoryInstanceName" value="AD01047147" />
  <add key="SqlLogManDmvRepositoryIP" value="127.0.0.1" />
  <add key="SqlLogManDmvRepositoryPort" value="1433" />
  <add key="SqlLogManDmvRepositoryDatabase" value="SqlLogManDmvRepository" />
  <add key="SqlLogManDmvRepositoryIsNtAuthYN" value="N" />
  <add key="SqlLogManDmvRepositoryUserName" value="lazyServer" />
  <add key="SqlLogManDmvRepositoryPassword" value="qcWa7DCqnN7vqMZ6b9ilzw==" />

  <add key="SqlLogManPerfmonRepositoryInstanceName" value="AD01047147" />
  <add key="SqlLogManPerfmonRepositoryIP" value="127.0.0.1" />
  <add key="SqlLogManPerfmonRepositoryPort" value="1433" />
  <add key="SqlLogManPerfmonRepositoryDatabase" value="SqlLogManPerfmonRepository" />
  <add key="SqlLogManPerfmonRepositoryIsNtAuthYN" value="N" />
  <add key="SqlLogManPerfmonRepositoryUserName" value="lazyServer" />
  <add key="SqlLogManPerfmonRepositoryPassword" value="qcWa7DCqnN7vqMZ6b9ilzw==" />
  <add key="ClientSettingsProvider.ServiceUri" value="" />
</appSettings>

• 残りのSqlLogManDmvRepositoryXXとSqlLogManPerfmonRepositoryXXは、DMV結果を保存するデータベースと性能カウンターとイベントログを保存するデータベース情報です。

Hostsファイル追加

モニタリングサーバーでモニター対象となるサーバーに名前でアクセス不可能する場合、windows/system32/drivers/etc/hostsファイルに追加して名前でアクセスができるようにします。 ワークグループ環境でのホストネームの追加は、性能カウンターとイベントログ収集に必須です。

モニターサーバーに対象となるサーバーの情報入力

モニタリング対象となるサーバーを登録する過程です。 SQLScriptサブフォルダでL04_TargerServerRegister.sqlを開き、下記の変数を修正してSQLCMDモードで実行します。

• SQLCMD コマンド

-- 変数修正

:setvar ManagerDatabaseName SqlLogManager
-- 設定が保存されるデータベース名
:setvar IdcName NCloud
-- サーバ位置 (プログラムに影響なし)
:setvar MachineName AD01047147
-- モニタリング対象となるサーバーのホスト名(遠隔の場合は、hostsに登録してアクセス可能にしておくこと)
:setvar MachinePublicIp 127.0.0.1
-- モニタリング対象となるアイピー
:setvar lazyNtUserName lazyNtUser
-- 性能モニターとイベントログを読み込む対象となるサーバーに作成したウィンドウ認証
:setvar lazyNtUserPasswordEncrypted XckO0Pvn7Lq0GjFVAjehhg==
-- 上記アカウントの暗号(lazylog.exeで暗号化することで作動します) lazylog.exe"P@ssw0rd2"と一緒に作成)
:setvar NtDomainNameIfNotAdEnvHostname AD01047147
--- AD環境であれば、ホストネームを除くドメイン名 hostname.adServer.com -> adServer.comだけ入力、workgroupの場合はhostname入力
:setvar FullInstanceName AD01047147
--モニタリング対象となるSQLインスタンスの名前(当該サーバでsysserversの0度も名前)
:setvar SqlUsername lazyClient
-- モニタリング対象となるSQLインスタンスに設定されたSQL adminアカウント
:setvar SqlPassword BaQJi8Br/qBUv0lq/F1Z1Q==
-- 上記アカウントの暗号(lazylog.exeで暗号化すると動作します) lazylog.exe"P@ssw0rd1"と一緒に作成)
:setvar RoleName テストサーバー
-- サーバメモ (プログラムに影響なし)
:setvar SqlServerMajorVersion 2014
--SQLサーバーの主なバージョン2008、2012、2014、2016の一つの値
:setvar SqlPort 1433
-- モニタリング対象となるサーバーのポート

-- 変数修正完了

基本モニタリング登録

SQLScriptサブフォルダでL05_TargetItemRegisterBasic_01.sqlファイルを開き、下の変数を修正してSQLCMDモードで実行します。

-- 変数修正

:setvar machineName AD01047147
-- MachineNameは hostname
:setvar FullInstancename AD01047147
--FullInstanceNameは該当サーバーのselect srvname from sysservers where srvid=0、結果値です
:setvar SqlLogManManagerDatabase SqlLogManager
-- lazylog すべての設定が保存されたマネージャデータベース名 (モニタリング保存先のデータベース)

-- 変数修正完了

LazyLog.exe 実行

ウインドウ探索機で、LazyLog.exeをダブルクリックして実行します。 下記は、実行中のコンソールモード LazyLog.exeが正常に実行されている様子です。

基本モニタリングの正常収集を確認

1. 性能カウンター正常に収集されているかを確認するため、LazyLogが起動してから約2分後、下のテーブルを確認します。

   • SQLCMD コマンド

     select * from SqlLogManPerfmonRepository.[dbo].[DisplayToID]
     select * from SqlLogManPerfmonRepository.[dbo].[CounterDetails]
     select * from SqlLogManPerfmonRepository.[dbo].[CounterData]
     

     次のように出力されていれば、正常に収集されています。

2. イベントログ
   正常に収集されているかを確認するため、LazyLogが起動してから約2分後、下のテーブルを確認します。

   select * from SqlLogManPerfmonRepository.[dbo].[EventLogApplication]
   select * from SqlLogManPerfmonRepository.[dbo].[EventLogSecurity]
   select * from SqlLogManPerfmonRepository.[dbo].[EventLogSetup]
   select * from SqlLogManPerfmonRepository.[dbo].[EventLogSystem
   

   次のように出力されていれば、正常に収集されています。

DMV確認

基本モニタリングが正常に実行されているかを確認するために、SqlLogManDmvRepository.dbo.sysdatabases_bテーブルの内容を確認します。 この内容が正常に収集されてこそ、拡張モニタリングが登録できます。

• SQLCMD コマンド

  select * from SqlLogManDmvRepository.dbo.sysdatabases_b order by probe_time desc
  

以下のようにユーザデータベースのリストが表示されると、正常に収集が完了していることを意味します。

拡張モニタリング登録

1. ユーザーデータベース情報の確認およびコピー
   拡張モニタリングは、基本モニタリング項目のうち、上記で確認したsysdatabaseが正常に収集されないと収集できません。 SQLScriptサブフォルダでL06_TargetItemRegisterAdvanced_01.sqlファイルを開き、下の変数を修正した後、SQLCMDモードで実行するとユーザーのデータベース情報をSqlLogManManager.dbo.MachineSqlDatabaseに入力するようになります。

• SQLCMD コマンド

  -- 変数修正
  :setvar machineName AD01047147
  -- モニタリング対象hostname
  :setvar SqlLogManManagerDatabase SqlLogManager
  -- lazylog すべての設定が保存されたマネージャデータベース名 (モニタリング保存先のデータベース)
  :setvar SqlLogManDmvRepositoryDatabaseSqlLogManDmvRepository
  -- DMV収集情報が保存されるデータベース名
  -- 基本アイテムの収集でsysdatabasesが収集されてこそ正常登録が可能です。
  -- 変数修正完了
  

  正常に実行されると下記のように出力されます。.
  

2. 拡張モニタリング登録スクリプト作成
   SQLScriptサブフォルダでL06_TargetItemRegisterAdvanced_02.sqlファイルを開き、下の変数を修正後SQLCMDモードで実行します。

• SQLCMD コマンド

  -- 変数修正
  :setvar machineName AD01047147
  -- モニタリング対象hostname
  :setvar SqlLogManManagerDatabase SqlLogManager
  -- lazylog すべての設定が保存されたマネージャデータベース名 (モニタリング保存先のデータベース)
  -- 変数修正完了
  

  正常実行されると、実行するスクリプトが出力されます。
  

作成されたスクリプト中 「実行するスクリプト」となっているスクリプト2個セットをSSMSで実行します。

3. 拡張モニタリングスクリプト活性化
   SQLScriptサブフォルダでL06_TargetItemRegisterAdvanced_04.sqlファイルを開き、下の変数を修正後SQLCMDモードで実行します。

• SQLCMD コマンド

  -- 変数修正
  :setvar machineName AD01047147
  -- モニタリング対象hostname
  :setvar FullInstanceName AD01047147
  --FullInstanceNameは該当サーバーのselect srvname from sysservers where srvid=0、結果値である
  :setvar SqlLogManManagerDatabase SqlLogManager
  -- lazylog すべての設定が保存されたマネージャデータベース名 (モニタリング保存先のデータベース)
  -- 変数修正完了
  

LazyLog.exeの再起動

拡張モニタリング項目を収集するために、コンソールLazyLog.exeを再起動します。 (コンソールアプリケーション終了および起動)

拡張モニタリング正常収集確認

基本モニタリングが正常であることを確認し、拡張モニタリングが正常であることを確認する場合、sp_spaceused_bが正常に収集されると、すべての収集が正常です。

• SQLCMD コマンド

  select * from SqlLogManDmvRepository.dbo.sp_spaceused_b order by probe_time desc
  

以下のようにdb_name別のテーブルサイズが正常出力されると、すべての収集が正常になります。

ウィンドウサービスで設置

コンソールモードですべての設定が完了すれば、LazyLogService.zipファイルを解凍して簡単にSetup.exeを実行してNextをクリックして設置を完了します。 (ウィンドサービスでLazyLogを起動する場合、一つのサービスのみ起動できるので、多数のLazyLogを起動しなければならない場合は、コンソールのモードで使用します。)

C:\Program Files\Naver Business Platform\SQL Server Log Collector
フォルダの LazyLog.exe.config ファイルの中の appSettings 部分だけ、以前に設定したコンソールアプリケーションの appSettings 設定をコピーして入れます。 cmdプロンプトでnet start 「SQL Server Log Collector」を入力してサービスを開始するか、またはservices.mscでSQL Server Log Collectorを開始します。

サービスで駆動する場合、モニタリングサーバーが起動しても自動的に収集を開始します。

すべての収集が正常であれば、FileLogYN設定をNに設定するようお勧めします。

インストール問題の解決

MSSQLログ収集機(LazyLog)インストール問題解決のためのガイド

問題解決

問題解決では例題と同じアカウントに設定したと仮定して見てみましょう。 他の問題はLogファイルを参考にして解決するようお願いします。ほとんどのエラーは、アカウントを間違えて設定したり、TCP 445ポートがISP事業者によってブロックされた場合で、この場合は性能カウンターとイベントログデータを収集できませんでした。 この場合、ブロックされたポートを使用しないように収集プログラムの位置を変更すればよいでしょう。

Machine テーブル

1. WorkGroup サーバー
   • AD01047147というホスト名を持つモニタリングサーバがあるなら、
   • windows/system32/drivers/etc/hostsにhostをaliasに登録します。
   • PingとTelnetなどで1433モニタリングSQL Serverにアクセスできるかテストします。
   • MachineNameにAD01047147を記入して、NtDomainにも同様にAD01047147を入力します。
   • NtUserNameにlazyNtUserを入力します。
   • NtPasswordに暗号化された暗号を入力します。(lazylog.exeを介して暗号化して記入します。)

• IsEnabledYN が Yなのか確認

2. Active Directory サーバー
   • モニタリング対象ホストがmhv1.minsouk.comサーバがあるなら、
   • NtDomain에 minsouk.com だけ記入します。 (hostnameは除外)
   • MachineName에 mhv1[ホスト名だけ] 記入します。(スラッシュ@などの他の内容が入ってはいけません。)
   • NtUserNameに lazyNtUser やモニタリング IDを記入します。(ドメイン名は除外)
   • NtPasswordに上のアカウントの暗号をlazylog.exeで暗号化した値を記します。
   • IsEnabledYN が Yなのか確認

MachineSqlInstance テーブル

SQLサーバーが基本のインスタンスなら、MHV１だけ入力して、命名されたインスタンスに設置されたなら、FullInstacneNameをすべて入力します。 (MHV1\SQL2016と一緒に入力したら問題ありません。)
DMV項目などをCollectItemでIsNtAuthYN値がYの場合はMachineに記録されたNtUserNameとNtPassword値を利用してアクセスし、Nの場合はMachinePublicIpとPortDefaultを利用してアクセスします。
IsEnabledYN がY であることを確認します。 下図を参照して修正します。

上記の設定中1回MachineIdはWorkGroupサーバーで、6番MachineIdは、ActiveDirectoryサーバーの設定です。 LazyLogは上記の情報をベースに見せかけて、ウィンドウ性能カウンター、イベントログ、DMVを読み込んできます。

内部ConnectionString作成ロジック

ワークグループ環境でDMVを収集するために対象サーバーにホスト名でPingやtelnetに1433ポートが正常に作動しているかよく調べるべきです(ユーザー指定のポートである場合、当該ポート確認)。 性能カウンターやイベントログ収集のためにPingや445ポートが正常に作動しているかを確認する必要があります。

アカウント別のアクセステスト

1. lazyServer
   lazylog.exe.configに設定され、収集されたデータを保存するSQL サーバーにアカウントと権限が設定されなければなりません。

• SQL Server アカウントで保存先のデータベースにアクセスができなければなりません。(lazyLog.config.XMLに設定)SSMS や SQLCMD でテストを行うときは、lazyServer アカウントが暗号化される前の暗号値で lazyLog.exe プログラムが作動するコンピュータで SqlLogManager データベースがある SQL Server にログインできるかを確認します。 (IP、Port 正確にテスト)
• lazyServerがsysadminやdbo権限を持っているか?
  

2. lazyClient
   MachineSqlInstanceに設定され、収集対象となるSQL サーバーにアカウントと権限が設定されていなければなりません。

• SQL Server アカウントでモニタリング対象となるサーバーに SSMS や SQLCMD プログラムで、該当サーバーにアクセスできるか?(lazyClient アカウントは SqlLogManManagerデータベースの Machine と MachineSqlInstance 情報で、対象サーバーにアクセスします。 SSMSでテストする時は、暗号化する前の値を入力しなければなりません)
• lazyCliecnt が sysadmin 権限を持っているか?
  

3. lazyNtUser
   Machineテーブルに設定され、対象コンピューターにアカウントと権限が設定されていなければなりません。

   • 性能カウンターとイベントログを読み込むために使用されます。(SQL認証は許可されません。)
   • 設定した暗号が正確かどうか収集対象となるサーバーにAdministratorでログインし、Runasコマンドで次のように実行します。

• runas /user:lazyNtUser cmd.exe
  

  • cmdウィンドウが正常にlazyNtUserで始まれば暗号は正確です。

• lazyNtUserの権限を確認します。 対象サーバーに設定されるべき権限は以下の通りです。
  

アカウント項目の確認

L07_FullCollectItemStatus.sqlの内容を基準に下のクエリーを実行して、IsEnabledYNにすべてYなのか?

遠隔収集のためのポートやサービスの確認

1. WorkGroup サーバー

   • ワークグループの場合、対象サーバーにlazyNtUserの暗号と同様にlazyNtUserをlazylog.exeが作動するサーバーにアカウントと暗号を同じように作成して該当アカウントにウィンドウログインをします。
   • 実行でPerfmonを行います。
   • カウンタの追加メニューで、次のコンピュータでカウンタの選択欄に収集対象IPを入力します。
   • 遠隔コンピュータの性能カウンターの収集が正常に行われているか確認します。

2. Active Directory サーバー

   • 実行で Perfmonを行います。
   • 遠隔コンピュータの性能カウンターの収集が正常に行われているか確認します。

3. ファイアウォール

   • 同一IDC内でファイアウォールのない環境でテストしたり、同一ネットワーク環境でテストして、作動しているかを確認します。
   • RPCポートがブロックされている場合、収集プログラムをファイアウォールの内側に位置させて収集結果だけ、外部のSQL Serverを指定するのも良い方法です。
   • ネットワークパケット分析プログラムを、対象コンピュータと収集コンピュータの両側にトレースをかけて、RPC 445ポートが正常であるかモニタリングします。
   • ネットワークパケット分析プログラムを対象コンピュータと収集コンピュータの両側にトレースをかけて、TCP 1433ポートが正常であるかモニタリングします。

4. モニタリング対象となるサーバーの必須サービス

   • Remote Registryサービスが活性化されているか?
   • Remote Procedure Call(RPC)サービスが活性化しているか?

5. SQL Server

   • sp_readerrorlogにどんなエラーがあるかを確認します。
   • プロファイラや拡張イベントでクエリーとエラーを確認します。

活用例

MSSQLログ収集機(LazyLog)活用例示

DMVデータ活用例示

下のダッシュボードは、上記の LazyLog 収集データを用いて単純にグラフ化したものです。 ダッシュボードやグラフを描くためにExcelやさまざまなダッシュボードツールが利用できます。

AGモニタリング

下図は、SQL サーバーの高可用ソリューションであるAlwaysOnモニタリングを構成した例です。 下図1のスタックチャートはスタック別の個別サーバーを意味しており、現在PrimaryサーバーのQueueに積もっている容量、PrimaryでSecondaryに送る速度、Secondary Queue Redoに積もっている容量、Secondary Redo反映速度をそれぞれ表現します。

中央赤いボックスを見ると、一個のサーバーのログの適用が停止された状態がモニタリングできます。 この場合、補助サーバーとしてすべてのログが転送された状態でモニタリングされ、適用のみ停止されたか、非常に遅いものと思われます。 この場合に
補助サーバーを再起動して問題を解決した場合です。 この問題をモニタリングしておらず、サービスを続けていたら、最悪の場合、AGを再構成しなければならない状況になった可能性があります。 補助サーバーでのログ適用は作動中の状態と再起動の時とで少し違うロジックでデザインされているが、再起動でログ適用を行えばもっと早く適用できます。 LazyLogにはdm_hadr_database_replica_states_bのテーブルに下記の情報が20秒単位でモニタリングされています。

サーバーリソース使用率モニタリング

下図は、同じグループのサーバーをモニタリングし、CPU、BatchRequests/Sec、Memoryの状況とネットワーク転送率を一度にモニタリングできるように構成したページの例です。 通常と異なる作動するサーバーがあれば、その場で発見できます。 LazyLogには、性能カウンター情報が15秒単位で記録されています。

クエリーリソース使用量を検証

下図は同じ役割を担う2台のサーバーを対象に、クエリーモニタリングをした一例です。 同じ役割を担うデータベースなので、クエリーも同じように流入され、CPU WorkerTimeも似たような状態でなければなりません。 ところが、特定の時間帯に1台のサーバーで特定のクエリーのみリソース使用量が高くなったことがすぐに分かる場合です。

中央バブルチャートは、X軸は実行回数、Y軸はWokerTimeです。 クエリーを見て、特定の時間帯もどのクエリーが一番リソースをたくさん消耗するのかがすぐわかります。
LazyLogのdm_exec_query_stats2_b_summaryには1分単位クォリビョルリソース使用量が詳しく記録されています。

ロックモニタリング

下記はロックをモニタリングした例です。 サーバーでロックが生じると、ロックの原因クエリーは何で、待機するクエリーがどんなクエリーなのか、すぐわかります。 LazyLog
のsp_lock2_bには10秒単位ロックモニタリングがクエリーとともに詳しく記録されています。 OLTP環境でロックモニタリングが重要であれば、収集間隔を適切に調節します。 (3秒を推奨)