MSSQL 부가 기능

MSTSC 접근

1. 시작 실행에서 MSTSC를 실행 후, 위에서 설정한 공인 IP 주소로 접근합니다.
2. [연결] 버튼을 클릭합니다.
3. Pem 파일을 이용해 생성한 암호를 입력합니다.
4. [확인] 버튼을 클릭합니다.
5. [예(Y)] 버튼을 클릭합니다.
6. 서버에 로그온이 정상적으로 수행됩니다.

SQL 인증을 이용한 외부에서 서버 접근

공인 IP 설정과 SQL 인증을 이용해 네이버 클라우드 플랫폼 MSSQL에 접근하는 방법을 설명합니다.

공인 IP 설정

위에서 생성된 서버에 외부에서 접근하기 위해서는 공인 IP 설정이 필요합니다.

1. 메뉴에서 Public IP를 클릭합니다.
2. 공인 IP 신청을 클릭합니다.
3. 적용 서버 선택 드랍 다운에서 생성된 서버를 선택합니다.
4. [확인] 버튼을 클릭합니다.
5. 적용 서버이름을 확인하고, [확인] 버튼을 클릭합니다.
6. 수분 후 IP 주소가 할당됩니다.
7. 정상적인 공인 IP가 할당되면 49.236.160.54:3389로 MSTSC 접근을 하면 해당 서버에 접속됩니다.
8. Public IP를 할당받은 경우, 기본 포트는 3389입니다.

SQL Server SQL 인증 설정하기

SSMS 실행

1. Windows Powershell 아이콘을 클릭합니다.
2. SSMS를 실행합니다.
3. [연결] 버튼을 클릭합니다.

SQL 인증모드 설정

1. SQL 인스턴스에서 우클릭합니다.
2. 속성을 클릭합니다.
3. 페이지 선택에서 보안을 클릭합니다.
4. 서버 인증에서 SQL Server 및 Windows 인증모드(S)를 선택합니다.
5. 확인을 클릭합니다.

MSSQL 서비스 재시작

1. SQL 인스턴스에서 우클릭합니다.
2. 다시 시작을 클릭하고, 확인 팝업에서 예(Y)를 선택합니다.

SQL 보안인증 계정 생성

1. SSMS 보안 로그인에서 우클릭 후 새 로그인(N)을 클릭합니다.
2. 사용할 아이디(sqlsa)를 입력하고, SQL Server 인증(S)를 선택합니다.
3. 해당하는 암호를 입력하고, 아래 암호 정책 강제 적용에 체크 해지를 합니다.
4. 서버 역할을 클릭합니다.
5. sysadmin에 체크합니다. (서비스에서 사용할 적절한 권한을 설정합니다.)
6. 확인을 클릭합니다.

SSMS를 이용하여 SQL 인증 모드로 DB 서버에 연결하기

1. 외부에서 연결하기 이전에 내부에서 SQL 인증이 성공적으로 되는지 파일 > 새로 만들기 > 데이터베이스 엔진 쿼리를 선택합니다.
2. 위에서 생성한 정보를 정확히 입력합니다.
3. 연결을 클릭합니다.
4. 성공적으로 연결되면 select @@version이라고 입력하고, 실행을 클릭합니다.

모두 정상적으로 수행이 되었다면, 외부에서도 정상적으로 로그인이 가능합니다.

MSSQL 설치형 한글 환경으로 변경

네이버 클라우드 플랫폼에서 MSSQL 설치형 서버를 사용하는 경우 기본 환경은 영어로 설정되어 있습니다. 본 가이드에서는 MSSQL의 언어 환경을 한글로 변경하는 방법을 설명합니다. 같은 방법으로 타언어(일본어, 중국어 등)로도 변경할 수 있습니다.
변경 순서는 다음과 같습니다.

1. 윈도우 언어 및 시스템 Locale 변경
2. MSSQL의 Collation 변경
3. SSMS 한글판 설치

윈도우 언어 및 시스템 Locale 변경

생성한 MSSQL 서버의 언어 및 시스템 Locale 설정을 한국어와 한국으로 변경합니다. 언어와 국가를 변경하지 않으면 한글판 SSMS을 설치할 수 없습니다.

