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

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

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

No. Subject Author Date Views
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
» DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login