紹介

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/ko-kr/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/en-us/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の運営、問題解決に役立てるほとんどの情報を収集しています。 詳しいモニタリング項目は以下の表を参照します。

項目名 重要度 項目類型 項目区分 説明
availability_replicas 2 AG (Availability Group) DMV 可用性コピーの情報
dm_hadr_auto_page_repair 2 AG (Availability Group) DMV 可用性コピーのすべての可用性データベースに対して行う自動ページ復旧に対する試みの情報
dm_hadr_availability
_group_states
2 AG (Availability Group) DMV ローカルインスタンスに可用性コピーがある各AlwaysOn可用性グループに関する情報
dm_hadr_availability
_replica_cluster_nodes
2 AG (Availability Group) DMV WSFCクラスターにあるAlwaysOn可用性グループの全ての可用性コピーに関する情報
dm_hadr_availability
_replica_cluster_states
2 AG (Availability Group) DMV WSFCクラスタにあるすべてのAlwaysOn可用性グループの各AlwaysOn可用性コピーに関する情報(コピーの位置や調印状態に関係ない)
dm_hadr_availability
_replica_states
2 AG (Availability Group) DMV 同一のAlwaysOn可用性グループのローカル複製で各ローカル可用性コピーに対する行及び各遠隔可用性コピーに対する行を返還
dm_hadr_cluster 2 AG (Availability Group) DMV クラスター名とクオラム情報の返却
dm_hadr_cluster_members 2 AG (Availability Group) DMV WSFCノードにクオラムがある場合、クオラムを構成する各メンバーに対する行と各メンバーの状態を返還
dm_hadr_database_replica
_cluster_states
2 AG (Availability Group) DMV AlwaysOn可用性グループにおける可用性データベースの状態についての情報
dm_hadr_database
_replica_states
1 AG (Availability Group) DMV 現在PrimaryサーバーのQueueに溜まっている容量、PrimaryからSecondaryに送る速度、SecondaryQueueRedoに溜まっている容量、SecondaryRedo反映速度がモニタリングできます。 この値は、新規インデックスを作成、大規模データの修正などを行う際に必須のモニタリング項目です。
dm_hadr_name_id_map 2 AG (Availability Group) DMV AlwaysOn可用性グループマッピング情報
database_principals 3 Config DMV データベースに作成されたログイン状況です。 アカウントの作成日、SID、基本データベースなどの情報
dbcc_loginfo 1 Config DMV データベースログファイルのVLF(Virtual Log File)ファイル個数や現在活性ログファイルがいくつあるか判断できます。 VLFファイルは100個未満で作成されている必要があり、この個数が過度に多ければ、該当SQL Server開始が非常に遅くなったり、バックアップ復旧が遅くなることがあります。
dbcc_tracestatus 1 Config DMV 当該データベースに活性化されたTraceFlag情報
dm_os_sys_info 2 Config DMV 物理メモリ容量、コミットメモリ容量、SQL Server開始時間、SQL Server Versionなど
dm_server_services 2 Config DMV SQL Server, SQL Server Agent, Full-text Daemonなど該当サーバーのSQLサービス開始類型、現状Service Accountなど
dm_xe_sessions 2 Config DMV サーバーで作動中の全べての拡張イベント情報
fn_trace_getinfo 2 Config DMV 全ての活性プロファイラー情報
identity_columns 1 Config DMV モニタリングするデータベースの全てのidentity属性の最大値と現在の値
server_principals 2 Config DMV サーバのすべてのログイン関連情報
server_triggers 2 Config DMV サーバレベルトリガーの全ての情報 (exログイントリガー)
sp_configure 1 Config DMV サーバーの設定情報
sysaltfiles 2 Config DMV データベースサイズ、自動増加の設定
sysdatabases 1 Config DMV データベース状態値(ログが切られなければ、なぜ切られないのか、page verify option、recovery modelなどの情報などが分かります。)
sysjobs 2 Config DMV すべての予約作業の情報を知ることができ、活性化や不活性化の情報
backupset 1 Log DMV データベースバックアップセット情報
sp_readerrorlog 1 Log DMV SQL Serverのエラーログ情報(Agentエラー情報も含め、Express Editionの場合、ItemOptionでSqlAgentLog=Fasleを設定する必要があります)
sysjobhistory 1 Log DMV 予約作業を行った結果
dbcc_memorystatus 3 Performance DMV サーバーの最も詳しいレベルのメモリー現況です。 サーバーのメモリ関連エラーがある時に詳しい情報が収集できます。
dbcc_sqlperf_logspace 1 Performance DMV ログファイル(LDF ファイル)の使用量がわかります。AG環境やミラーリング環境の場合、DBCC Loginfoのステータス情報では残りの空間が推定できません。 by design
dm_db_index_usage_stats 1 Performance DMV インデックス使用状態値(クラスター非クラスター、インデックスサイズ、複合コラム順、統計アップデート日付、Unique可否、Filteredインデックス可否、活性・非活性可否、indexsek scan回収などを保存します。 ただし、断片化情報は遂行時間の問題で収集していません。
dm_db_missing_index
_group_stats
2 Performance DMV 追加インデックスの推奨機能として提案されたインデックスと、インデックス作成時の性能向上度を示します。
dm_db_task_space_usage 2 Performance DMV TEMPDB空間追跡用に、どのクエリーによってどの程度の空間がTEMDBに割り当てられているかを示します。
dm_exec_query_stats2 1 Performance DMV クエリー別の1分単位の統計を収集します。 CPU、Reads、Execution Count、Elapsed Timeなどの統計を求めます。 また、関連するQuery Stringとクエリー実行計画を保管します。 1分統計テーブル:dm_exec_query_stats2_b_summary、実際のクエリーテーブル:dm_exec_query_stats2_b_statementクエリープランテーブル:dm_exec_query_stats2_b_query_plan、実際クエリー統計の基本データ(1時間):dm_exec_query_stats2_bdm_io_virtual_file_stats2PerformanceDMV
データファイルおよびログファイルに対するI/O統計を返還します。 一つの物理ディスクに複数のファイルが存在する場合、どのようなファイルで最も多くのI/Oが発生しているかが判断できる主要な数値です。
dm_os_memory_clerks 2 Performance DMV SQL Server インスタンスで、現在の活性状態のすべてのメモリクロックを返還します。 SQL Serverで使用するメモリ割り当てのほとんどは、当該 DMV に登録してから使用されるので、メモリ使用量を追跡したり、過度のメモリ使用量を示す個体を確認する際に主に使用されます。
dm_os_wait_stats 1 Performance DMV クエリーの実行が完了した待機時間を表示します。 累積値として記録され、過去の値と現在の値との差を一定周期でクエリーすると、どのような待機が多く発生するかが分かります。
dm_os_workers 1 Performance DMV 現在作動中のクエリーと状態値を返還します。 SQL Serverで現在実行されているユーザクエリーとシステムで発生する作動もすべて表示するようにデザインされており、現在作動中のサーバの状態を最も詳しく知ることができます。 (dm_exec_query_stats2には作動が完了した統計があり、dm_os_workersは、現在実行中のクエリーのリソース使用量情報があります。)
sp_lock2 1 Performance DMV SQL Serverロック情報をTreeの形式で出力するように作られており、原因クエリー分析が可能です。 (例:65>70>81というLockTree結果は、65番spidがロックの原因であり、70番と81番spidが遂行待機[ロックで遂行が不可能な状況]をしているという説明になります。)収集周期を運営環境に合わせて適切に調節する必要があります。 基本値は10秒になっていますが、必要に応じてその収集周期を短く設定するのもいいでしょう。
sp_spaceused 1 Performance DMV テーブルの行数および空間使用情報を出力します。
suspect_pages 1 Performance DMV 一貫性が崩れたページ情報を出力します。 823、824エラーをモニタリングすることができます。 823、824エラーは物理的なハードウェアのストレージの一貫性に問題がある可能性がありますので、ハードウェア点検が必要となることがあります。
Application 1 Log EVENTLOG イベントログのApplication情報を読み込んできます。フィルタの条件を追加し、ご希望の条件のデータのみ収集可能です。
Security 3 Log EVENTLOG イベントログのセキュリティ情報を読み込んできます。フィルタの条件を追加し、ご希望の条件のデータのみ収集可能です。
Setup 3 Log EVENTLOG イベントログのSetup情報を読み込んできます。フィルタの条件を追加し、ご希望の条件のデータのみ収集可能です。
System 3 Log EVENTLOG イベントログのSystem情報を読み込んできます。フィルタの条件を追加し、ご希望の条件のデータのみ収集可能です。
BaselineCollect 1 Performacne PERFMON Performance Counter値を読み込んできます。あらかじめ定義されたPerformance Counter値を読み込んできて、あらかじめ定義されたカウンターはインストールフォルダの下位のResourcesフォルダにUpdateCounterDetailsY.sql値をベースとしてモニタリングします。 モニタリングの基本値を変更するために、該当クエリーを変えたり個別にSqlLogManagerデータベースのCounterDetailsAutoUpdatedテーブルの値IsEnabledYNを調節します。 追加で必要な性能カウンター情報があれば、CounterDetailsFilterInfoテーブルのフィルター情報を修正します。

重要度はLazyLog開発者がSQL Serverのモニタリングをする際に主にチェックしなければならない内容を1としており、特殊な問題解決やその重要度が低いのは3と設定しました。
特に問題がなければ、すべての設定を活性化してモニタリングします。 保存スペースの問題があれば、アイテムの収集周期を変更するか、削除または限界時間を調節すればいいです。

データベーススキーマ

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

SqlLogManManager

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

テーブル名 コラム名 説明
CollectItem CollectId 自動増加値
ItemType DMV / EVENTLOG / PERFMON値を持つことができます。
ItemName dm_os_workers、dm_os_waitstats、dm_exec_query_stats2等の値が入っています。 アイテムがDMVの場合、SqlLogManDmvRepositoryにItemName_bとして保存されます。 dm_exec_query_stats2アイテムの場合は、テーブルが追加されます。 dm_exec_query_stats2_b、dm_exec_query_stats2_b_query_plan、dm_exec_query_stats2_b_statement、dm_exec_query_stats2_b_summaryテーブルに結果が保存されます。
すべてのItemNameは、CollectItemTemplateテーブルに基本値と共に保存されています。
ItemDesc PERF、CONFIG、LOGで割った分類値であり、プログラムで特別な作動をしません。
MachineId Machineテーブルに登録されたモニタリング対象サーバーのMachineId値
SqlInstanceId MachineSqlInstanceに保存されたSqlInstanceIdの値
DatabaseName Connection Stringの基本データベース値です。 いくつかのアイテムは、masterデータベースに収集されず、直接ユーザデータベースにアクセスする必要があります。 CollectitemTemplateにmasterとUserDatabaseに区分されています。 UserDatabaseに分類されたアイテムは、基本アイテムが先に収集された後に設定されなければなりません。
CollectIntervalSec 収集周期の秒
IsNtAuthYN Y/N値を持つことができ、NT認証かSQL認証かを区分する値です。 ItemTypeの中でPERFMONとEVENTLOGは必ずIsNtAuthYNがYに設定されなければなりません。 DMV 関連アイテムはY/N 値を持つことができます。 DMV関連アイテムはIsNtAuthYNがYの場合、モニタリング対象サーバーを探す際にMachineテーブルにあるMachineName、MachinePublicIp、NtUserName、NtPassword、NtDomain、MachineSqlInstanceテーブルのPortDefault情報を利用してWindows Imperson 対象サーバーがドメイン環境であれば、NtDomainにはドメイン(例: minsouk.com)を書き込み、ドメインがなければhostname(例:LocalMachineHostnameA)を書いていただきます。 Nの場合、SQL認証で作動し、MachineのMachinePublicIp、MachineSqlInstanceテーブルのSqlUsername、SqlPassword、PortDefault値を利用してアクセスします。 EVENTLOGとPERFMONは、MachinePublicIpではなくMachineNameを利用してアクセスするので、AD環境ではドメイン、ドメインがない場合にはhostnameをwindows/system32/drivers/etc/hostsに登録して、NtDomainに記入すれば正常的に収集できます。
IsEnabledYN Y / N 値を持つことができ、N の場合は、該当アイテムは収集しません。
PurgeIntervalSec 収集して保存されたデータの削除周期の秒は、収集周期と同様に設定することをお勧めします。
PurgeDurationHH 残しておくデータの限界時間です。 24を入力すると24時間が過ぎたデータは、IsPurgeEnabledYNがYの場合、自動削除されます。
IsPurgeEnabledYN Y / N 値を持ち、Y の場合、データの削除作動を実行します。
ItemOption db_exec_query_stats2_bにはtopCnt、SrcDelHH、IsNewDmv、isGatherQueryStatement、isGatherQueryPlanオプションを持つことができます。 topCntは、dm_exec_query_statsをlast_execution_time desc基準に並べ替え、topで読み込むとき、最大値を記録します。 初期値は20000です。 SrcDelHH dm_exec_query_stats2_bのデータ維持時間です。 デフォルト値は1であり、1時間です。 特殊な場合でなければ、1時間以上維持する必要がありません。 集計値は別途にdm_exec_query_stats2_b_summaryに保存されているためです。 isNewDmvはSQL Server 2012 SP3からtotal_dop~max_used_threads値を収集することができます。 SP バージョンごとに異なり、デフォルト値は false に設定されています。 もしこの値が出るdm_exec_query_statsを収集するとしたら、trueを入力すればよいでしょう。 isGatherQueryStatementはクエリ文収集の有無であり、デフォルト値はtrueです。 isGatherQueryPlanはプラン収集の有無であり、デフォルト値はtrueです。 ad-hocクエリで作動して数多くのプランができた場合、falseに設定します。 trueに設定されていても最小限の負荷でプランを収集してきます。クエリ文とプランは1分に最大負荷を起こすクエリ100個を収集します。
EVENTLOG Securityの場合、filter条件を明示することができます。 デフォルト値はfilter|and\*[System[EventID!='4674']]and\*[System[EventID!='4624']]and\*[System[EventID!='4634']]and\*[System[EventID!='4672']]and\*[System[EventID!='5447']]and\*[System[EventID!='4673']]and\*[System[EventID!='4776']]and\*[System[EventID!='4670']]です。
PERFMON BaselineCollectの場合、counterReloadオプションを持つことができ、デフォルト値はYです。 サーバーの全性能カウンター収集が完了すると、Nに自動で変更されます。
CollectItemTemplate デフォルト値を保存しているテーブルです。 IsEnabledYN がすべてN に設定されています。 isNtAuthYNはEVENTLOG、PERFMONはY DMV関連の項目はNに登録されています。 usp_register_CollectItem保存プロシージャを利用してアイテム登録時にこの値を参照して実行します。
Config Type PERFMON / EVENTLOG / DMV 値を持つことができます。
Name アイテム名
CollectPerServerItemSleepMiliSec : 性能カウンターアイテム収集時の各アイテム間の休止時間(Thread)。Sleep値)です。 同一IDC内遠隔サーバーの場合150を勧告します。例えば性能カウンター収集間隔が10秒であり、収集性能カウンターアイテムが10個であれば、CollectPerServerItemSleepMiliSecは、最大1000 msまで与えることができるはずです。 この値はサーバーのアイテム収集項目と収集周期によります。 収集間隔の間に少なくとも1000 msまで設定は可能ですが、900ms程度まで設定することをお勧めしており、最小値は25ms以上確保しなければなりません。 ローカルサーバーの場合、より短い時間で設定可能です。
CollectIntervalSec : 性能カウンターの収集周期です。デフォルト値は15秒です。 遠隔サーバーの場合15秒以上の値を設定しなければなりません。 サーバーごとに異なる値を与えることはできません。 対象サーバーの性能カウンターを追加する場合、この値は変更されなければなりません。
CounterDetailsThreads : 最初の性能カウンタを収集する場合、各カウンタ情報を読み込まなければならず、この値はCounterDetailsFilterInfoテーブル設定により性能カウンタカテゴリをフィルタします。 一度に多すぎるサーバーの性能カウンタを読み込めば、ネットワークのボトルネック現象を誘発することがありますので、適切に設定すればよいでしょう。 1に設定すると一度にひとつのサーバで性能カウンタ情報を収集します。 この値は、収集プログラム内のSemaphoreSlimクラスの同時実行の数字を制御します。 収集対象となるサーバーを全て同時に収集したい場合は、収集対象となるサーバーの数だけ書いておけばいいです。 デフォルト値1を適用して順次的に収集できるようにすることを推奨します。
PerfmonReset : LazyLog.exe プログラムの実行中の項目の Value 値が Y にアップデートされると、数秒後、性能カウンターのみ収集を再開します。 全体収集プログラムを再起動しないためのオプションであり、LazyLog.exeプログラムを終了し、再起動しても同じ作動を行います。
DmvReset : LazyLog.exeプログラムの実行中の項目のValue値がYにアップデートされると、数秒後、DMVのみ収集を再開します。 全体 LazyLog プログラムを再起動しないためのオプションであり、LazyLog.exe プログラムを終了し、再起動しても同じ作動を行います。
EventLogReset LazyLog.exe : プログラム実行中の項目のValue値がYにアップデートされると、数秒後、イベントログのみ収集を再開します。 全体 LazyLog プログラムを再起動しないためのオプションであり、LazyLog.exe プログラムを終了し、再起動しても同じ作動を行います。
Value いずれも文字値やプログラムで適切に変換して使用されます。 (上記Nameコラムの説明参照)
CounterDetailsAutoUpdated 性能カウンタ収集のために、CounterDetailsFilterInfoテーブル値を基準にサーバーで読み込みます。 CounterDetailAutoUpdatedはサーバーで読み込んだ性能カウンター情報の最初の保存位置であり、デフォルトでIsEnabledYNがYに設定されたカウンターは、lazylog.exe下位ResourcesフォルダにUpdateCounterDetailsY.sql値によって設定されます。 性能カウンタを追加したいなら、SqlLogManManagerデータベースのCounterDetailsAutoUpdatedテーブルで、該当項目のIsEnabledYN値をYに修正し、CollectItemで該当サーバの性能カウンタ項目ItemOptionにcounterReload=Yを設定します。(Yに設定された性能カウンタをNに変える手順も同様です。)
CounterDetailsFilterInfo idx identity値
FilterType IN / NOTIN値を設定することができ、ObjectName基準でINは含み、NOTINは収集しません。
ObjectName 性能カウンターObjectNameです。
Machine MachineId identity値
IdcName 使用されません
MachineName EventLog収集時にEventLogSessionクラスで使用されます。 PERFMON収集の際、PerformanceCounterクラスで使用されます。 ActiveDirectory環境じゃなければ、windows/system32/etc/hostsにhostnameとIPが登録されていなければなりません。
MachinePublicIp DMV収集時に対象サーバーにアクセスIP
MachinePrivateIp 使用されません
MachineClusterIp 使用されません
MachineAgIp 使用されません
NtUserName Windows Impersonation認証で使用するアカウント名
NtPassword Windows Impersonation認証で使用する暗号lazylog.exe 「暗号」を実行して得られた、暗号化された値を入れなければなりません。 暗号化された暗号は、プログラム内部で正常暗号値でDeCryptして使用されます。
NtDomain Windows Impersonation認証で使用するドメイン名です。 Active Directory環境でない場合は、必ずhostnameを書きます。
NtSessionAuthenticationType KERBEROS、NEGOTIATE、NTLM、DEFAULTをサポートし、EventLogSessionクラスで使用されます。 デフォルト値はDEFAULTです。
IsEnabledYN Y / Nを設定することができ、N の場合は、該当サーバーのすべてのアイテムを収集しません。(lazylog.exe 再起動またはConfig 値の調節で該当収集を再開しなければなりません。)
MachineSqlDatabase MachineId MachineテーブルのMachineId値
SqlInstanceId MacineSqlInstanceテーブルのSqlInstanceId値
DatabaseName SqlLogManDmvRepositoryデータベースのsysdatabases_bに収集されたデータベース名
DatabaseDesc 使用されません
IsEnabledYN usp_register_CollectItem保存プロシージャを利用して作成するとき、スクリプティングするかどうかを決定します。 Nはスクリプトされません。
CollectItem CollectId 使用されません

