1 RTE와 SQL Server

2 Change Data Capture

3 CDC를 사용하면 얻게 되는 이점

4 CDC 사용 예

5 Change Data Tracking

6 참고자료

RTE란 용어를 접한 지 몇 년이 된 것 같다. 인터넷의 기사나 각종 문서에서는 RTE와 더불어 SOA라 는 용어를 써먹어 가면서 '실시간'이 대세라고들 말하고 있었다. SQL Server 2005 버전부터는 이런 시장의 요구사항을 어느 정도 수용하고 있다. DW쪽 에서는 아직까지는 SQL Server를 이용한 '실시간 DW'에 대한 사례를 찾아 볼 수는 없었다. 하지만 SQL Server 2005버전에서는 Service Broker라는 기능으로 SOA의 요구사항을 수용하려 하였다. 즉, RTE의 핵심인 '적시에 정보를 제공/소비'라는 것을 Service Broker를 이용한 '비 동기 트리거' 로 밀어 붙였으나 별로 인기를 얻지는 못한 것 같다. 아무래도 귀찮다. 일일이 비 동기 트리거를 구현하려면 말이다. SQL Server 2008에서는 '실시간'이라는 것을 더욱 밀어붙였던 것 같다. 바로 'Change Data Capture/Tracking' 이라는 기능으로. 실시간 DW의 큰 그림은 아마도 다음과 같을 것이다.

어쨌든 반가운 소식이다. 하지만 신중해야 한다는 것. 구관이 명관일 경우도 허다하다? 암턴.. 중요한 것은 개념이지 구현방법은 아니다만 어쨌든 SQL Server 2008의 새로운 기능인 Change Data Capture & Tracking에 대한 정리를 대충 해봤다.

변경 데이터 캡처(Change Data Capture, 이하 CDC)는 Insert, Update, Delete와 같은 연산에 대한 변경내용의 세부 정보를 쉽게 사용 할 수 있도록 관계형 형식으로 접근 할 수 있는 SQL Server 2008의 새로운 기능이다.

Source Table에서 변경이 일어나게 되면 로그파일에 변경된 사항이 반영된다. Change Capture Process는 이를 감지하여 Change Table에 저장하게 된다. 이러한 변경된 데이터에 대한 소비자는 SQL Server 2008에서 CDC를 위해 여러 Function들을 제공하여, 직접 Change Table에 접근하지 않고, 쉽게 관계형 테이블 형태로 접근이 가능하게 한다.

• 데이터 동기화를 위한 트리거, 타임스템프열, 기타 추가 열 및 테이블이 불필요
  
• DML작업이 발생 할 때가 아닌 커밋할 때 내용이 추적된다.
  
• CDC를 위해 제공되는 함수는 테이블 및 버전 정보에 대한 증분 변경 내용을 반환
  
• 커밋되지 않은 트랜잭션이 있을 때에도 신뢰할 수 있다.
  
• 성능 오버헤드는 최소 수준이다.
  
• 변경 내용 추적 데이터는 자동으로 정리할 수 있다.
  
  

SQL Server 원본 테이블에서 데이터 웨어하우스나 데이터 마트로 변경 데이터를 증분 로드하는 ETL(추출, 변환 및 로드) 응용 프로그램을 들 수 있다. 데이터 웨어하우스 내에서 원본 테이블의 표현은 해당 테이블의 변경 내용을 반영해야 하지만 원본 복제본을 새로 고치는 종단 간 기술은 적합하지 않다. 여기에는 소비자가 다른 종류의 데이터 대상 표현에 적용할 수 있도록 구조화된 안정적인 변경 데이터 스트림이 필요하다. SQL Server 변경 데이터 캡처가 이러한 기술을 제공한다.

