SQL Server SP_Congifure 변경 사항 캡처

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가 신속히 보고를 받을 수 있다.

 

2012-12-14 / 강성욱 / http://sqlmvp.kr