• Win 2012R2, 2016
  • Control Panel > Clock, language and Region > Language 메뉴를 클릭해 주십시오.
    • 한국어 우선 순위를 가장 높게 설정해 주십시오.
    • Language pack이 설치되어 있지 않은 경우 Options 를 클릭한 후 설치해 주십시오.
  • Control Panel > Clock, language and Region > Region 메뉴를 클릭해 주십시오.
    • [Location] 탭의 Home location을 Korea로 선택해 주십시오.
    • [Administrative] 탭의 [Change system locale...] 버튼을 클릭해 주십시오.
    • Current system locale을 Korean(Korea)로 선택한 후 [OK] 버튼을 클릭해 주십시오.
  • 서버를 재시작 해 주십시오.
• Win 2019
  • Windows Settings > Time&Language > Language 메뉴를 클릭해 주십시오.
    • Preferred languages의 한국어 우선 순위를 가장 높게 설정해 주십시오.
    • 한국어의 [Options] 버튼을 클릭한 후 Language pack을 설치해 주십시오.
    • 한국어 Language pack 설치가 끝난 후 Windows display language를 한국어로 선택해 주십시오.
  • Windows Settings > Time&Language > Region 메뉴를 클릭해 주십시오.
    • Country or region을 Korea로 선택해 주십시오.
  • Control Panel > Clock and Region > Region 메뉴를 클릭해 주십시오.
    • [Administrative] 탭의 [Change system locale...] 버튼을 클릭해 주십시오.
    • Current system locale을 Korean(Korea)로 선택한 후 [OK] 버튼을 클릭해 주십시오.
  • 서버를 재시작 해 주십시오.

MSSQL의 Collation 변경

MSSQL의 Collation을 한국어로 변경합니다. 변경하는 과정에서 Master 데이터베이스가 Rebuild 되므로 DB 백업해 주십시오. 변경하는 방법은 다음과 같습니다.

1. CMD(Command) 프로그램을 실행해 주십시오.
2. 다음 명령을 실행하여 MSSQL 서버 재시작 및 SQL 서버 에이전트를 시작해 주십시오.

net stop mssqlserver
net start mssqlserver
net start sqlserveragent

3. MSSQL 버전에 따라 Setup Bootstrap 폴더로 이동해 주십시오.
   • MSSQL 2012
     cd C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
   • MSSQL 2014
     cd C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
   • MSSQL 2016
     cd C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016
   • MSSQL 2017
     cd C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017
   • MSSQL 2019
     cd C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019
4. Collation 변경 명령을 실행해 주십시오.
   .\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=administrator /SAPWD=Str0ngUserP@ssw0rd /SQLCOLLATION=Korean_Wansung_CI_AS

SSMS 한글판 설치

SSMS(SQL Server Management Studio)는 MSSQL Engine과는 별도의 GUI 기반의 관리 툴 입니다. Microsoft 공식 웹사이트에서 한글 버전의 SSMS를 무료로 다운로드 및 사용할 수 있으며 MSSQL 버전과 상관없이 최신 버전의 SSMS를 다운로드하여 사용합니다.

1. 프로그램 다운로드 및 설치를 위해 IE 보안 강화를 해제해 주십시오.
   • 서버 관리자 > 로컬 서버 > IE 보안 강화 구성 > 관리자: 사용 안 함
2. Microsoft 공식 웹사이트에서 한글 버전의 SSMS를 다운로드해 설치해 주십시오.
   • 서버에 최신버전의 SSMS가 설치되어 있는 경우 기존에 설치되어 있는 SSMS를 제거한 후 한글 버전을 설치해 주십시오.
   • 제어판 > 프로그램 제거 에서 Microsoft SQL Server Management Studio를 제거할 수 있습니다.
3. sa계정의 암호 및 로그인 인증 모드를 설정해 주십시오.
   • sqlcmd 에서 다음 명령을 실행해 주십시오.

  USE [master]
  GO
  ALTER LOGIN [sa] WITH PASSWORD=N'설정할 패스워드'
  GO
  ALTER LOGIN [sa] ENABLE
  GO
  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
  GO

MSSQL 로그 수집기

소개

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 폴더에 일별 아이템별로 생성되게 되어 있으므로, 수집에 문제가 있으면 참고합니다.

로그 수집기 구성 방법

로그 수집기는 원격 수집이 가능하므로 수집 대상 서버와 별도로 설치하는 것을 권고하고 수집 대상 서버에 직접 설치도 가능합니다. 아래 몇몇 예시를 들어 설치 가능한 구성을 설명합니다. 본 설치 가이드에서는 방화벽과 계정 설정에 제한이 없는 로컬 설치(구성안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 권한이 있어야 합니다.)

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은 기본 모니터링에 바로 추가 가능한 항목입니다.

