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
