Using MSSQL log collector (Lazylog )

Available in Classic and VPC

Introduction

MSSQL Log collector (LazyLog) overview and required content

Log collector overview [Must Read]

When running SQL Server, it is very important to collect the settings of the monitored server, the query performance of the program, the event log of the server, and the baselines of the performance counters.
Without this baseline data, it can take a long time to determine whether the current service is normal (or different from yesterday). Therefore, Naver Cloud deploys the SQL Server log collector program. The collected data is required data for failure analysis or performance analysis (Windows performance counter, Windows event log, SQL error log Agent log, query syntax, query execution plan, Pending, lock, etc. required DMV snapshot).

Log Collection Console application (LazyLog) supports SQL Server 2008, 2012, 2014, 2016, and as far as server resources allow, it supports unlimited destination servers, multiple SQL instances, and multiple databases. You can run multiple log collector programs on one server at the same time. You can separate the folders and run them separately. Grouping similar server groups together makes it easier to determine whether they are normal by comparing server settings or resource usage. Therefore, it is recommended that you isolate and configure the objects appropriately. If you monitor multiple monitored SQL servers in a single group, you can also use it as a Windows service program. (After Setup is complete, run Setup.exe and edit the settings file to start the service).

LazyLog accesses target server based on user defined value (SqlLogManManager) to collect specific data, and automatically deletes data that exceeds the storage period.
If you set the collection period too short, it can save a lot of logs and make the database for data storage very large, or the collection program may malfunction. Therefore, you should adjust the collection period and set the data storage period appropriately. It is also recommended that at least 3GB of free memory is left for the program to run. (This extra free memory is the memory that you need to reserve in addition to the memory you need to leave in the OS when you run SQL Server.) The log collection program temporarily stores data received from multiple monitored servers in memory and Bulk Insert them into the SQL Server repository database.

If the managed SQL server is very important, you will not want the application to be installed on that SQL server. LazyLog is developed to be able to collect all data remotely and can also monitor a local SQL server. The program is collecting and analyzing logs for dozens of SQL servers performing several TB capacity and tens of thousands of Batch Requests per second. Windows impersonation and SQL authentication are supported for remote logon and data collection. It requires proper permissions and firewall opening to remotely read performance counters and event logs.

Windows authentication (Active Directory or WorkGroup) must be used to collect Windows performance counters and event logs, and Windows authentication and SQL authentication are supported for SQL Server status monitoring. (SQL authentication is recommended, and SQL authentication is set as default.) You must create and use an encrypted value through the LazyLog.exe program for account password setting (for secure password storage). Settings are stored in two places. The first XML file named LazyLog.exe.config stores global setting information, information setting database access information, and database setting information for storing collected information. In the second database called SqlLogManManger, the target server access information must be entered.

You can complete the setup quickly if you install after testing your account and permissions first for the following on the server where the log collector application (LazyLog.exe) or service application is to be installed: (1) whether the database containing the monitored server information (SqlLogManManger) and the database for storing collected data (SqlLogManDmvRepository, SqlLogManPerfmonRepository) server are accessible;
(2) whether performance counter monitoring is possible for the monitored server;
(3) whether monitored SQL Server is accessible.

If the collection repository database is slow, the log collector program memory may be used excessively, so we recommend that you run the log collector program separately from the database server. If you are monitoring multiple servers, SSD is recommended for repository storage. Monitor a small number of servers and slowly extend the target. Increase the monitoring target server by determining the resource usage of the log collector program in the collection operating server environment. Log collection program error information is created for each daily item in the Log folder. If there is a problem with the collection, refer to it.

Configuration guide

MSSQL Log collector (LazyLog) installation structure

How to configure the log collector

Since the log collector can be remotely collected, it is recommended to install it separately from the collection target server, but it can be installed directly on the collection target server. Here are some examples of installable configurations. This installation guide is based on a local installation with no restrictions on firewalls and account settings (Configuration sample 1). Try local configuration first, learn how to configure and use the LazyLog applications, and then try the remote collection.

Configuration sample 1

Configuration sample 1 is the simplest configuration and is the configuration to learn how to use LazyLog.