Dynamic Management View

Always On Availability의 수행 현황, 현재 상태, 쿼리별 리소스 사용량, 쿼리, 쿼리 플랜, 잠금, 대기 분석, 파일별 I/O 양, 인덱스 구조, 통계 일자, 서버 메모리 현황, Agent 실행 기록 등을 수집해 저장하고 있습니다.

LazyLog는 SQL Server 운영, 문제 해결에 도움이 되는 대부분의 정보를 수집하고 있습니다. 자세한 모니터링 항목은 아래 표를 참고합니다.

중요도는 LazyLog 개발자가 SQL Server 모니터링 시 주요하게 살펴봐야 할 내용을 1로 했으며, 특수한 문제 해결이나 그 중요도가 낮은 것은 3으로 설정했습니다.
특별히 문제가 없다면, 모든 설정을 활성화해 모니터링 합니다. 저장 공간 문제가 있다면 아이템의 수집 주기를 변경하거나 삭제, 한계 시간을 조절하면 됩니다.

데이터베이스 스키마

MSSQL 로그 수집기(LazyLog) 데이터베이스 구조

SqlLogManManager

수집 대상 서버 정보를 저장하는 테이블과, 수집 설정에 관계된 모든 설정이 들어있는 데이터베이스입니다. (이 데이터베이스와 lazylog.exe.config 파일만 백업하면 언제든지 수집 서버를 복구할 수 있습니다.)

SqlLogManPerfmonRepository

성능 카운터와 이벤트로그를 저장하는 데이터베이스입니다.

설치

MSSQL 로그 수집기(LazyLog) 설치 가이드

프로그램 설치

개요

• 암호를 평문으로 저장하면, 해킹이나 보안에 문제가 있으므로 암호를 다시 암호화해 사용해야 합니다. [필수]
• 성능카운터와 이벤트로그를 모니터링 대상 서버에서 읽어오기 위해 대상 서버에서 윈도우 계정설정, 권한설정, 필수 서비스시작, 방화벽 설정이 필요합니다.
• 모니터링 대상 서버를 대상으로 모니터 서버에서 성능카운터, 이벤트로그, DMV를 조회할 수 있는지 먼저 확인이 필요합니다
• 저장소 데이터베이스 3개를 생성합니다.
• lazylog.exe.config 파일에는 항목의 글로벌 설정과 수집기 설정이 저장되는 데이터베이스, 수집 결과가 저장되는 데이터베이스 정보가 기록됩니다.
• SqlLogManManager 데이터베이스에 수집 대상 서버의 접속 정보를 저장합니다.
• SqlLogManManager 데이터베이스에 서버별 수집 아이템을 등록합니다. (성능카운터 수집여부, 이벤트로그 각 항목 수집여부, DMV 각 항목 수집여부 등)
  • 설정이 복잡해 등록 스크립트를 생성해주는 프로시저가 제공되므로 스크립트를 생성해 SQLCMD 나 SSMS에서 실행합니다.
• 기본 수집과 확장 수집이 있는데, 확장 수집의 경우
  • SqlLogManDmvRepository.dbo.sysdatabases_b 수집이 완료되어야 합니다. (기본 아이템 중 sysdatabases를 수집해야 함)
• SqlLogManDmvRepository 데이터베이스에는 SQL Server 관련 모니터링 항목이 저장됩니다.
• SqlLogManPerfmonRepository 데이터베이스에는 성능카운터와 이벤트로그 수집 결과가 저장됩니다.

콘솔 모드 설치

LazyLog.zip 파일의 압축을 해제 하면 아래와 같은 파일이 있습니다.

탐색기 보기 옵션에서 파일 확장명에 체크를 하지 않으면, LazyLog.exe는 LazyLog, LazyLog.exe.config는 LazyLog.exe로 보이므로 LazyLog.exe.config와 LazyLog.exe를 혼동하지 않도록 주의합니다.

암호 생성

