使用CDC捕獲SQLSERVER資料變更

近期根據公司規劃，需要將以前SQLSERVER資料庫部分表中資料增量取到ORACLE資料庫中，決定使用sqlserver2008中新增的CDC變更資料擷取）功能來實現。

具體操作步驟如下：

1. 查看指定資料庫有沒有啟用CDC功能
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'jointown'
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" height="154" src="http://www.bkjia.com/uploads/allimg/131229/1949441A7-0.png" />

2. 在資料庫級啟用CDC功能
EXEC sys.sp_cdc_enable_db
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image002" border="0" alt="clip_image002" height="145" src="http://www.bkjia.com/uploads/allimg/131229/1949442092-1.png" />
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image003" border="0" alt="clip_image003" height="133" src="http://www.bkjia.com/uploads/allimg/131229/194944Kc-2.png" />

3. 建立一個測試表
create table t1 (id int,name varchar(20));

4. 在測試表上啟用CDC
EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo',

@source_name = 't1',

@role_name = NULL,

@capture_instance = NULL,

@supports_net_changes = 1,

@index_name = NULL,

@captured_column_list = NULL,

@filegroup_name = default
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image005" border="0" alt="clip_image005" height="53" src="http://www.bkjia.com/uploads/allimg/131229/19494451Y-3.jpg" />

執行時報錯，需要在表中設定主鍵或唯一鍵

650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image006" border="0" alt="clip_image006" height="130" src="http://www.bkjia.com/uploads/allimg/131229/1949444327-4.png" />
再次啟用成功:
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image007" border="0" alt="clip_image007" height="341" src="http://www.bkjia.com/uploads/allimg/131229/1949441411-5.png" />

5. 提醒：若要CDC能正常工作，除了以上配置外，需要開啟agent服務！

6. 檢查指定表上CDC是否已經啟用
SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 't1' and schema_id = SCHEMA_ID('dbo')
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image009" border="0" alt="clip_image009" height="174" src="http://www.bkjia.com/uploads/allimg/131229/19494433V-6.jpg" />

7. 此時查看變更資料擷取表,根據之前建的表t1,對應的存放變更資料的表為cdc.dbo_t1_ct，表中暫無任何資料
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image010" border="0" alt="clip_image010" height="216" src="http://www.bkjia.com/uploads/allimg/131229/194944J17-7.png" />

8. 在t1表中分別插入與更改一條資料後再查看
insert into t1 values (1,'a');

select * from cdc.dbo_t1_ct
650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image012" border="0" alt="clip_image012" height="157" src="http://img1.51cto.com/attachment/201207/17/459544_13425135264sTg.jpg" />650) this.width=650;" style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image014" border="0" alt="clip_image014" height="170" src="http://www.bkjia.com/uploads/allimg/131229/19494454b-9.jpg" />
在跟蹤表中可以看到源表資料插入已被捕獲，相比原表而言，cdc表中新增了五列，一般我們額外需要關注的主要是__$start_lsn與__$operation 這兩列，前一列存放此操作對應的資料庫lsn號，後一列存放當前操作類型，以下為__$operation列對應操作類型:
1=delete，
2=insert，
3=update(舊值)，
4=update(新值)。

通過以上操作可以看到，配置CDC過程本身來說是比較簡單的，可以很方便的在對應的CDC表中跟蹤到原表所做的資料操作，有了這些跟蹤的資料後相應的開發人員就可以很方便的將sqlserver中源庫和表中的資料擷取後同步到對應的其它庫中，相比原來2005的觸發器的方式，在效率和方便性上有了極大的提高。

本文出自 “天知道的技術部落格” 部落格，請務必保留此出處http://tianzt.blog.51cto.com/459544/933681