This configuration does not require a firewall setting. You can install the log collector directly on the monitored SQL Server and create the monitoring database on the same SQL Server. In this case, the speed of performance counter or event log collection is high because it is not through the network. However, if the log collection program runs abnormally (usually due to problems when the Agent program is running on the operating server) or the capacity of the SQL monitoring database is increased, it may affect the operating server and is not recommended.

If you are monitoring only one server, only DMV and performance counter information is stored in the database, and event logs do not need to be stored in the database. (That is because it may be better to query the event log directly.) However, when you want to send an alarm for a specific event, you can manage it more conveniently by storing the event log in SqlLogManPerfmonRepository (which is the default repository name and can be changed).

Configuration sample 2

Configuration sample 2 is the recommended model for most monitoring servers.

You do not install any programs on your server; you need to configure Monitoring Server separately to install SQL Server on that server and create ManagerDB(SqlLogManManager), DMV Repository(SqlLogManDmvRepository), Perfmon EventLog(SqlLogManPerfmonRepository). The log collector program collects information remotely against the monitored server and stores the data in the SQL monitoring database.

Configuring the monitoring server separately and monitoring multiple servers can have a network bandwidth burden on the performance counter collection. Modify and use the Perfmon metrics in the Config table of the Manager database appropriately. (Default values for deployed programs are set to appropriate values for monitoring remote servers in the same IDC.)

To verify whether a monitoring server has a problem, or if a problem with the monitored server occurs due to monitoring, block the account information set on the monitoring server or stop the collection program from the monitored collection server.
The advantage is that you can easily verify if monitoring is causing problems for the database.

This monitoring model has a problem that if the monitored server has few resources (target server CPU 100%) so that the monitoring query does not work, it may not collect data. This is a unavoidable problem in the remote monitoring model. Even if Configuration sample 1 is applied, monitoring data may not be saved due to a complete lack of resources.

Some ISP providers cannot open port 445, so it is recommended that Monitoring Server be installed on the same IDC as the monitored server. This model may cost heavy licensing fees because independent SQL Server is used for monitoring only. An appropriate CAL license model may be helpful.

Configuration sample 3

If you have SQL Server licensing issues, you can install and run the monitoring program and monitoring database together on a server with low server usage.

Configuration sample 4

If the resource usage of the log collector program is burdensome, you can configure only the monitoring program using another server. In addition, you can mix the configurations of the Configuration sample 1, 2, 3, and 4 as appropriate.
However, if the SQL monitoring database is configured as a different instance or server as in Configuration sample 4, then two preconfigured synonyms must be modified accordingly to ensure normal monitoring.
(Synonyms include SN_CounterDetailsd located in the SqlLogManManager database and CounterDetailsAutoUpdated located in the SqlLogManPerformanceRepository database.)

Synonyms:
https://msdn.microsoft.com/ko-kr/library/ms187552.aspx

Collection data

Data that can be collected from MSSQL Log collector (LazyLog)

Performance counter

Windows performance counters can be collected, and it reads which counter is on the server based on the SqlLogManManager.dbo.CounterDetailsFilterinfo table information. The read counter is stored in SqlLogManManager.dbo.CounterDetailsAutoUpdated.
The FilterType [IN] in SqlLogManManager.dbo.CounterDetailsFilterinfo refers to the object to be collected, and FilterType [NOTIN] is the object to be excluded.
This value can be modified by the user after installation.

SqlLogManManager.dbo.CounterDetailsFilterInfo

SqlLogManManager.dbo.CounterDetailsAutoUpdated

The configuration of this table is the same as the Windows performance counter schema, and all the counter information corresponding to the above filter information is stored. Only the IsEnabledYN field has been added, but only the Y field is stored in the SqlLogManPerfmonRepository database every 15 seconds. (Storage interval limit: local minimum 5 seconds, remote minimum 15 seconds)
The tables that are actually stored are CounterData, CounterDetails, and DisplayToID of the SqlLogManPerfmonRepository. If you want to modify the default collection Y metrics, you can update it by modifying UpdateCounterDetailsY.sql under Resources in the installation folder. It is designed to input Free, Total, and Used Megabytes for each disk volume that is not in the original counter metrics. We hope you find it useful.

For the schema of these three tables, please refer to https://msdn.microsoft.com/en-us/library/aa371915(VS.85).aspx.