1. 데이터베이스 CDC 설정
CDC가 설정되면 해당 데이터베이스에 cdc 스키마, cdc 사용자, cdc 메타데이터 테이블 및 다른 시스템 개체가 생성된다. (즉, CDC를 위한 환경이 만들어 진다.) cdc라는 사용자 정의 스키마 및 사용자가 존재하는 한 변경 데이터베이스 캡처를 설정할 수 없다. (SQL Agent 시작 중이어야 함)

USE AdventureWorks2008;
GO
EXEC sys.sp_cdc_enable_db;
GO
SELECT 
	name db_name
,	is_cdc_enabled 
FROM master.sys.databases;
GO

2. 테이블 CDC 설정
(하나의 원본 테이블에 캡처 인스턴스를 2개까지 설정 가능)

IF OBJECT_ID('dbo.CDC_Test') IS NOT NULL
	DROP TABLE dbo.CDC_Test;
GO

CREATE TABLE dbo.CDC_Test
(
	UserID varchar(20) NOT NULL
,	PW varchar(20)
,	ModifyDT datetime
);

INSERT dbo.CDC_Test VALUES 
	('yasicom', '1234', GETDATE())
,	('sqlserver', '1234', GETDATE())
,	('oracle', '1234', GETDATE());
GO

CREATE UNIQUE INDEX UIX_UserID
ON dbo.CDC_Test(UserID);
GO

/*
sys.sp_cdc_enable_table 
    [ @source_schema = ] 'source_schema', 
    [ @source_name = ] 'source_name' ,
    [ @role_name = ] 'role_name'
    [,[ @capture_instance = ] 'capture_instance' ]
    [,[ @supports_net_changes = ] supports_net_changes ]
    [,[ @index_name = ] 'index_name' ]
    [,[ @captured_column_list = ] 'captured_column_list' ]
    [,[ @filegroup_name = ] 'filegroup_name' ]
    [,[ @partition_switch = ] 'partition_switch' ]
*/

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo'
  , @source_name = N'CDC_Test'
  , @role_name = N'CDC_Admin'
  , @capture_instance = N'CDC_Instance_01' 
  , @supports_net_changes = 1
  , @index_name = N'UIX_UserID' 
  , @captured_column_list = N'UserID, PW, ModifyDT' 
  , @filegroup_name = N'PRIMARY';
GO

SELECT is_tracked_by_cdc
FROM sys.tables 
WHERE name = 'CDC_Test';
GO

3. CDC 예제

UPDATE dbo.CDC_Test
SET 
	PW = 'asdf'
,	ModifyDT = GETDATE()
WHERE UserID = 'yasicom';
GO
INSERT dbo.CDC_Test VALUES 
	('MySQL', '1234', GETDATE());
GO

DECLARE 
	@begin_time datetime
,	@end_time datetime
,	@from_lsn binary(10)
,	@to_lsn binary(10)
,	@row_filter_option nvarchar(30);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();

-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT @row_filter_option = N'all';
--SELECT @from_lsn, @to_lsn, @row_filter_option;

--cdc.fn_cdc_get_all_changes_<capture_instance> 형식
--에이..씨발 CTP6 SQL Server가 만들어 준 함수 뻑 난다.. 왜 뻑나지? 이해 할 수 없다..떱..
/*
Msg 313, Level 16, State 3, Line 8
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... 

메시지313, 수준16, 상태3, 줄20
프로시저또는함수cdc.fn_cdc_get_all_changes_ ... 에제공된인수개수가부족합니다.
*/
--SELECT * FROM cdc.fn_cdc_get_all_changes_CDC_Instance_01(@from_lsn, @to_lsn, @row_filter_option);	

SELECT
	[__$start_lsn]
,	[__$seqval]
,	CASE [__$operation] 
		WHEN 1 THEN 'DELETE'
		WHEN 2 THEN 'INSERT'
		WHEN 3 THEN 'UPDATE(Before)'
		WHEN 4 THEN 'UPDATE(After)'
	END [__$operation] 
