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