Free, Total, Used Megabytes for each disk volume

If the collection is successful, you can view the data as follows.

SQLCMD command [server]

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

Query result of normal running data from SSMS

After executing odbcad32 on Windows Run (ODBC Data Source Administrator (64 bit)) and performing configurations, connect to Windows built-in performance counter program to view the saved history. (Windows accounts only, so the Windows account must have login and data_reader permissions to the monitoring collection server.)

ODBC configuration

View data collected from LazyLog via Perfmon application

Event Log

It is possible to read the server's Application, Security, Setup, and System event logs. It is also possible to filter and store specific information. Each server's filter for this event information is defined in ItemOption in the SqlLogManManager.dbo.CollectItem table.

This information was entered through the SqlLogManManager.dbo.usp_register_CollectItem procedure, based on the SqlLogManManager.dbo.CollectItemTemplate table, and can be individually or fully modified.

SQLCMD command

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

Query result when the application log is saved via LazyLog

Stored event logs are stored as they are read directly from the Windows default eventvwr applications. We have added the IsCheckedYN field for sending SMS, so it is useful for sending messages about specific events. In the example, EventLogSecurity, EventLogSetup, and EventLogSystem are metrics that can be added directly to the default monitoring.

DMV (Dynamic Management View)

It collects and stores the performance status of Always On Availability, current status, resource usage per query, query, query plan, lock, pending analysis, I/O amount per file, index structure, statistics date, server memory status, and agent execution history.
LazyLog collects most of the information that will help you to operate and troubleshoot SQL Server. For detailed monitoring metrics, refer to the table below.

To prioritize, what LazyLog developers should mainly focus on when monitoring SQL Server is set to 1. A special troubleshooting, or cases with low priority are set to 3.
If you do not have a particular problem, enable all settings to monitor. If you have storage issues, change or delete the collection period of items to adjust the time limit.

Database schema

MSSQL Log collector (LazyLog) database structure

SqlLogManManager

A table for storing the collected server information and all the settings related to the collection setting is contained in this database. (If you only back up this database and the lazylog.exe.config file, you can recover the collection server at any time.)

SqlLogManDmvRepository

This is the database where the DMV collection result data is stored.

SqlLogManPerfmonRepository

This is the database that stores performance counters and event logs.

Install

MSSQL Log collector (LazyLog) installation guide

Install program

Overview

1. If you save your password as plain text, you will have to re-encrypt your password because there is a hacking or security problem. [Required]
2. In order to read performance counters and event logs from the monitored server, Windows account settings, permission settings, required service startup, and firewall settings are required on the target server.
3. You must first check if the monitor server is able to query performance counters, event logs, and DMVs for monitored servers.
4. Create three repository databases.
5. The lazylog.exe.config file records the database in which the metrics global settings and collector settings are stored and the database information where the collection results are stored.
6. Save the connection information of the collected server to the SqlLogManManager database.
7. Register collection items for each server in the SqlLogManManager database. (Whether to collect performance counters, collect each metrics of event log, collect each metrics of DMV, etc.)

• Due to the complexity of the setup, we provide the procedure to create the registration script. You can create the script and run it in SQLCMD or SSMS.

8. There are basic collections and extended collections. For extended collections

• SqlLogManDmvRepository.dbo.sysdatabases_b collection must be completed. (Need to collect sysdatabases among the default items)

9. The SqlLogManDmvRepository database stores SQL Server related monitoring metrics.
10. The SqlLogManPerfmonRepository database stores performance counters and event log collection results.

Install Console mode

If you unzip the LazyLog.zip, you will see the following files.

If you do not check the file extension in the Explorer view options, LazyLog.exe looks like LazyLog and LazyLog.exe.config looks like LazyLog.exe. Be careful not to confuse LazyLog.exe.config with LazyLog.exe.

Create password

The password used by LazyLog must use the encrypted string created by LazyLog. Encrypted string is created by entering the LazyLog "password string" in cmd. Below is an example that created three passwords. It is also a good idea to set the same password for all test settings. However, passwords for each server may be different in an actual monitoring environment.
You can set up even easier if you also practice creating passwords like the guides for tests.