,	[__$update_mask]
,	UserID
,	PW
,	ModifyDT 
FROM cdc.CDC_Instance_01_CT
WHERE [__$start_lsn] BETWEEN @from_lsn AND @to_lsn;
GO

--마지막lsn을저장하고있다고가정하면..
INSERT dbo.CDC_Test VALUES 
	('PostgreSQL', '1234', GETDATE());
GO

DECLARE 
	@from_lsn binary(10)
,	@to_lsn binary(10);

SET @to_lsn = 0x0000003A00001E8C0001;
SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn);
SET @to_lsn = sys.fn_cdc_get_max_lsn();

SELECT
	[__$start_lsn]
,	[__$seqval]
,	CASE [__$operation] 
		WHEN 1 THEN 'DELETE'
		WHEN 2 THEN 'INSERT'
		WHEN 3 THEN 'UPDATE(Before)'
		WHEN 4 THEN 'UPDATE(After)'
	END [__$operation] 
,	[__$update_mask]
,	UserID
,	PW
,	ModifyDT 
FROM cdc.CDC_Instance_01_CT
WHERE [__$start_lsn] BETWEEN @from_lsn AND @to_lsn;
GO

기본적인 기능

• 사용자 테이블에 적용된 변경 내용을 추적하는 기능
  
• 이전 지정 시간 이후 적용된 변경 내용에 대한 정보를 쿼리하는 기능
  
• 특정 행이 변경되었는지 만을 추적(변경횟수 등은 추적하지 않음)
  
  

Change Data Tracking 동작(이후 CDT)

• 테이블에 대한 변경 내용 추적 설정
  
• INSERT, DELETE, UPDATE에 대한 변경 내용 추적
  
• 내부 변경 내용 추적 테이블에 행 추가.
  
  

버전카운터

• 변경 내용 추적이 설정된 데이터베이스에 존재.
  
• 변경된 각 행에 대응되는 버전 번호 존재
  
• 변경 내용 추적은 트랜잭션 커밋 시점에 하므로 버전카운터를 이용하여 증분 추적
  
  

변경 내용 추적 함수

• CHANGETABLE (CHANGES): 지정된 버전 이후에 발생한 모든 변경 내용에 대한 추적 정보를 테이블에 반환합니다.
  
• CHANGETABLE (VERSION): 지정된 열에 대한 최신 변경 내용 추적 정보를 반환합니다.
  
• CHANGE_TRACKING_MIN_VALID_VERSION(): CHANGETABLE 함수를 사용할 경우 지정된 테이블에서 변경 내용 추적 정보를 가져오는 데 사용할 수 있는 최소 버전을 반환합니다.
  
• CHANGE_TRACKING_CURRENT_VERSION:마지막으로 커밋된 트랜잭션과 연관된 버전을 가져옵니다. 이 버전을 사용하여 다음 번에 CHANGETABLE을 사용하여 변경 내용을 열거할 수 있습니다.
  
• CHANGE_TRACKING_IS_COLUMN_IN_MASK: CHANGETABLE(CHANGES …) 함수에서 반환된 SYS_CHANGE_COLUMNS 값을 해석합니다.
  
• WITH CHANGE_TRACKING_CONTEXT:응용 프로그램이 데이터를 변경할 경우 주관자 ID와 같은 변경 컨텍스트의 사양을 설정합니다.
  
  

증분변경에 대한 쿼리

Change Data Capture vs Change Data Tracking

- CDT는 Primary Key 필요
- CDT는 Snapshot Isolation Level을 사용할 것을 권장

CDT 설정

--1. 데이터베이스 설정
ALTER DATABASE AdventureWorks2008
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);
GO

--2. 테이블 설정
IF OBJECT_ID('dbo.CDC_Test') IS NOT NULL
	DROP TABLE dbo.CDC_Test;
GO

CREATE TABLE dbo.CDT_Test
(
	UserID varchar(20) NOT NULL PRIMARY KEY
,	PW varchar(20)
,	ModifyDT datetime
);

