MSSQL Log Collector (Lazylog) 사용 가이드

Classic/VPC 환경에서 이용 가능합니다.

소개

MSSQL 로그 수집기(LazyLog) 개요 및 필수 내용

로그 수집기 개요

SQL 서버 운영 시 모니터링 대상 서버의 설정이나, 프로그램의 쿼리 성능, 서버의 이벤트 로그, 성능 카운터의 베이스라인을 수집해 두는 것은 매우 중요합니다.
이러한 베이스라인 데이터가 없다면 현재 서비스가 정상인지 (어제와 다른지) 판단에 오랜 시간이 걸릴 수 있습니다. 이에 네이버클라우드에서는 SQL 서버 로그 수집 프로그램을 배포하고 있으며, 수집된 데이터는 장애 분석이나 성능 분석용 필수 데이터(윈도우 성능카운터, 윈도우 이벤트로그, SQL 에러로그 Agent 로그, 쿼리 구문, 쿼리 실행계획, 대기, 잠금 등 필수 DMV 스냅샷)입니다.

로그 수집 콘솔 응용 프로그램(LazyLog)은 SQL Server 2008, 2012, 2014, 2016을 지원하며, 서버 리소스가 허락하는 한 무제한의 대상 서버, 다중 SQL 인스턴스, 다중 데이터베이스를 지원합니다. 로그 수집기 프로그램은 하나의 서버에서 동시에 여러 개 동작 시킬 수 있으며, 이때는 폴더를 분리해 따로 실행하면 됩니다. 비슷한 서버 그룹을 묶어 모니터링 하면 서버의 설정이나 리소스 사용량 비교를 통해 정상 여부를 더욱 쉽게 판단할 수 있으므로 대상을 적절히 분리해 구성하는 것이 좋습니다. 여러 모니터링 대상 SQL 서버를 단 한 개의 그룹으로 모니터링 한다면, 윈도우 서비스프로그램으로도 사용 가능합니다. (설정 완료 후 Setup.exe을 동작시키고 설정 파일 수정 후 서비스 시작)

LazyLog는 사용자 정의 값(SqlLogManManager) 기반으로 대상 서버에 접근해 특정 데이터를 수집해 오고, 저장 기간을 초과한 데이터는 자동으로 삭제합니다.
수집 주기를 과도하게 짧게 설정하면 많은 로그를 저장해 데이터 저장용 데이터베이스가 매우 커지거나 수집 프로그램이 오동작할 수 있으므로, 수집 주기 조절과 데이터 보관 주기를 적절하게 설정해야 합니다. 또한 프로그램이 실행되기 위해 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 Server에 직접 설치하고, 모니터링 데이터베이스도 같은 SQLServer에 생성해 사용하는 방법입니다. 이 경우 네트워크를 통하지 않아 성능 카운터나 이벤트 로그 수집 속도는 빠르나 로그 수집 프로그램이 이상 동작하거나 (일반적으로 Agent 프로그램이 운영서버에서 동작하는 경우의 문제들) SQL 모니터링 데이터베이스 용량이 늘어날 경우 운영 서버에 영향을 줄 수 있어 권고하지 않습니다.

1대의 서버만 모니터링하는 경우 DMV, 성능 카운터 정보만 데이터베이스에 저장하고 이벤트 로그는 데이터베이스에 저장하지 않아도 됩니다. (직접 이벤트 로그를 조회하는 것이 더 좋을 수 있기 때문입니다.) 그러나, 어떤 특정 이벤트로 알람을 보내고자 할 때는 이벤트 로그를 SqlLogManPerfmonRepository(기본 저장소 이름이며 변경 가능합니다.)에 저장하면 좀 더 편리하게 관리 가능합니다.

구성안 2

구성안 2는 대부분의 모니터링 서버에 권장되는 모델입니다.

