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

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

Change Data Capture(CDC) – 변경 이력 추적

 

  • Version : SQL Server 2008, 2008R2, 2012

 

많은 분들의 문의 하는 내용 중 하나가 테이블의 데이터가 변경되었을 때 변경 내역을 알고 싶다는 것이다. 물론 프로시저를 사용할 경우 프로시저 내에서 기록용 코드를 추가하여 사용 할 수도 있지만 비즈니스를 변경 하지 않고 어떻게 추적이 가능 할까? 그래서 CDC 라는 기능을 소개 하려 한다.

 

CDC는 데이터베이스 및 테이블에 대해서 변경된 사항이 있으면 캡처 하는 기능이며 SQL Server 2008 부터 추가 되었다. 변경 내용 추적은 DML(INSERT, UPDATE, DELETE)에 대해서 가능 하다.

 

CDC 관령 링크 : http://msdn.microsoft.com/en-us/library/cc645937.aspx

 

다음 실습을 통해서 CDC 기능 활성화 및 사용법을 알아 보자.

 

CDC는 데이터베이스 수준에서 활성화 해야 한다. 기본 값은 비활성화 이다.

  • CDC 기능을 사용하기 위해서는 sysadmin 고정 서버 역할 멤버 자격이 있어야 한다.
  • CDC는 시스템DB는 사용할 수 없으며 사용자 데이터베이스만 가능하다.
  • 엔터프라이즈 및 개발자 에디션에서 가능 하다.
  • 테이블 변경 내용을 추적 하기 위해서 PK가 필요하다.

 

스크립트를 실행하면 현재 CDC의 활성/비활성 설정 및 각 데이터베이스의 CDC 상태를 확인 할 수 있다.

--ENABLE

DECLARE @CDC_ENABLED INT

EXEC @CDC_ENABLED = SYS.SP_CDC_ENABLE_DB

SELECT @CDC_ENABLED

GO

 

SELECT NAME, IS_CDC_ENABLED FROM SYS.DATABASES

GO

 

--DISABLE

DECLARE @CDC_ENABLED INT

EXEC @CDC_ENABLED = SYS.SP_CDC_DISABLE_DB

SELECT @CDC_ENABLED

GO

 

SELECT NAME, IS_CDC_ENABLED FROM SYS.DATABASES

GO

 

 

 

SSMS에서 확인

 

 

CDC 실습을 위해 테스트 테이블을 생성 한다.

CREATE TABLE DBO.CUSTOMER

(

ID INT IDENTITY NOT NULL

, NAME VARCHAR(50) NOT NULL

, STATE VARCHAR(2) NOT NULL

, CONSTRAINT PK_CUSTOMER PRIMARY KEY CLUSTERED (ID)

)

GO

 

CDC 기능 중 테이블 캡처를 위하여 다음 스크립트를 실행 한다.

위에서 생성한 테스트 테이블(CUSTOMER)에 대하여 CDC 활성화 하였으며 사용자 테이블에 설정된 CDC의 목록을 확인 할 수 있다.

--ENABLE

EXEC SYS.SP_CDC_ENABLE_TABLE

@SOURCE_SCHEMA = 'DBO',

@SOURCE_NAME = 'CUSTOMER' ,

@ROLE_NAME = 'CDCROLE',

@SUPPORTS_NET_CHANGES = 1

GO

 

SELECT NAME, TYPE, TYPE_DESC, IS_TRACKED_BY_CDC FROM SYS.TABLES

GO

 

--DISABLE

EXEC SYS.SP_CDC_DISABLE_TABLE

@SOURCE_SCHEMA = 'DBO',

@SOURCE_NAME = 'CUSTOMER',

@CAPTURE_INSTANCE = 'DBO_CUSTOMER' -- OR 'ALL'

 

 

 

데이터베이스 및 테이블 수준에서 CDC를 사용하면 cdc.sql2008demo_capture, cdc.sql2008demo_cleanup 에이전트 작업이 생성되었다는 메시지를 확인 할 수 있다. CDC가 실행되면 캡처를 위한 스키마가 생성되며 명명 규칙은 CDC 이다. 다음 스크립트를 통해 생성된 스키마를 확인 할 수 있다.

SELECT O.NAME, O.TYPE, O.TYPE_DESC FROM SYS.OBJECTS O

JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID

WHERE S.NAME = 'CDC'

 

 

SSMS에서 확인

 

 

