데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL Server SP_Congifure 변경 사항 캡처

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server가 처음 구성되면 DBA는 많은 구성관리를 한다. DBA는 승인되지 않은 구성 옵션의 변경을 감지하고 변경된 내용을 추적 할 수 있어야 한다.

 

기본적으로 추적에 사용할 수 있는 도구는 Profiler 또는 'fn_trace_gettable(@trc_Path) 가 있다. 이번 실습에서는 'fn_trace_gettable(@trc_path)'를 사용하여 변경사항을 확인해 보자.

 

우선 기본 추적이 활성화 되어 있는지 쿼리 이용하여 확인해 보자.

SELECT

     NAME, CASE WHEN VALUE_IN_USE = 1 THEN 'ENABLED'

                WHEN VALUE_IN_USE = 0 THEN 'DISABLED' END AS [STATUS]

FROM SYS.CONFIGURATIONS

WHERE NAME = 'DEFAULT TRACE ENABLED'

 

 

 

변경된 히스토리를 보관할 테이블을 생성한다.

CREATE TABLE SQLCONFIG_CHANGE (

TEXTDATA NVARCHAR(500),

HOSTNAME NVARCHAR(155),

APPLICATIONNAME NVARCHAR(255),

DATABASENAME NVARCHAR(155),

LOGINNAME NVARCHAR(155),

SPID INT,

STARTTIME DATETIME,

EVENTSEQUENCE INT

)

 

 

구성설정이 변경된 이력을 캡처하고 저장하는 저장 프로시저를 생성한다.

CREATE PROCEDURE USP_SQLCONFIG_CHANGE

 

AS

 

CREATE TABLE #TEMP_CONFIGURE(

TEXTDATA NVARCHAR(500),

HOSTNAME NVARCHAR(155),

APPLICATIONNAME NVARCHAR(255),

DATABASENAME NVARCHAR(155),

LOGINNAME NVARCHAR(155),

SPID INT,

STARTTIME DATETIME,

EVENTSEQUENCE INT

)

 

DECLARE @TRC_PATH NVARCHAR(500)

SELECT @TRC_PATH = CONVERT(NVARCHAR(500), VALUE) FROM FN_TRACE_GETINFO (DEFAULT) WHERE PROPERTY = 2

 

INSERT INTO #TEMP_CONFIGURE

SELECT TEXTDATA, HOSTNAME, APPLICATIONNAME, DATABASENAME, LOGINNAME, SPID, STARTTIME, EVENTSEQUENCE

FROM FN_TRACE_GETTABLE (@TRC_PATH, 1)

WHERE TEXTDATA LIKE '%CONFIGURE%'

AND SPID <> @@SPID

AND EVENTSEQUENCE NOT IN (SELECT EVENTSEQUENCE FROM SQLCONFIG_CHANGE)

AND TEXTDATA NOT LIKE '%INSERT INTO #TEMP_CONFIG%'

ORDER BY STARTTIME DESC

 

INSERT INTO SQLCONFIG_CHANGE

SELECT * FROM #TEMP_CONFIGURE

 

 

 

구성 설정을 변경하고 저장 프로시저를 호출하여 보자. 그리고 변경된 이력을 저장하는 테이블을 조회하면 변경 이력이 캡처 된 것을 확인 할 수 있다.

SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1

GO

RECONFIGURE WITH OVERRIDE

GO

 

SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1

GO

RECONFIGURE WITH OVERRIDE

GO

 

EXEC USP_SQLCONFIG_CHANGE

GO

SELECT * FROM SQLCONFIG_CHANGE

 

 

 

 

생성된 프로시저를 SQL Serve Agent를 통하여 1분 또는 상황에 따라 예약 작업을 설정해 놓으면주기적으로 변경사항을 캡처 할 수 있다. 또한 변경이력을 캡처하는 프로시저에 Email 또는 SMS 서비스를 호출하는 기능을 추가하면 변경 사항이 발생 하였을 때 DBA가 신속히 보고를 받을 수 있다.



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 866
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 713
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2984
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1291
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1063
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1565
1705 BCP 사용 jevida(강성욱) 2016.09.13 3380
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2475
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 7080
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1565
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1609
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1796
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1575
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1812
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10929
» SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1109
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2648





XE Login