Set monitored server

1. Set server
   lazyNtUser is the Windows impersonation authentication account to be set in the SqlLogManManager database. Below is the command to execute on the monitored server, and it must be run in administrator mode. If the monitoring repository server and the monitored server are the same, the following red command is not required.

• Windows CMD commands [client]

sc config remoteregistry start=auto
net start remoteregistry
netsh advfirewall firewall set rule name="Netlogon service(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. Set SQL Server
   An account called lazyClient is the account information that will be set in the SqlLogManManager database. As an SQL Server administrator, run the following command in SQLCMD mode: Open the L03_Account_lazyClient.sql file in the SQLScript subfolder.

• SQLCMD command [Client]

-- Edit variable
:setvar AccountName lazyClient
:setvar AccountPassword P@ssw0rd1
-- End edit variable

• The lazyClient SQL authentication account is created with the password P@ssw0rd1.

Operate all SQL query statements of this guide in SQLCMD mode. To operate in SQLCMD mode in SSMS, refer to the description below.

How to convert SQLCMD mode	Normal mode	SQLCMD mode (The variable part is inverted)

Create monitoring repository database

1. Create a folder to create the repository database.

• Windows CMD command

mkdir c:\lazylog

You should create the folder. (The database name can be freely changed, and the database will be created in the folder later.) Keep it as it is for the first exercise.

2. Open the L01_InitScript.sql file in the SQLScript subfolder.

• SQLCMD command

-- Edit variable
: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
-- End edit variable

This script creates three databases: SqlLogManManager, SqlLogManDmvRepository, and SqlLogManPerfmonRepository. Check the variable modification section and execute in SQLCMD mode. Three databases are created as shown below.

Create an account for the monitoring repository

Create an account to access the database created in the step above.

1. Open the L02_Account_lazyServer.sql file in the SQLScript subfolder.

• SQLCMD command

-- Edit variable
:setvar AccountName lazyServer
:setvar AccountPassword P@ssw0rd3
-- End edit variable

2. Modify the variable and execute it in SQLCMD mode.
   Be sure to test whether you can log in to the monitoring repository server as SSMS with ip, port, and account from where lazylog application is running. (If you log in as SSMS, access via the original password P@ssw0rd3) If you set it to GUI, you may need to change your password before you can log in depending on the option. For the lazyServer password, P@ssw0rd3, enter the value encrypted as qcWa7DCqnN7vqMZ6b9ilzw== when you put it in Lazylog.exe.config.

3. On the server running LazyLog.exe file with the created account and IP password, test the IP and Port access to the repository database server.

Set LazyLog.exe.config

This file is the step where the LazyLog application enters information to access the database created in the above step.

You can edit it with notepad. Edit the IP, Port, account name, and encrypted account password appropriately. You should set after testing whether it can access SSMS if you access via 127.0.0.1,1433.

<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>

• The remaining SqlLogManDmvRepositoryXXXX and SqlLogManPerfmonRepositoryXXXX are the database to store the DMV results, and the database information to store performance counters and event logs.

Add Hosts file

If the monitoring server cannot access the monitored server by name, add it to the windows/ system32/drivers/etc/hosts file and make it accessible by name. Adding a hostname in a workgroup environment is required for performance counter and event log collection.

Enter target server information on the monitor server

This is the process of registering the monitored server. Open L04_TargerServerRegister.sql in the SQLScript sub folder and modify the variable below and execute it in SQLCMD mode.

• SQLCMD command

-- Edit variable