SqlLogManDmvRepository

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

テーブル名 コラム名 説明
SqlLogManManagerデータベースのCollectItemテーブルにあるItemNameに\_b (ベースラインの意味)を持つすべてのテーブル idx identity値
MachineName 収集対象となるサーバーのホスト名
FullinstanceName 収集対象となるSQLサーバーのインスタンス名
probe_time 収集リクエスト時間
etc 各itemnameコマンドをベースに類推
dm_exec_query_stats2_b   dm_exec_query_stats2アイテムのデータ
dm_exec_query_stats2_b_summary   dm_exec_query_stats2_bのdelta値(現在の設定基準で1分基準delta値)このテーブルはdm_exec_query_stats_bが3回以上収集されて初めて過去の価格をベースに作成されます。
dm_exec_query_stats2_b_statement   dm_exec_query_stats2_b_summaryにあるquery_hashに該当するクエリー文章
dm_exec_query_stats2_b_query_plan   dm_exec_query_stats2_b_summaryにあるplan_handleに該当するxml plan

SqlLogManPerfmonRepository

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

テーブル名 コラム名 説明
CounterData        性能カウンター基本データベーススキーマに従う (収集された性能値)
CounterDetails 性能カウンター基本データベーススキーマに従う(収集された性能カウンターの種類)
DisplayToID 性能カウンターの基本データベーススキーマに従う (収集性能カウンターSet定義であり、lazylog.exe.configのPerfmonItemName値のBaselineCollectは、DisplayStringで使用されます。
EventLogApplication ウィンドウのイベントログのApplication情報(最初收集時の30分前のログから取得します)
EventLogSecurity ウィンドウのイベントログのSecurity(最初收集時の1分前のログから取得します)
EventLogSetup ウィンドウのイベントログのSetup(最初收集時の1日以前のログから取得します)
EventLogSystem ウィンドウのイベントログのSystem(最初收集時の30分前のログから取得します)

設置

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ファイルの圧縮を解凍すると、以下のようなファイルがあります。

項目 説明
Resources UpdateCounterDetailsY.sqlファイルあり
SQLScript ウィンドウコマンドの母音とインストールStepで使用するSQLスクリプトファイルがある。
LazyLog.exe ログ収集プログラム (コンソールアプリケーション)
LazyLog.exe.config ログ収集設定ファイル (XML設定ファイル)
LazyLogService.zip ログ収集プログラム(Serviceインストール用アプリケーションインストールファイル)

探索機の表示オプションでファイル拡張名にチェックをしないと、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>
項目 説明
FileLogYN LazyLogアプリケーションのエラーログを残すかどうか、Yの場合はインストールフォルダにLogというフォルダができて、日付別、アイテム別のログが作成されます。 収集が正常化されればNに変えてプログラムを再起動してログファイルがサーバーに溜まらないようにします。
UsePerfmonYN LazyLogアプリケーションが性能カウンターを収集するかどうかを決定します。 Yの場合は性能カウンターが収集され、Nの場合はSqlLogManager.dbo.CollectItemにYに設定されても、どのアイテムも収集されません。
UseEventmonYN LazyLog アプリケーションがイベントログを収集するかどうかを決定します。 Yの場合はイベントログが収集され、Nの場合はSqlLogManager.dbo.CollectItemにYに設定されても、どのアイテムも収集されません。
UseSqlmonYN LazyLog アプリケーションが DMVを収集するかどうかを決定します。 Yの場合はDMVが収集され、Nの場合はSqlLogManager.dbo.CollectItemにYに設定されても、どのアイテムも収集されません。
UseLocalKey 必ずYでなければなりません。
PerfmonItemName 性能カウンターを保存するデータベース(SqlLogManPerfmonRepository) DisplayToIDテーブルのDisplayString名です。 該当名前で性能カウンター保存先が構成し、ウィンドウ性能カウンターで再び絵を描いてみる時、照会する名前として使用されます。 デフォルト値はBaselineCollectが使用されます。
SqlLogManManagerInstanceName 収集設定保存先インスタンス名。(プログラムでは使用されません。)
SqlLogManManagerIP 収集設定保存先 IP
SqlLogManManagerPort 収集設定保存先 SQL Service Port
SqlLogManManagerDatabase 収集設定データベース名
SqlLogManManagerIsNtAuthYN ウィンドウ認証の場合、Y、SQL認証の場合、Nを入力します。(Nを勧告します。)
SqlLogManManagerUserName 収集設定データベースのアカウント名
SqlLogManManagerPassword 収集設定のデータベースの暗号(実際のデーターベースにアクセスする暗号がP@ssw0rd3なら、LazyLog.exeを利用して作成した暗号値qcWa7DCqnN7vqMZ6b9ilzw==を入力しなければなりません。 暗号化に関連するエラーの多くは、アカウントごとの暗号値を書き間違える場合か、暗号が長すぎる場合です。)
  • 残りの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サーバーが基本のインスタンスなら、MHV1だけ入力して、命名されたインスタンスに設置されたなら、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コマンドで次のように実行します。
  4. runas /user:lazyNtUser cmd.exe

    • cmdウィンドウが正常にlazyNtUserで始まれば暗号は正確です。
  5. 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秒を推奨)

関連情報リンク

下記のガイドで関連情報を確認することができます。

に対する検索結果は~件です。 ""

    に対する検索結果がありません。 ""

    処理中...