CDC學習總結(jié)(一)數(shù)據(jù)庫----建立測試數(shù)據(jù)庫ifnotexists(selectnamefromsys.databaseswherename='Test')begincreatedatabaseTestend----啟用變更數(shù)據(jù)捕獲execsys.sp_cdc_enable_dbgo----可以用此方式驗證數(shù)據(jù)庫是否開啟數(shù)據(jù)捕獲(1為開啟)SELECTIS_CDC_ENABLEDFROMSYS.databasesWHEREname='Test'/*Test開啟數(shù)據(jù)捕獲后，你將會在Test-〉安全性-〉架構下面看到新建立了一個CDC架構.同樣在Test會建立一些CDC架構的下的系統(tǒng)表：。cdc.captured_columns返回指定的跟蹤列cdc.change_tables返回啟用CDC的表.使用sys.sp_cdc_help_change_data_capture比直接查詢好.cdc.ddl_history返回每個表再啟用CDC后的DDL變更.可以使用sys.sp_cdc_get_ddl_history代替查詢該表.cdc.index_columns返回啟用CDC的表的相關索引列.同樣用sys.sp_cdc_help_change_data_capure來獲取比較好.cdc.lsn_time_mapping為每個在更改表中存在行的事務返回一行.該表用于在日志序列號(LSN)提交值和提交事務的時間之間建立映射.要避免直接查詢該表，使用sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn函數(shù).*/---檢查SQLServerAgent服務的狀態(tài)，如果未啟動，則啟動它DECLARE@agnt_servicesysname;SET@agnt_service=N'SQLServerAgent';DECLARE@tb_agent_statusTABLE(statevarchar(50));INSERT@tb_agent_statusEXECmaster.sys.xp_servicecontrolN'QUERYSTATE',@agnt_service;IFNOTEXISTS(SELECT*FROM@tb_agent_statusWHEREstate=N'Running.')EXECmaster.sys.xp_servicecontrolN'START',@agnt_service;GO----創(chuàng)建測試表createtableTB_CDC_USER(useridintnotnullprimarykeyidentity(1,1),namevarchar(30)notnull,[address]varchar(50)notnull,ageint)insertintoTB_CDC_USER(name,address,age)values('test1','xxxxx',1)insertintoTB_CDC_USER(name,address)values('test5','xxxxx')----創(chuàng)建一個變更數(shù)據(jù)捕獲實例---所有列execsys.sp_cdc_enable_table@source_schema='dbo',@source_name='TB_CDC_USER',@role_name=null,@capture_instance=null,@supports_net_changes=1,@index_name=null,@captured_column_list=null,@filegroup_name=default,@allow_partition_switch=1/*@source_schema:是源表所屬的架構的名稱.source_schema的數(shù)據(jù)類型為sysname,無默認值，并且不能為NULL.@source_name:是對其啟用變更數(shù)據(jù)捕獲的源表的名稱.source_name的數(shù)據(jù)類型為sysname，無默認值，并且不能為NULL.source_name必須存在于當前數(shù)據(jù)庫中.不能對cdc架構中的表啟用變更數(shù)據(jù)捕獲.@role_name:是用于控制更改數(shù)據(jù)訪問的數(shù)據(jù)庫角色的名稱.role_name為sysname，并且必須指定如果顯式設置為NULL，則沒有控制角色用于限制對更改數(shù)據(jù)的訪問?如果當前存在該角色，則使用它.如果不存在該角色，則會嘗試創(chuàng)建具有指定名稱的數(shù)據(jù)庫角色.在嘗試創(chuàng)建該角色之前，將刪除角色名稱字符串右側(cè)的空格.如果調(diào)用方無權在數(shù)據(jù)庫中創(chuàng)建角色，則存儲過程操作將失敗.@capture_instance:是用于命名特定于實例的變更數(shù)據(jù)捕獲對象的捕獲實例的名稱.capture_instance為sysname，并且不能為NULL?源表最多可以有兩個捕獲實例.@supports_net_changes:指示是否對此捕獲實例啟用凈更改查詢支持.supports_net_changes為bit,如果此表有主鍵，或者有已使用@index_name參數(shù)進行標識的唯一索引，則此參數(shù)的默認值為1.否則，此參數(shù)默認為0.如果為0，則只生成查詢所有更改的支持函數(shù).如果為1，則還會生成查詢凈更改所需的函數(shù).如果將supports_net_changes設置為1,則必須指定index_name,或者源表必須具有已定義的主鍵.@index_name:用于唯一標識源表中的行的唯一索引的名稱.index_name為sysname，并且可以為NULL.如果指定，則index_name必須是源表的唯一有效索引.如果指定index_name，則標識的索引列優(yōu)先于任何定義的主鍵列，就像表的唯一行標識符一樣.@captured_column_list:標識將包括在更改表中的源表列.captured_column_list的數(shù)據(jù)類型為nvarchar(max)，并且可以為NULL?如果為NULL，則所有列都將包括在更改表中.@filegroup_name:這個選項允許指定CDC的數(shù)據(jù)存儲到哪里.filegroup_name為sysname，并且可以NULL.如果指定，則必須為當前數(shù)據(jù)庫定義filegroup_name.如果為NULL，則使用默認文件組.對于大型數(shù)據(jù)集，通過不同的文件組進行分隔會帶來更好的管理型和性能.@allow_partition_switch:指示是否可以對啟用了變更數(shù)據(jù)捕獲的表執(zhí)行ALTERTABLE的SWITCHPARTITION命令.allow_partition_switch為bit,默認值為1.執(zhí)行后會啟動個作業(yè)，每個啟用表CDC的都會啟用以下個作業(yè).如果已經(jīng)存在啟用了CDC的表，則作業(yè)不會被重建cdc.Test_capture捕獲作業(yè)該作業(yè)的職責是使用日志讀取器技術捕獲發(fā)生的變更，并且在SQLSERVER啟動并運行時設定為自動運行.當JOB運行的時候會調(diào)用系統(tǒng)SPsys.sp_MScdc_capture_job，該sp內(nèi)部調(diào)用調(diào)用sys.sp_cdc_scan.如果變更數(shù)據(jù)捕獲日志掃描操作已經(jīng)處于活動狀態(tài)，或數(shù)據(jù)庫啟用了事務復制，則無法顯式執(zhí)行此過程.此存儲過程應當由需要自定義自動配置的捕獲作業(yè)的行為的管理員使用.cdc.Test_cleanup清除作業(yè)默認為每天上午點運行，默認是清除存放天以上的數(shù)據(jù).

調(diào)用系統(tǒng)存儲過程sys.sp_MScdc_cleanup_job執(zhí)行成功之后同樣在Test數(shù)據(jù)庫系統(tǒng)表中會生成表cdc.dbo_TB_CDC_USER_CT,該表包含TB_CDC_USER_CT表上所有的變更.展開表后會發(fā)現(xiàn)新增加了列.__$start_lsn與相應更改的提交事務關聯(lián)的日志序列號（LSN）.__$end_lsn__$seqval 用于對事務內(nèi)的行更改進行排序的序列值.__$operation識與相應更改關聯(lián)的數(shù)據(jù)操作語言（DML）操作.可以是下列值之一：1=刪除;2=插入;3=更新（舊值）列數(shù)據(jù)中具有執(zhí)行更新語句之前的行值.4=更新（新值）列數(shù)據(jù)中具有執(zhí)行更新語句之后的行值.__$update_mask 基于更改表的列序號的位掩碼，用于標識那些發(fā)生更改的列。*/----通過下面的查詢確認這個表已經(jīng)被跟蹤（1則表示已跟蹤）SELECTis_tracked_by_cdcFROMSYS.TABLESWHERENAME='TB_CDC_USER'ANDSCHEMA_ID=SCHEMA_ID('DBO')----可以使用sys?sp_cdc_help_change_data_capture來驗證數(shù)據(jù)捕獲的配置信息EXECsys.sp_cdc_help_change_data_capture'DBO','TB_CDC_USER'----分別向表中插入，刪除，更新記錄后查看cdc.dbo_TB_CDC_USER_CT的變化最初cdc.dbo_TB_CDC_USER_CT的信息口結(jié)果陸曹息IjaHUjpi|fendjin| |3ape<Wgi|Jupdg1mEM-uerrd|winemddz習爍|L向TB_CDC_USER表中插入一條記錄后cdc.dbo_TB_CDC_USER_CT的信息insertintoTB_CDC_USER(name,address)values('test11','xxxxx')向TB_CDC_USER表中刪除一條記錄后cdc.dbo_TB_CDC_USER_CT的信息DELETEFR0MTB_CDC_USERWHEREuserid=8二皓果|匕消息|5?i-;ilsn|jcpef3bon|J)wdBtemBA：.|US^FC|addr?i$jtDDKCsDZNULLPC［虹LEDlEnWOTStre32 ftcCF J5i 三第 'atQBtllCM&CNULL三矍:吧苗￡匸!3004勺噸臥 IJULL莊沮：站的DC000000X1DOWcHecrjoLixxxxx114.向TB_CDC_USER表中更新一條記錄后cdc.dbo_TB_CDC_USER_CT的信息口結(jié)杲|酸有忌IXri；X如鬥日上鬥詮吋訓M|鼻弓*合I?g?1\ZaEOC'ECEDCCrCCCZ7EEE(^NULLScDMC>巧DZDODCCDTSl匸DI-Oxi-F45tMIIXKO3NULLCwOOD&IXDfflXXMAlDDD4NULL氐DD曲DCOOOM0A1DDD21(MF8aheignxuWOOOL113噸［盟IXDOOOOjD10WJ4NULL應DD&M3DCDOTDODD100023ftrDS19ird7XXKKX114NULL&D&MJJXDC0D0CDr7；24IMS19L?7應----幾個比較好的CDC函數(shù)使用某些函數(shù)我們可以根據(jù)日志序列號(Logsequencenumber,LSN)來跟蹤變更數(shù)據(jù)使用函數(shù)sys.fn_cdc_map_time_to_lsn獲取變更范圍內(nèi)的的最大和最小LSN值.關系運算符有:smallestgreaterthan;smallestgreaterthanorequal; largestlessthan;largestlessthanorequal.例如使用sys.fn_cdc_map_time_to_lsn函數(shù)可以查詢某個時間段內(nèi)插入的記錄DECLARE@BGLSNVARBINARY(10)=SYS.fn_cdc_map_time_to_lsn('smallestgreaterthanorequal','2011-05-2719:43:29.990')DECLARE@EDLSNVARBINARY(10)=SYS.fn_cdc_map_time_to_lsn('largestlessthanorequal','2011-05-2719:49:52.930')selectuserid,name,address,agefromcdc.dbo_TB_CDC_USER_CTwhere__$OPERATION=2and__$start_lsnbetween@BGLSNand@EDLSN使用函數(shù)sys.fn_cdc_map_lsn_to_time可以查詢變更時間SELECT__$OPERATION,sys.fn_cdc_map_lsn_to_time(__$start_lsn),USERID,NAME,ADDRESSFROMcdc.dbo_TB_CDC_USER_CT獲取LSN邊界selectsys.fn_cdc_get_max_lsn()--返回數(shù)據(jù)庫級別的最大LSNselectsys.fn_cdc_get_min_lsn('cdc.dbo_TB_CDC_USER_CT')--需要捕獲實例名稱做參數(shù)(二)使用程序捕獲變更數(shù)據(jù)基于SQLdenpendency類的數(shù)據(jù)庫變更自動通知須在MSDB數(shù)據(jù)庫中的QueryNotificationService服務上向Guest用戶授予發(fā)送權限方法如下，注意要區(qū)分大小寫usemsdbgrantsendonservice::[HYPERLIN