:setvar ManagerDatabaseName SqlLogManManager
-- The database name to store the settings
:setvar IdcName NCloud
-- Server location (No impact on program)
:setvar MachineName AD01047147
-- Monitored server host name (If it is remote, register it in hosts to make it accessible.)
:setvar MachinePublicIp 127.0.0.1
-- Monitored IP
:setvar lazyNtUserName lazyNtUser
-- Windows authentication created on the target server to read the performance monitor and event log
:setvar lazyNtUserPasswordEncrypted XckO0Pvn7Lq0GjFVAjehhg==
-- Password for above account (It must be encrypted with lazylog.exe. Create like lazylog.exe "P@ssw0rd2")
:setvar NtDomainNameIfNotAdEnvHostname AD01047147
-- In the AD environment, enter only the domain name without the hostname hostname.adServer.com -> adServer.com, if workgroup, enter the hostname
:setvar FullInstanceName AD01047147
-- Monitored SQL Instance Name (Name 0 of sysservers on that server)
:setvar SqlUsername lazyClient
-- SQL admin account set on monitored SQL instance
:setvar SqlPassword BaQJi8Br/qBUv0lq/F1Z1Q==
-- Password for above account (It must be encrypted with lazylog.exe. Create like lazylog.exe "P@ssw0rd1")
:setvar RoleName test server
-- Server notes (No impact on program)
:setvar SqlServerMajorVersion 2014
-- One of the values of the major versions of SQL Server 2008, 2012, 2014, 2016
:setvar SqlPort 1433
-- Monitored server port

-- End edit variable

Register default monitoring

Open L05_TargetItemRegisterBasic_01.sql file in the SQLScript subfolder, and edit the variable below and execute it in SQLCMD mode.

-- Edit variable

:setvar machineName AD01047147
-- MachineName is the hostname
:setvar FullInstancename AD01047147
-- FullInstanceName is the result of the “select srvname from sysservers where srvid = 0” on that server
:setvar SqlLogManManagerDatabase SqlLogManManager
-- Name of the manager database where all lazylog settings are stored (monitoring repository database)

-- End edit variable

Execute LazyLog.exe

Double click on LazyLog.exe in Windows Explorer. Below is a sample of the Console mode LazyLog.exe running normally.

View normal status of default monitoring

1. Performance counter
   To check if LazyLog is collecting normally, check the table below about two minutes after it is activated.

• SQLCMD command

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

If it is output as below, it’s collecting normally.

2. Event log
   To check if LazyLog is collecting normally, check the table below about two minutes after it is activated.

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

If it is output as below, it’s collecting normally.

View DMV

To check that basic monitoring is working properly, check the content of SqlLogManDmvRepository.dbo.sysdatabases_b table. This information must be collected in order to register extended monitoring.

• SQLCMD command

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

If you see the user database list as shown below, the normal collection is completed.

Register extended monitoring

1. View and copy user database information
   Extended monitoring can be collected only when the sysdatabase that was checked above among the basic monitoring metrics is normally collected. Open the L06_TargetItemRegisterAdvanced_01.sql file in SQLScript subfolder, and edit the below variable and execute as SQLCMD mode to enter user database information in SqlLogManManager.dbo.MachineSqlDatabase.

• SQLCMD command

-- Edit variable
:setvar machineName AD01047147
-- Monitored hostname
:setvar SqlLogManManagerDatabase SqlLogManManager
-- Name of the manager database where all lazylog settings are stored (monitoring repository database)
:setvar SqlLogManDmvRepositoryDatabase SqlLogManDmvRepository
-- Database name where DMV collection information will be stored
-- In the basic item collection, normal registration is possible when the sysdatabases is collected.
-- End edit variable

If it is performed normally, the following output will appear.

2. Create extended monitoring registration script
   Open the L06_TargetItemRegisterAdvanced_02.sql file in the SQLScript subfolder, edit the variables below, and run it in SQLCMD mode.

• SQLCMD command

-- Edit variable
:setvar machineName AD01047147
-- Monitored hostname
:setvar SqlLogManManagerDatabase SqlLogManManager
-- Name of the manager database where all lazylog settings are stored (monitoring repository database)
-- End edit variable

When it is performed normally, the script to be executed is output.

Among the created scripts, execute a set of two scripts called "script to execute" in SSMS.

3. Enable extended monitoring scripts
   Open the L06_TargetItemRegisterAdvanced_04.sql file in the SQLScript subfolder, edit the variables below, and run it in SQLCMD mode.

• SQLCMD command

-- Edit variable
:setvar machineName AD01047147
-- Monitored hostname
:setvar FullInstanceName AD01047147
--FullInstanceName is the result of the “select srvname from sysservers where srvid = 0” on that server.
:setvar SqlLogManManagerDatabase SqlLogManManager
-- Name of the manager database where all lazylog settings are stored (monitoring repository database)
-- End edit variable

Restart LazyLog.exe

