DDL Trigger를 이용한 데이터베이스 변경 사항 추적

DDL Trigger를 이용한 데이터베이스 변경 사항 추적

 

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

 

누군가 데이터베이스에 접속하여 프로시저를 생성하고, 테이블을 생성하고, 스키마를 변경한다. 어떻게 추적할 수 있을까?

DDL 트리거를 이용하여 데이터베이스의 변경사항을 추적하여 보자.

 

변경이력을 저장할 테이블을 생성한다.

CREATE TABLE dbo.DDLEvents( EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, EventType NVARCHAR(64), EventDDL NVARCHAR(MAX), EventXML XML, DatabaseName NVARCHAR(255), SchemaName NVARCHAR(255), ObjectName NVARCHAR(255), HostName VARCHAR(64), IPAddress VARCHAR(32), ProgramName NVARCHAR(255), LoginName NVARCHAR(255) );

 

 

 

트리거를 생성한다. 트리거 생성 시 프로시저, 테이블의 생성, 수정, 삭제, 스키마 변경등 추적 옵션에 따라 다양하게 설정 할 수 있다.

CREATE TRIGGER DDLTrigger_Sample ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_SCHEMA, ALTER_SCHEMA, RENAME, CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS   SET NOCOUNT ON;   DECLARE @EventData XML = EVENTDATA();   DECLARE @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID );   INSERT dbo.DDLEvents ( EventType, EventDDL, EventXML, DatabaseName, SchemaName, ObjectName, HostName, IPAddress, ProgramName, LoginName ) SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), @EventData, DB_NAME(), @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();

 

 

 

트리거 생성이 완료 되었으면 변경 사항이 잘 추적되는지 테이블을 생성하고 프로시저를 생성, 삭제 하는 등 이벤트를 발생 시켜보도록 하자.

CREATE PROCEDURE USP_PROC1   AS   SELECT 1 GO   ALTER PROCEDURE USP_PROC1   AS   SELECT 2 GO   EXEC SP_RENAME 'USP_PROC1', 'USP_PROC2' GO   DROP PROCEDURE USP_PROC2 GO   CREATE TABLE TBL_X( COL_1 INT ) GO   ALTER TABLE TBL_X     ADD COL_2 INT GO   EXEC SP_RENAME 'TBL_X', 'TBL_Y' GO   DROP TABLE TBL_Y GO

 

 

 

 

변경 이력을 저장하는 테이블을 조회해 보면 다음과 같이 이벤트에 대해 모두 추적이 된 것을 확인 할 수 있다.

SELECT * FROM DDLEVENTS

 

 

 

아래 스크립트는 위의 변경이력 테이블에 저장된 데이터를 기반으로 최근의 변경 내역을 확인 할 수 있는 스크립트 이다.

;WITH [Events] AS ( SELECT EventDate, DatabaseName, SchemaName, ObjectName, EventDDL, rnLatest = ROW_NUMBER() OVER ( PARTITION BY DatabaseName, SchemaName, ObjectName ORDER BY EventDate DESC ), rnEarliest = ROW_NUMBER() OVER ( PARTITION BY DatabaseName, SchemaName, ObjectName ORDER BY EventDate ) FROM dbo.DDLEvents ) SELECT Original.DatabaseName, Original.SchemaName, Original.ObjectName, OriginalCode = Original.EventDDL, NewestCode = COALESCE(Newest.EventDDL, ''), LastModified = COALESCE(Newest.EventDate, Original.EventDate) FROM [Events] AS Original LEFT OUTER JOIN [Events] AS Newest ON Original.DatabaseName = Newest.DatabaseName AND Original.SchemaName = Newest.SchemaName AND Original.ObjectName = Newest.ObjectName AND Newest.rnEarliest = Original.rnLatest AND Newest.rnLatest = Original.rnEarliest AND Newest.rnEarliest > 1 WHERE Original.rnEarliest = 1;

 

 

 

[참고 자료]

http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

 

 

너무 많은 트리거는 시스템에 부하를 유발 할 수도 있다. 꼭 필요한 위치에 최소한의 트리거 기능을 이용하여 시스템에 영향을 주지 않도록 하자.

 

2013-02-15 / 강성욱 / http://sqlmvp.kr