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 蠏碁殊 襷 れ螻 螳 蟆企.
伎 覦螳 企. 讌襷 譴伎 る 蟆. 蟲蟯 覈蟯 蟆曙磯 ろ? .. 譴 蟆 螳伎 蟲覦覯 る 伎 SQL Server 2008 襦 蠍磯レ Change Data Capture & Tracking 襴襯 豢 企瓦.
2 Change Data Capture #
覲蟆 一危 貂′(Change Data Capture, 危 CDC) Insert, Update, Delete 螳 一一 覲蟆暑伎 碁 覲企ゼ 所 襦 蟯螻 朱 蠏 SQL Server 2008 襦 蠍磯レ企.
Source Table 覲蟆曙 殊企蟆 覃 襦蠏誤殊 覲蟆暑 覦. Change Capture Process 企ゼ 螳讌 Change Table ロ蟆 . 企 覲蟆暑 一危一 觜 SQL Server 2008 CDC襯 Functionれ 螻牛, 讌 Change Table 蠏狩讌 螻, 所 蟯螻 企 襦 蠏殊 螳ロ蟆 .
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 螳 覲蟆 貉ろ語 れ.
讀覿覲蟆曙 貎朱Μ
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
![[http]](/moniwiki/imgs/http.png)