Restart Console LazyLog.exe to collect extended monitoring metrics.
(Shutdown and start Console application)

View normal status of extended monitoring

In case you checked that basic monitoring is normal and you want to check if extended monitoring is normal, all the collection is normal if sp_spaceused_b is collected normally.

• SQLCMD command

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

All the collection is normal if the table size per db_name is normally output as below.

Install using Windows service

When all the settings are completed in Console mode, unzip the LazyLogService.zip file and simply run Setup.exe and click Next to complete the installation. (If you run LazyLog as a Windows service, you can run only one service, so if you need to run multiple LazyLogs, use it in Console mode.)

C:\Program Files\Naver Business Platform\SQL Server Log Collector
Copy the appSettings configuration of the Console application that was previously set only and paste it to the appSettings section of LazyLog.exe.config file in the folderEnter net start "SQL Server Log Collector" in the cmd prompt to start the service, or start the SQL Server Log Collector in services.msc.

If run as a service, the collection will automatically start even if the monitoring server restarts.

If all the collections are normal, we recommend setting the [FileLogYN] setting to [N].

Troubleshooting installation issues

MSSQL Log collector (LazyLog) installation troubleshooting guide

Troubleshooting

In troubleshooting, let’s assume that you have set up the same account as the example. Please refer to the log file to solve other problems. Most of the errors were due to incorrect account setting or the TCP port 445 being blocked by the ISP provider, in which performance counter and event log data could not be collected. In this case, you can change the location of the collector program so that it does not use blocked ports.

Machine table

1. WorkGroup server

• If you have a monitoring server with a hostname of AD01047147

• Register the [host] as [alias] in [windows/system32/drivers/etc/].

• Test if you can access 1433 monitoring SQL Server via Ping and Telnet.

• For MachineName, enter "AD01047147," and for NtDomain, enter "AD01047147" as well.

• For NtUserName, enter "lazyNtUser."

• For NtPassword, enter the encrypted password. (Encrypt your password using lazylog.exe.)
  

• Check if IsEnabledYN is Y.

2. Active Directory server

• If the monitored host has [mhv1.minsouk.com] server
• For NtDomain, enter "minsouk.com" only (excluding a hostname).
• For MachineName, enter "mhv1[host name only]".(No other content, such as Slash @, should be included.)
• For NtUserName, enter "lazyNtUser" or monitoring ID (excluding a domain name).
• For NtPassword, enter the password of the above account encrypted with lazylog.exe.
• Check if IsEnabledYN is Y.

MachineSqlInstance table

If the SQL Server is the default instance, enter only "MHV1"; if it is installed as a named instance, enter "FullInstanceName." (Enter as shown in MHV1\SQL2016.)
For DMV metrics, if the IsNtAuthYN value in CollectItem is Y, it is accessed by using NtUserName and NtPassword values recorded in the machine. If it is N, it is accessed by using MachinePublicIp and PortDefault.
Check if IsEnabledYN is Y. Please refer to the figure below to edit.

In the above configuration, MachineId 1 is the WorkGroup server and MachineId 6 is the ActiveDirectory server setting. LazyLog impersonates based on the above information, and reads Windows performance counters, event logs, and DMV.

Internal ConnectionString creation logic

In a workgroup environment, you should check if port 1433 is working properly via Ping or telnet with hostname to the target server for DMV collection (If it is a customized port, check the corresponding port). To collect performance counters and event logs, Ping or port 445 should be verified to check if they are working properly.

Test access by account

1. lazyServer
   It is set in lazylog.exe.config and the account and permissions must be set in SQL Server to store the collected data.

• The repository database must be accessible by the SQL Server account. (Set in lazyLog.config.XML) When testing with SSMS or SQLCMD, check if you can use the password before the lazyServer account is encrypted to log in from the computer where the lazyLog.exe program is running to the SQL Server where the SqlLogManManager database is located. (accurately test IP and Port)
• Does lazyServer have sysadmin or dbo permissions?
  

2. lazyClient
   It is set in MachineSqlInstance, and the account and permissions must be set on the collected SQL Server.