LazyLog에서 사용하는 암호는 LazyLog를 통해 생성한 암호화된 문자열을 사용해야 합니다. 암호화된 문자열 생성은 cmd에서 LazyLog “암호문자열”을 입력하여 생성합니다. 아래는 3개의 암호를 생성한 예제입니다. 테스트 설정의 경우 모두 같은 암호를 설정하는 것도 좋은 방법입니다. 그러나, 실제 모니터링 환경에서는 서버별 암호가 모두 다를 수 있습니다. 테스트의 경우 암호도 가이드와 같게 생성해 실습하면 더욱 쉽게 설정 할 수 있습니다.

모니터링 대상 서버 설정

• 서버 설정
  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
  

• 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 모드 (변수 부분이 반전되어 있음)

모니터링 저장소 데이터베이스 생성

• 저장소 데이터베이스가 생성될 폴더를 생성합니다.

  • 윈도우 CMD 명령어

  mkdir c:\lazylog
  

  폴더가 생성되면 됩니다. (데이터베이스 이름은 자유롭게 변경 가능합니다. 나중에 해당 폴더에 데이터베이스가 생성됩니다.) 처음 실습에서는 그대로 유지합니다.

• 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개가 생성됩니다.

모니터링 저장소용 계정 생성

위 단계에서 만든 데이터베이스에 접근할 계정을 만들어 줍니다.

• SQLScript하위 폴더에 L02_Account_lazyServer.sql 파일을 엽니다.

  • SQLCMD 명령어

  -- 변수 수정
  :setvar AccountName lazyServer
  :setvar AccountPassword P@ssw0rd3
  -- 변수 수정 끝
  

• 변수를 수정하고 SQLCMD 모드로 실행합니다.
  lazylog 응용 프로그램이 구동하는 곳에서 ip, port, 계정으로 SSMS로 모니터링 저장소 서버에 로그인이 되는지 반드시 테스트 합니다. (SSMS로 로그인할 경우는 원래의 암호 P@ssw0rd3로 접근) GUI로 설정할 경우, 옵션에 따라 암호를 변경해줘야 로그인되는 경우가 있습니다. lazyServer의 암호 P@ssw0rd3는 Lazylog.exe.config에 넣을 때는 qcWa7DCqnN7vqMZ6b9ilzw== 로 암호화된 값을 입력합니다.

• 생성한 계정과 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가 정상 실행되는 모습입니다.

기본 모니터링 정상 수집 확인

• 성능카운터
  정상 수집이 되는지 확인하기 위해 LazyLog가 구동되고 난 후 약 2분 후 아래 테이블을 확인합니다.

  • SQLCMD 명령어

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

• 이벤트로그
  정상 수집이 되는지 확인하기 위해 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

확장 모니터링 등록

• 사용자 데이터베이스 정보 확인 및 복사
  확장 모니터링은 기본 모니터링 항목 중 바로 위에서 확인한 sysdatabase가 정상 수집 되어야 수집 가능합니다. SQLScript하위 폴더에서 L06_TargetItemRegisterAdvanced_01.sql 파일을 열고 아래 변수를 수정 후 SQLCMD 모드로 실행하면, 사용자 데이터베이스 정보를 SqlLogManManager.dbo.MachineSqlDatabase에 입력하게 됩니다.

  • SQLCMD 명령어

  -- 변수 수정
  :setvar machineName AD01047147
  -- 모니터링 대상 hostname
  :setvar SqlLogManManagerDatabase SqlLogManManager
  -- lazylog 모든 설정이 저장된 매니저 데이터베이스 이름 (모니터링 저장소 데이터베이스)
  :setvar SqlLogManDmvRepositoryDatabase SqlLogManDmvRepository
  -- DMV 수집 정보가 저장될 데이터베이스 이름
  -- 기본 아이템 수집에서 sysdatabases가 수집되어야 정상 등록이 가능합니다.
  -- 변수 수정 끝
  

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

  • SQLCMD 명령어

    -- 변수 수정
    :setvar machineName AD01047147
    -- 모니터링 대상 hostname
    :setvar SqlLogManManagerDatabase SqlLogManManager
    -- lazylog 모든 설정이 저장된 매니저 데이터베이스 이름 (모니터링 저장소 데이터베이스)
    -- 변수 수정 끝
    

  정상 수행이 되면, 실행할 스크립트가 출력됩니다.
  생성된 스크립트 중 “실행할 스크립트”라고 된 스크립트 2개 세트를 SSMS에서 실행합니다.

• 확장 모니터링 스크립트 활성화
  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) 활용 예시

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초 권장)