INSERT dbo.CDT_Test VALUES 
	('yasicom', '1234', GETDATE())
,	('sqlserver', '1234', GETDATE())
,	('oracle', '1234', GETDATE());
GO

ALTER TABLE dbo.CDT_Test
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO

예제: Column Tracking

INSERT dbo.CDT_Test VALUES 
	('MySQL', '1234', GETDATE());
GO

UPDATE dbo.CDT_Test
SET 
	PW = 'asdf'
,	ModifyDT = GETDATE()
WHERE UserID = 'yasicom';
GO

DECLARE @sync_ver bigint;
SET @sync_ver = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.CDT_Test'));
SELECT 
	A.UserID
,	A.PW 
,	A.ModifyDT
,	B.SYS_CHANGE_VERSION
,	B.SYS_CHANGE_CREATION_VERSION 
,	B.SYS_CHANGE_OPERATION
,	B.SYS_CHANGE_COLUMNS
,	B.SYS_CHANGE_CONTEXT 
,	B.UserID
FROM dbo.CDT_Test A RIGHT OUTER JOIN CHANGETABLE(CHANGES dbo.CDT_Test, @sync_ver) B
ON A.UserID = B.UserID
GO
--Row Version = 6 이었음.

UPDATE dbo.CDT_Test
SET 
	PW = '1234'
,	ModifyDT = GETDATE()
WHERE UserID = 'yasicom';
GO

--Using Column Tracking
DECLARE 
	@sync_ver bigint = 6
,	@col_id int = COLUMNPROPERTY(OBJECT_ID('dbo.CDT_Test'), 'PW', 'ColumnId');

SELECT 
	A.UserID
,	A.PW 
,	A.ModifyDT
,	B.SYS_CHANGE_VERSION
,	B.SYS_CHANGE_CREATION_VERSION 
,	B.SYS_CHANGE_OPERATION
,	B.SYS_CHANGE_COLUMNS
,	B.SYS_CHANGE_CONTEXT 
,	B.UserID
,	CASE 
		WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(@col_id, B.SYS_CHANGE_COLUMNS) = 1 THEN A.PW 
		ELSE NULL 
	END PW_Changed
,	CHANGE_TRACKING_IS_COLUMN_IN_MASK(@col_id, B.SYS_CHANGE_COLUMNS) ChangeYN
FROM dbo.CDT_Test A RIGHT OUTER JOIN CHANGETABLE(CHANGES dbo.CDT_Test, @sync_ver) B
ON A.UserID = B.UserID
AND B.SYS_CHANGE_OPERATION = 'U'
GO

• Combining CDC and SSIS for Incremental Data Loads
  
• MSSQL Change Data Capture 는 어디에 저장되고 크기는 얼마만큼일까?
  
• Change Tracking - 2008
  
• Field Level Auditing using Change Data Capture - Part 1
  
• Field Level Auditing using Change Data Capture - Part 2
  
• Introduction to Change Data Capture (CDC) in SQL Server 2008
  
• Setting up Change Data Capture in SQL Server 2008
  
• Using Change Tracking feature of SQL Server 2008
  
• Change Tracking in SQL Server 2008
  
• How To Process Change Data Capture (CDC) in SQL Server Integration Services SSIS 2008
  
• Merge & CDC Demo
  
• Tuning the Performance of Change Data Capture in SQL Server 2008
  
• SQL Server 2008’s Change Data Capture – Tracking the Moving Parts
  
• Microsoft SQL Server 2008 - Change Data Capture – Part 1
  
• Microsoft SQL Server 2008 - Change Data Capture – Part 2
  
• Microsoft SQL Server 2008 - Change Data Capture – Part 3
  
• Microsoft SQL Server 2008 - Change Data Capture – Part 4
  
• Tuning the Performance of Change Data Capture in SQL Server 2008
  
• Using SQL Server 2008 Change Data