CDC 기능을 실행 하였으면 캡처 기능을 확인 하기 위하여 테스트 테이블(CUSTOMER)에 데이터를 입력하고 수정 한다.

INSERT CUSTOMER VALUES ('1 KANG SUNG WOOK', 'AB')

INSERT CUSTOMER VALUES ('2 KANG SUNG WOOK', 'CD')

INSERT CUSTOMER VALUES ('3 KANG SUNG WOOK', 'EF')

UPDATE CUSTOMER SET STATE = 'PA' WHERE ID = 1

DELETE FROM CUSTOMER WHERE ID = 3

 

 

 

CDC가 테이블의 변경 사항을 추적하려면 LSN이 필요 하다. LSN은 고유 데이터베이스 트랜잭션 을 식별하기 위한 로그 번호이다. 최대 및 최소 LSN을 사용하여 변경 사항을 검색 할 수 있다.

DECLARE @BEGIN_LSN BINARY(10), @END_LSN BINARY(10)

SELECT @BEGIN_LSN = SYS.FN_CDC_GET_MIN_LSN('DBO_CUSTOMER')

SELECT @END_LSN = SYS.FN_CDC_GET_MAX_LSN()

SELECT * FROM CDC.FN_CDC_GET_NET_CHANGES_DBO_CUSTOMER(@BEGIN_LSN, @END_LSN, 'ALL');

SELECT * FROM CDC.FN_CDC_GET_ALL_CHANGES_DBO_CUSTOMER(@BEGIN_LSN, @END_LSN, 'ALL');

 

 

 

위 스크립트 결과에서 첫 번째 집합은 net change에 대한 변경사항을 나타내며 두 번째 결과는 개별적인 변경 사항을 나타낸다. 첫 번째 결과 집합은 행을 표시하지 않으며 두 번째 결과 집합은 변경사항에 대한 행을 표시해 준다. 각 열에 대한 정보를 알아 보자.

컬럼명

설명

__$start_lsn

 

변경의 커밋 순서를 유지하고 있는 변경과 관련된 LSN. 동일한 트랜잭션에서 커밋된 LSN은 동일하다.

__$operation

1

삭제

2

삽입

3

업데이트 이전 값

4

업데이트 후 값

__$update_mask

 

캡처된 열에 해당하는 비트마스크.

 

 

다음 스크립트는 위의 CDC 확인 스크립트를 이용하여 변경된 내용에 대해서 사용자 테이블에 보관하는 하려고 할 때 사용 할 수 있다.

CREATE TABLE DBO.CUSTOMER_LSN (

LAST_LSN BINARY(10)

)

GO

 

CREATE FUNCTION DBO.GET_LAST_CUSTOMER_LSN()

RETURNS BINARY(10)

AS

BEGIN

DECLARE @LAST_LSN BINARY(10)

SELECT @LAST_LSN = LAST_LSN FROM DBO.CUSTOMER_LSN

SELECT @LAST_LSN = ISNULL(@LAST_LSN, SYS.FN_CDC_GET_MIN_LSN('DBO_CUSTOMER'))

RETURN @LAST_LSN

END

 

DECLARE @BEGIN_LSN BINARY(10), @END_LSN BINARY(10)

SELECT @BEGIN_LSN = DBO.GET_LAST_CUSTOMER_LSN()

SELECT @END_LSN = SYS.FN_CDC_GET_MAX_LSN()

SELECT * FROM CDC.FN_CDC_GET_NET_CHANGES_DBO_CUSTOMER(@BEGIN_LSN, @END_LSN, 'ALL');

SELECT * FROM CDC.FN_CDC_GET_ALL_CHANGES_DBO_CUSTOMER(@BEGIN_LSN, @END_LSN, 'ALL');

 

UPDATE DBO.CUSTOMER_LSN

SET LAST_LSN = @END_LSN

IF @@ROWCOUNT = 0

INSERT INTO DBO.CUSTOMER_LSN VALUES(@END_LSN)

GO

 

SELECT * FROM DBO.CUSTOMER_LSN

 

 

 

CDC기능은 변경 내용 추적 및 감사 등 여러 가지 용도로 응용하여 사용 할 수 있다.



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 713
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2906
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1278
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1061
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1541
1705 BCP 사용 jevida(강성욱) 2016.09.13 3280
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
» Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2372
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 6310
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1536
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1602
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1701
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1570
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1768
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10638
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1103
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2562
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 864





XE Login