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/
너무 많은 트리거는 시스템에 부하를 유발 할 수도 있다. 꼭 필요한 위치에 최소한의 트리거 기능을 이용하여 시스템에 영향을 주지 않도록 하자.
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
