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

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

SQL Server에서 Trigger 활성 / 비활성 감시

 

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

 

SQL Server는 기본적으로 트리거의 활성/비활성화를 추적하지 않는다. 트리거를 생성하면 기본적으로는 활성화 되어 있다. 하지만 권한을 가진 다른 사용자가 트리거를 비활성 시킬 수 있다.

트리거의 활성/비활성을 감시하는데에는 많은 비용이 소모 될 수 있다. 실습을 통하여 트리거의 활성/비활성을 캡처하자.

 

 

다음과 같이 서버 감사를 생성하고 실행 한다.

CREATE SERVER AUDIT ServerAudit

TO FILE (FILEPATH = 'D:\', MAXSIZE = 1GB)

WITH (ON_FAILURE = CONTINUE);

GO

 

ALTER SERVER AUDIT ServerAudit WITH (STATE = ON);

 

 

 

서버 감사를 실행 하면 다음과 같이 지정된 경로에 감사 파일이 생성되는 것을 확인 할 수 있다.

 

 

데이터베이스 수준에서 감사 사양을 생성해야 한다.

캡처의 범위는 SCHEMA_OBJECT_CHANGE_GROUP이다.

CREATE DATABASE AUDIT SPECIFICATION schema_change

FOR SERVER AUDIT ServerAudit

ADD (SCHEMA_OBJECT_CHANGE_GROUP)

WITH (STATE = ON);

GO

 

 

 

 

간단한 트리거가 있는 테이블을 생성한다.

CREATE TABLE SW_Trigger( ID INT);

GO

 

CREATE TRIGGER T_SW_Trigger ON SW_Trigger

FOR INSERT

AS

BEGIN

    SELECT 1

END

GO

 

 

 

트리거 생성이 완료 되었으면 간단한 테이블을 만들어 본다. 그리고 다음의 코드를 실행하면 다음과 같이 ServerAudit 파일에서 트리거된 내용을 확인 할 수 있다.

SELECT

    EVENT_TIME,

    SUCCEEDED,

    SERVER_PRINCIPAL_NAME,

    [OBJECT_NAME],

    [STATEMENT]

FROM SYS.FN_GET_AUDIT_FILE ('D:\SERVERAUDIT*'    , NULL, NULL)

WHERE DATABASE_NAME = 'SW_TEST'

 

 

 

트리거를 활성 비활성화 해보자.

DISABLE TRIGGER T_SW_Trigger ON SW_Trigger

GO

 

ENABLE TRIGGER T_SW_Trigger ON SW_Trigger

GO

 

 

 

트리거의 활성/비활성 내용이 캡처 되고 있는지 확인 할 수 있다.

SELECT

    EVENT_TIME,

    SUCCEEDED,

    SERVER_PRINCIPAL_NAME,

    [OBJECT_NAME],

    [STATEMENT]

FROM SYS.FN_GET_AUDIT_FILE ('D:\SERVERAUDIT*'    , NULL, NULL)

WHERE DATABASE_NAME = 'SW_TEST'

 

 

비활성의 내용만 확인하려면 다음과 같이 where에 스크립을 추가 한다.

AND [statement] LIKE '%Disable%TRIGGER%'

 

특정 조건에 따라 필터를 적절히 활용 할 수 있도록 하자.

 

SQL Server에서 특정 동작을 캡처하는 방법에는 여러가지가 있다. 버전에 따라 약간의 제약사항은 있지만 데이터베이스 감사는 매우 중요하다. 많은 오버헤드를 발생하지 않는 적정선에서 잘 활용 할 수 있도록 하자.

 

[참고자료]

 

 


강성욱 / 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
» SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 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