Contents

1 RTE SQL Server
2 Change Data Capture
3 CDC襯 覃 至 伎
4 CDC
5 Change Data Tracking
6 谿瑚襭


1 RTE SQL Server #

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 蠏碁殊 襷 れ螻 螳 蟆企.

cdc01.jpg

伎 覦螳 企. 讌襷 譴伎 る 蟆. 蟲蟯 覈蟯 蟆曙磯 ろ? .. 譴 蟆 螳伎 蟲覦覯 る 伎 SQL Server 2008 襦 蠍磯レ Change Data Capture & Tracking 襴襯 豢 企瓦.

2 Change Data Capture #

覲蟆 一危 貂′(Change Data Capture, 危 CDC) Insert, Update, Delete 螳 一一 覲蟆暑伎 碁 覲企ゼ 所 襦 蟯螻 朱 蠏 SQL Server 2008 襦 蠍磯レ企.

cdc02.jpg

Source Table 覲蟆曙 殊企蟆 覃 襦蠏誤殊 覲蟆暑 覦. Change Capture Process 企ゼ 螳讌 Change Table ロ蟆 . 企 覲蟆暑 一危一 觜 SQL Server 2008 CDC襯 Functionれ 螻牛, 讌 Change Table 蠏狩讌 螻, 所 蟯螻 企 襦 蠏殊 螳ロ蟆 .

3 CDC襯 覃 至 伎 #

  • 一危 蠍壱襯 碁Μ蟇, ろ, 蠍壱 豢螳 覦 企 覿
  • DML 覦 螳 貉る 伎 豢.
  • CDC襯 螻給 企 覦 覯 覲伎 讀覿 覲蟆 伎 覦
  • 貉る讌 碁 襤壱 .
  • 焔 るる 豕 譴企.
  • 覲蟆 伎 豢 一危磯 朱 襴 .

4 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

5 Change Data Tracking #

蠍磯蓋 蠍磯
  • 企 覲蟆 伎 豢 蠍磯
  • 伎 讌 螳 危 覲蟆 伎 覲企ゼ 貎朱Μ 蠍磯
  • 轟 覲蟆暑讌 襷 豢(覲蟆渚 煙 豢讌 )

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 螳 覲蟆 貉ろ語 れ.

讀覿覲蟆曙 貎朱Μ

cdc03.jpg

Change Data Capture vs Change Data Tracking

cdc04.jpg

- 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

6 谿瑚襭 #