• Is the SQL Server account accessible to the monitored server by SSMS or SQLCMD program? (The lazyClient account accesses the target server with the Machine and MachineSqlInstance information in the SqlLogManManager database. When testing in SSMS, you must enter it as a value before encryption.)
• Does lazyClient have sysadmin permissions?
  

3. lazyNtUser
   Set in Machine table, and the account and permissions must be set on the target computer.

• Used to read performance counters and event logs. (SQL authentication is not allowed.)

• To check if the password is correct, log in as Administrator to the collected server and execute as follows with the Runas command.

• runas /user:lazyNtUser cmd.exe
  

• If the cmd window normally starts with lazyNtUser, the password is correct.

• Check the permissions of lazyNtUser. The permissions to be set on the target server are as below.
  

View account metrics

Have you executed the following query based on the L07_FullCollectItemStatus.sql contents, and is IsEnabledYN all set to Y?

View ports and services for remote collection

1. WorkGroup server

• If it is a workgroup, create a lazyNtUser on the target server with the same account and password as the lazyNtUser on the server that runs lazylog.exe, then log in to Windows with this account.
• Run Perfmon in Execute.
• In the Add Counter menu, enter the IP to collect from the computer in the counter check box.
• Verify if the performance counter collection on the remote computer is working properly.

2. Active Directory server

• Run Perfmon in Execute.
• Verify if the performance counter collection on the remote computer is working properly.

3. Firewall

• Test in an environment where there is no firewall in the same IDC, or test in the same network environment and test if it works.
• If the RPC port is blocked, it is a good idea to place the collection program inside the firewall and specify an external SQL Server only for the collection results.
• Trace network packet analysis programs to both the collected and collecting computers and monitor whether the RPC 445 port is normal.
• Trace network packet analysis programs to both the collected and collecting computers and monitor whether the TCP 1443 port is normal.

4. Required services on monitored servers

• Is Remote Registry service enabled?
• Is Remote Procedure Call(RPC) service enabled?

5. SQL Server

• Check the sp_readerrorlog for any errors.
• Check for queries and errors with profiler or extended events.

Sample

MSSQL Log collector(LazyLog) sample

DMV data sample

The dashboard below is simply a graph using the above LazyLog collection data. Either Excel or a variety of dashboard tools can be used to draw dashboards and graphs.

AG monitoring

The following figure shows an example of configuring SQL Server high availability solution, AlwaysOn monitoring. The stack chart in Figure 1 below represents individual servers for each stack. It represents the capacity accumulated in the queue of the current primary server, the rate of sending from primary to secondary, the capacity accumulated in secondary queue redo, and the rate of secondary redo reflection respectively.

If you look at the central red box, you can monitor the stopped status of one server's log application. In this case, all logs to the secondary server have been monitored as being sent and it appears that only the application is stopped or very slow.
This case was troubleshot by restarting the secondary server. If you have not been monitoring this problem and have continued service, you may have been in a worst-case situation to reconfigure your AG. (The log application on the secondary server is designed with slightly different logic between active status and restarting. You can apply it more quickly if you apply logs by restarting.) In LazyLog, the following information is being monitored in the dm_hadr_database_replica_states_b table in 20 second increments.

Server resource usage monitoring

The following figure shows an example of a page configured to monitor the same group of servers and monitor CPU, BatchRequests/Sec, Memory status and network transfer rate at once. If there is a server that behaves differently than usual, you can find it immediately. LazyLog records performance counter information in 15 second increments.

Verify query resource usage

The following figure shows an example of query monitoring for two servers performing the same role. As they are databases that perform the same role, the query should be received similarly and the CPU WorkerTime should be similar. However, in this case you can recognize that only certain queries on one server in a given time period have increased resource usage.

For the central bubble chart, the X axis is the number of executions and the Y axis is WorkerTime. By looking at the query, you can instantly see which queries are consuming the most resources at specific times of the day.
dm_exec_query_stats2_b_summary of LazyLog shows detailed resource usage per query in 1 minute increments.

Lock monitoring

Below is an example of monitoring a lock. When a lock on the server occurs, you can immediately see which query is causing the lock and which query is pending. sp_lock2_b of LazyLog contains a lock monitoring detail with the query in 10 second increments. If lock monitoring is important in an OLTP environment, adjust the collection interval accordingly (3 seconds recommended).