사용자 서버에는 어떠한 프로그램도 설치하지 않고 모니터링 서버를 따로 구성해 해당 서버에 SQL Server를 설치하고 ManagerDB(SqlLogManManager), 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 테이블 정보를 기반으로 어떤 카운터가 서버에 있는지 읽어오고, 읽어온 카운터는 SqlLogManManager.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비트)) 및 설정 수행 후 윈도우 내장 성능 카운터 프로그램으로 연결하면 저장된 기록을 볼 수 있습니다. (윈도우 계정만 가능하므로, 해당 윈도우 계정이 모니터링 수집 서버에 로그인 및 data_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 라는 계정은 SqlLogManManager 데이터베이스에 설정될 계정 정보입니다. 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와 Port로 접근 가능한지 테스트합니다.

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>

• 나머지 SqlLogManDmvRepositoryXXXX와 SqlLogManPerfmonRepositoryXXXX는 DMV 결과를 저장할 데이터베이스와 성능카운터와 이벤트 로그를 저장할 데이터베이스 정보입니다.

Hosts 파일 추가

모니터링 서버에서 모니터 대상 서버로 이름으로 접근 불가능 할 경우 windows/system32/drivers/etc/hosts 파일에 추가해 이름으로 접근 가능하게 합니다. 워크그룹 환경에서 호스트네임 추가는 성능카운터와 이벤트로그 수집에 필수입니다.

모니터 서버에 대상 서버 정보 입력

모니터링 대상 서버를 등록하는 과정입니다. SQLScript하위 폴더에서 L04_TargerServerRegister.sql을 열고 아래 변수를 수정하고 SQLCMD 모드로 실행합니다.

• SQLCMD 명령어

-- 변수 수정

:setvar ManagerDatabaseName SqlLogManManager
-- 설정이 저장될 데이터베이스 이름
: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 SqlLogManManager
-- 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 SqlLogManManager
     -- lazylog 모든 설정이 저장된 매니저 데이터베이스 이름 (모니터링 저장소 데이터베이스)
     :setvar SqlLogManDmvRepositoryDatabase SqlLogManDmvRepository
     -- DMV 수집 정보가 저장될 데이터베이스 이름
     -- 기본 아이템 수집에서 sysdatabases가 수집되어야 정상 등록이 가능합니다.
     -- 변수 수정 끝
     

     정상적으로 수행되면 아래와 같은 출력이 나옵니다.
     

2. 확장 모니터링 등록 스크립트 생성
   SQLScript하위 폴더에서 L06_TargetItemRegisterAdvanced_02.sql 파일을 열고 아래 변수를 수정 후 SQLCMD 모드로 실행합니다.

   • SQLCMD 명령어

     -- 변수 수정
     :setvar machineName AD01047147
     -- 모니터링 대상 hostname
     :setvar SqlLogManManagerDatabase SqlLogManManager
     -- 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 SqlLogManManager
     -- 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 나 모니터링 아이디를 적습니다. (도메인 명 제외)
   • 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 Server에 계정과 권한이 설정되어야 합니다.

   • SQL Server 계정으로 저장소 데이터베이스에 접근 가능해야 합니다. (lazyLog.config.XML에 설정) SSMS 나 SQLCMD로 테스트를 수행할 때는 lazyServer 계정이 암호화되기 전 암호값으로 lazyLog.exe 프로그램이 동작하는 컴퓨터에서 SqlLogManManager 데이터베이스가 있는 SQL Server에 로그인이 되는지 확인합니다. (IP,Port 정확하게 테스트)
   • lazyServer가 sysadmin이나 dbo 권한을 가지고 있는가?
     

2. lazyClient
   MachineSqlInstance에 설정되며, 수집 대상 SQL Server에 계정과 권한이 설정되어야 합니다.

   • 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 Server 고가용 솔루션인 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 도 비슷해야 합니다. 그런데 특정 시간대에 한 서버에서 특정 쿼리만 리소스 사용량이 높아진 것을 바로 인지 가능한 경우입니다.

중앙 버블 차트는 X축은 수행 회수, Y 축은 WokerTime입니다. 쿼리를 보고 특정 시간대 어떤 쿼리가 가장 리소스를 많이 소모하는지 즉시 알 수 있습니다.
LazyLog의 dm_exec_query_stats2_b_summary에는 1분 단위 쿼리별 리소스 사용량이 상세히 기록되어 있습니다.

잠금 모니터링

아래는 잠금을 모니터링 한 예입니다. 서버에서 잠금이 생기면 잠금의 원인 쿼리는 무엇이고 대기하는 쿼리가 어떤 쿼리인지 즉시 알 수 있습니다.
LazyLog의 sp_lock2_b에는 10초 단위 잠금 모니터링이 쿼리와 함께 상세히 기록되어 있습니다. OLTP 환경에서 잠금 모니터링이 중요하다면 수집 간격을 적절하게 조절합니다. (3초 권장)