基础知识天善智能一家之言

1、CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧目录11.天善 CDC 增量更新1CDC 主要的作用2CDC 变更数据捕获基本知识5CDC 变更数据捕获案例详解10关于 CDC 功能的答疑19追踪每笔业务操作数据改变的利器SQLCDC20使用更改跟踪实现数据同步22初探 SQL Server 2008 中的更改跟踪与变更数据捕获44使用 SQL SERVER 2008 数据变更捕获(CDC )69变更数据捕获103一个完整版的 CDC 案例使用11011.天善 CDC 增量更新sql server c

2、dc 变更数据捕获 无法启用如下第一次尝试微软 sql server 2008 的新功能。sys.sp_cdc_enable_db对当前数据库启用变更数据捕获。必须先对数据库执行此过程，然后才能对该数据库中的任何表启用变更数据捕获。变更数据捕获可应用到所启用的表中的、更新和删除活动，同时采用易于使用的关系格式提供变更详细信息。此操作将为已修改的行捕获反映了所跟踪源表列结构的列信息，同时还捕获将更改应用到目标环境所需的元数据。变更数据捕获仅在 SQL Server 2008 Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用

3、。运行如下exec sys.sp_cdc_enable_dbgo报错消息 22902，级别 16，状态 1，过程 sp_cdc_enable_db，第 19 行调用方无权启动所请求的操作。需要 Sysadmin。解决方法：加上改变用户权限的语句EXEC sp_changedbowner 'sa'goexec sys.sp_cdc_enable_dbgo是 sql server 2008 运行过程时，会将权限降级。CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧sql server cd

4、c 变更数据捕获 主键设置默认第一个是主键值CDC 主要的作用SQLSERVER2008 CDC(Change Data Capture)其主要是通过对事务日志的异步，DML操作的发生时间、类型和实际影响的数据变化，然后将这些数据到启用CDC时自动创建的表中。通过SQL由于数据变化是异步化Server 2008 CDC相过程，可以获取详细的数据变化情况。的，因此对整体性能的影响不大，远小于通过Trigger实现的数据变/*CDC功能功能说明:SQLSERVER2008 CDC(Change Data Capture)其主要是通过对事务日志的异步，DML操作的发生时间、类型和实际影响的数据变化，

5、然后将这些数据到启用CDC时自动创建的表中。通过SQL Server 2008 CDC相过程，可以获取详细的数据变化情况。由于数据变化是异步远小于通过Trigger实现的数据变化的，因此对整体性能的影响不大，*/USE AdventureWorksDWGO/*功能说明:启用数据库对 CDC 的支持*/EXEC sp_cdc_enable_db GO/*功能说明:启用某个表对 CDC 的支持 GO这里的 supports_net_changes 指的是是否支持所谓的净更改，即过滤掉重复的*/EXECsys.sp_cdc_enable_table'dbo','FactInt

6、ernetSales',role_name=null,supports_net_changes=0;GO/*CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧功能说明：查看那些表启用了 CDC 功能*/SELECT name,is_tracked_by_cdcFROM sys.tables WHERE name LIKE ('fact%'); GO/*功能说明:CDC 功能的验证*/INSERT INTO FactInternetSalesSELECT 484,1127,1139

7、,1134,18759,1,100,6,'SO75124',1,1,1,21.9800, 21.9800,0,0,8.2205,8.2205,21.9800,1.7584,0.5495,NULL,NULLUNION ALLSELECT 486,1127,1139,1134,18759,1,100,6,'SO75125',1,1,1,21.9800, 21.9800,0,0,8.2205,8.2205,21.9800,1.7584,0.5495,NULL,NULL;UPDATE FactInternetSales SET PromotionKey=2 WHERE

8、SalesOrderNumber = 'SO75124'DELETE FROM FactInternetSales WHERE SalesOrderNumber='SO75125'GO/*功能说明：这个表其实是在系统表里面参数说明: 这里将看到条结果，其中 operation 为和是 update 操作的3 表示旧值，4 表示新值2 表示新增1 表示删除，*/SELECT * FROM cdc.dbo_FactInternetSales_CT;/*功能说明：变更历史*/DECLARE begin_time datetime, end_time datetime,

9、 from_lsn binary(10), to_lsn binary(10);SET begin_time=GETDATE()-1;SET end_time=GETDATE();SELECTfrom_lsn=sys.fn_cdc_map_time_to_lsn('smallestgreaterthanorequal',begin_time);SELECT to_lsn=SYS.fn_cdc_map_time_to_lsn('largestless PRINT begin_time;thanorequal',end_time);CDC 使用手册天善智能专注 BI

10、 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧PRINT end_time;PRINT from_lsn; PRINT to_lsn; GO/*功能说明:创建一个*/过程，根据开始日期和结束日期变更CREATE PROCEDURE GetCDCResult(begin_time datetime, end_time datetime) ASDECLARE from_lsn binary(10),to_lsn binary(10);SELECTfrom_lsn=sys.fn_cdc_map_time_to_lsn('small

11、estgreaterthanorequal',begin_time);SELECT to_lsn=SYS.fn_cdc_map_time_to_lsn('largestlessthanorequal',end_time);SELECT * FROM cdc.dbo_FactInternetSales_CT WHERE $start_lsn between from_lsn ANDto_lsn; GOEXEC GetCDCResult '2009-4-27','2011-10-29'GO/*功能说明：撤销 CDC 功能*/EXEC sys.

12、sp_cdc_disable_table 'dbo','FactInternetSales','All' GOEXEC sys.sp_cdc_disable_dbGO;/*功能说明：重点1. 其实，它是有一个程读到，那么此时的进程的。它是异步地日志文件。如果某部分更改没有被进日志截断也是没有效果的，很显然需要这样来保证。2. net_changes 是什么意思呢？说的是一条为准。一行，如果有多个更改的话，那么以最后的CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧

13、加入我们吧3. 这个更改是不是会永远保存？数创建的。的，它会定期清除的捕获和清除作业都是使用默认参将立即启动捕获作业。它连续运行，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天（分钟），可使用单个删除语句最多删除 5000 项。4. 如果启用了之后，修改了表的结构，会怎么样？为适应固定列结构更改表，在为源表启用变更数据捕获后，负责填充更改表的捕获进程将忽略未指定进行捕获的任何新列。如果删除了某个跟踪的列，则会为在后续更改Null 值。但为该列提供是，如果现有列更改了其数据类型，则会将更改致跟踪的列到更改表，以确

14、保捕获机制没有导发生数据丢失。捕获进程还会将检测的跟踪表列结构的任何更改到 cdc.ddl_history表。如果使用者希望得到下游应 用程序中可能需 要进行的调整 ，请使 用过程。sys.sp_cdc_get_ddl_historyCDC 变更数据捕获基本知识此技术的数据使用者的一个典型示例是提取、转换和加载(ETL)应用程序。ETL 应用程序以增量方式将SQL Server源表中的更改数据加载到数据仓库或数据市场。虽然数据仓库中的源表的表示形式必须反映源表中的更改，但刷新源副本的端到端技术并不适用。相反，您需要一种具有特定结构的可靠更改数据流，以便使用者可以将其应用于不同的目标数据表示形式

15、。SQL Server变更数据捕获就提供了这一技术。变更数据捕获仅在SQL Server EnterpriseEdition、DeveloperEditionEvaluation Edition和中可用。变更数据捕获数据流下图说明了变更数据捕获的主体数据流。CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧变更数据捕获的更改数据源为 SQL Server 事务日志。在将、更新和删除应用于跟踪的源表时，将会在日志中添加说明这些更改的项。日志用作变更数据捕获进程的输入来源。它会日志，并在跟踪的表的关联更改表

16、中添加有关更改的信息。将提供一些函数，以枚举在更改表中指定范围内发生的更改，并以筛选的结果集的形式返回该值。通常，应用程序进程使用筛选的结果集在某种外部环境中更新源表示形式。了解变更数据捕获和捕获实例在跟踪对数据库中任何单个表进行的更改之前，必须为数据库显式启用变更数据捕获。这是使用 sys.sp_cdc_enable_db sys.sp_cdc_enable_table将创建一个关联的捕获实例以支持过程完成的。为数据库启用变更数据捕获后，可以使用过程将源表标识为跟踪的表。为表启用变更数据捕获后，源表中的更改数据。捕获实例由一个更改表和最多两个查询函数组成。说明捕获实例配置详细信息的元数据保留

17、在变更数据捕获元数据表cdc.change_tables、cdc.index_columns 和 cdc.captured_columns 中。可以使用sys.sp_cdc_help_change_data_capture过程来检索此信息。与捕获实例关联的所有对象都是在启用变更数据捕获的数据库的变更数据捕获架构中创建的。捕获实例名称的要求是：必须是有效的对象名，并且在数据库捕获实例中是唯一的。默认情况下，此名称为源表的 <架构名_表名>。它的关联更改表名：在捕获实例名称后面追加_CT。用于所有更改的函数名：在捕获实例名称后面追加fn_cdc_get_all_changes_。如果将

18、捕获实例配置为支持 net changes，则还会创建函数，并通过在捕获实例名称后面追加 fn_cdc_get_net_changes_ 来net_changes进行命名。CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧更改表变更数据捕获更改表的前五列是元数据列。这些列提供与的更改有附加信息。其余列镜像源表中按名称标识的捕获列（通常还会按类型进行标识）。这些列保存从源表中收集的捕获列数据。应用于源表的每个或删除操作在更改表中各占一行。操作生成的行的数据列包含插入后的列值。删除操作生成的行的数据列包含删

19、除前的列值。更新操作需要两行数据：一行用于标识更新前的列值，另一行用于标识更新后的列值。更改表中的每一行还包含其他元数据，用于解释更改操作的情况。 $start_lsn 列标识为更改指定的提交日志序列号 (LSN)。提交 LSN 不仅标识在同一事务中提交的更改，而且还对这些事务进行排序。可以使用 $seqval 列对同一事务中进行的其他更改进行排序。 $operation 列与更改关联的操作：1 = 删除，2 =，3 = 更新（前像），4 = 更新（后像）。 $update_mask 列是一个可变的位掩码，每个捕获列一个对应的定义位。对于和删除项，更新掩码始终设定所有位。但是，更新行仅设定与更

20、改列对应的那些位。数据库的变更数据捕获有效性间隔数据库的变更数据捕获有效性间隔是指更改数据可供捕获实例使用的时段。有效性间隔从为数据库表创建第一个捕获实例时开始，并一直持续到当前时间。如果没有定期系统地清除数据，更改表中的数据将会变得非常大。变更数据捕获清除进程负责实施基于保持期的清除策略。首先，它移动有效性间隔的低端点以满足时间限制。然后，它删除过期的更改表项。默认情况下，数据保持期为三天。在高端，当捕获进程提交每批新的更改数据时，将在 cdc.lsn_time_mapping 中为每个具有更改表项的事务添加新的项。在表中，将保留提交日志序列号 (LSN) 和事务提交时间（分别为 start

21、_lsn 和 tran_end_time 列）。位于 cdc.lsn_time_mapping 中的最大 LSN 值表示数据库有效性窗口的高水印。其相应提交时间将作为基于保持期的清除操作计算新的低水印的基础。由于捕获进程从事务日志中提取更改数据，因此，提交更改的时间与更改出现在其关联更改表中的时间之间内在的延迟。虽然这种延迟通常很小，但务必记住，在捕获进程处理相关日志项之前无法使用更改数据。捕获实例的变更数据捕获有效性间隔虽然数据库有效性间隔和各个捕获实例的有效性间隔通常是一致的，但并非始终是这种情况。捕获实例的有效性间隔从捕获进程识别捕获实例并开始将关联更改到其更改表时开始。因此，如果捕获实

22、例是在不同时间创建的，则每个实例最初具有不同的低端点。sys.sp_cdc_help_change_data_capture 返回的结果集中的 start_lsn 列显示每个定义的捕获实例的当前低端点。当清除进程清除更改表项时，它将调整所有捕获实例的start_lsn 值，以反映可用更改数据的新低水印。仅调整那些 start_lsn 值当前低于新的低水印的捕获实例。随着时间的推移，如果没有创建新的捕获实例，所有单个实例的有效性间隔CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧将逐渐与数据库有效性间隔

23、保持一致。有效性间隔对更改数据使用者，因为捕获实例的当前变更数据捕获有效性间隔必须完全涵盖请求的提取间隔。如果提取间隔的低端点位于有效性间隔低端点左侧，则可能会由于过早清除而丢失更改数据。如果提取间隔的高端点位于有效性间隔高端点右侧，则捕获进程没有全部处理提取间隔所表示的时段，也可能会丢失更改数据。sys.fn_cdc_get_min_lsn 函数为捕获实例检索当前的最小 LSN，而sys.fn_cdc_get_max_lsn 用于检索当前的最大 LSN 值。当更改数据时，如果指定的LSN 范围不在这两个 LSN 值之间，变更数据捕获函数将会失败。处理对源表的更改对于下游使用者来说，适应所跟踪

24、的源表中的列更改是一个难题。虽然对源表启用变更数据捕获并防止发生此类 DDL 更改，但变更数据捕获将通过 API 返回的传递结果集保持不变（甚至在基础源表的列结果发生变化时也是如此），从而有助于减轻对使用者造成的影响。在定义函数的基础更改表中，也会反映这种固定的列结构。为适应固定列结构更改表，在为源表启用变更数据捕获后，负责填充更改表的捕获进程将忽略未指定进行捕获的任何新列。如果删除了某个跟踪的列，则会为在后续更改为该列提供Null 值。但是，如果现有列更改了其数据类型，则会将更改到更改表，以确保捕获机制没有导致跟踪的列发生数据丢失。捕获进程还会将检测的跟踪表列结构的任何更改cdc.ddl_h

25、istory 表。如果使用者希望得到下游应用程序中可能需要进行的调整到，请使用 sys.sp_cdc_get_ddl_history过程。通常，在将 DDL 更改应用于其关联源表时，当前捕获实例将继续保持其结构。不过，可以为表创建第二个捕获实例以反映新的列结构。这样，捕获进程就可以将对相同源表所做的更改发送到两个不同的更改表，这两个更改表具有不同的列结构。因此，一个更改表可以继续为当前运行的程序提供数据，而第二个更改表可以驱动开发环境以尝试加入新的列数据。捕获机制依次填充两个更改表意味着，可以从一个表转换到另一个表，而造成更改数据丢失。只要两个变更数据捕获时间线重叠，就可能会发生这种转换。当转

26、换生效时，可能会删除过时的捕获实例。注意可同时与单个源表相关联的最大捕获实例数为两个。捕获作业与事务日志器之间的关系变更数据捕获进程逻辑嵌入在过程 sp_replcmds 中，后者是作为 sqlservr.exe一部分生成的内部服务器函数，事务也会使用它从事务日志中收集更改。如果仅为数据库启用了变更数据捕获，您可以将变更数据捕获 SQL Server捕获作业作为调用sp_replcmds 的载体进行创建。如果还启用了，则会单独使用事务日志器来满足这两个使用者的更改数据需求。如果为相同数据库同时启用了大减少日志争用。和变更数据捕获，这种策略可大只要启用了变更数据捕获的数据库的状态发生变化，就会自

27、动在这两种运行模式之间进CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧行切换以捕获更改数据。重要提示两个捕获逻辑实例均需要运行 SQL Server才能执行相应的进程。变更数据捕获进程的主要任务是，扫描日志并将列数据以及与事务有信息写入变更数据捕获更改表中。若要确保它填充的所有变更数据捕获更改表具有一致的事务界限，捕获进程将在每个扫描周期内打开并提交其的事务。它检测何表新启用了变更数据捕获，并自动将这些表加入到当前在日志中监视更改项的表集中。类似地，还会检测禁用的变更数据捕获，从而导致将源表从当前监

28、视更改数据的表集中删除。在处理完日志的某个部分后，捕获进程将通知服务器日志截断逻辑，后者使用此信息来确定适合截断的日志项。注意在对数据库启用变更数据捕获时，即使恢复模式设置为简单恢复，日志截断点也向前推进，直到为捕获标记的所有更改改，执行 CHECKPOINT 将捕获进程收集为止。如果捕获进程未运行且有要收集的更截断日志。还可以使用捕获进程保留对跟踪的表进行的 DDL 更改的历史。只要删除了启用变更数据捕获的数据库或表，或者添加、修改或删除了启用变更数据捕获的表中的列，与变更数据捕获关联的 DDL 语句就会在数据库事务日志中输入内容。捕获进程将处理这些日志项，然后将关联的 DDL到 cdc.d

29、dl_history 表。可使用 sys.sp_cdc_get_ddl_history 存储过程来获取与影响所跟踪的表的 DDL的相关信息。变更数据捕获作业通常有两个 SQL Server作业与启用了变更数据捕获的数据库相关联：一个作业用于填充数据库更改表，另一个作业负责清除更改表。两个作业都包含一个运行 Transact-SQL 命令的步骤。调用的 Transact-SQL 命令是变更数据捕获定义的过程，用于实现作业逻辑。为数据库中的第一个表启用变更数据捕获时，将会创建这些作业。将始终创建清除作业。仅当没有为数据库定义事务发布时，才会创建捕获作业。如果为数据库同时启用了变更数据捕获和事务，并

30、删除了事务日志器作业，则也会创建捕获作业，因为数据库不再具有定义的发布。捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天（4320 分钟），可使用单个删除语句最多删除 5000 项。为数据库禁用变更数据捕获时，将会删除变更数据捕获作业。如果同时启用了变更数据捕获和事务，则在数据库中添加第一个发布时，也可能会删除捕获作业。在内部，变更数据捕获sys.sp_cdc_drop_job作业是分别使用 sys.sp_cdc_add_job 和过程创建和删

31、除的。也会公开这些过程，以使管理员能够控CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧制这些作业的创建和删除过程。管理员对变更数据捕获sys.sp_cdc_change_job sys.sp_cdc_help_jobs作业的默认配置没有显式的权。提供过程旨在过程还修改默认配置参数。此外，查看当前的配置参数。在启动时，捕获作业和清除作业均会从 msdb.dbo.cdc_jobs 表中提取配置参数。在停止并重新启动作业后，使用sys.sp_cdc_change_job 对这些值所做的任何更改才会生效。此

32、外，还提供了两个额外的过程以启动和停止变更数据捕获作业：sys.sp_cdc_start_job和 sys.sp_cdc_stop_job。注意启动和停止捕获作业并在更改表中。若要在造成更改数据丢失。它仅防止捕获进程主动扫描日志，以将更改项需求时段扫描日志以免增加负载，一个合理的策略是停止捕获作业并在需求减少时重新启动。两个 SQL Server作业从设计上都具有足够高的灵活性和可配置性，可以满足变更数据捕获环境的基本需求。不过，在这两种情况下，已公开了提供功能的基础可以进行进一步的自定义。过程，因而CDC 变更数据捕获案例详解此文章基于SQL Server 2008 CTP3 (Jun 04

33、, 2007)。change data capture是在每次对数据库执行insert、update、delete操作的时候，捕获变更数据的方法，这在数据仓库中是常用到的技术。Oracle早在9i中就加入了这一特性，在多年后的今天，SQL Server总算是提供了同样的功能。CDC的原理是每次对源表（Source Tables）执行insert、update、delete时，数据库事务日志会DML造成的变更数据，然后捕获处理过程将日志中源表的变更数据写入变更捕获表（Change Tables），最后ETL工具使用CDC函数将变更数据抽取到数据仓库。CDC 使用手册天善智能专注 BI 商业智能和

34、数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧相比起在源表建立促发器，CDC对源表事务性能影响小，而且可以获取变更元数据。这里示范一个CDC过程第一步启用数据库的SQL Server Agent。第二步建立一个数据库Database for CDC。第三步然后以服务sysadmin的用户登录，执行下面令：USE Database for CDC;GOEXECUTE sys.sp_cdc_enable_db_change_data_capture;GO执行命令后即启用了数据库的CDC特性。启用CDC特性后系统会自动建立名为CDC的构架和用户，并建

35、立了几个用于CDC的数据表。CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧第四步 建立源表Source_Table，且将column_1列作为唯一索引IX_Source_TableCDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧第五步 执行命令：EXEC sys.sp_cdc_enable_table_change_data_capturesource_schema = 'dbo',sour

36、ce_name = 'Source_Table',role_name = 'change_table_user1',index_name = 'IX_Source_Table',capture_instance = 'ST_Instance',supports_net_changes = 1,captured_column_list = 'column_key, column_1,column_2',filegroup_name = 'PRIMARY'这个命令指定捕获源表Source_Table的变

37、化，参数说明如下：参数说明source_schema源表的构架名source_name源表名CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧为源表Source_Table建立捕获实例ST_Instance 后，系统自动建立了变更捕获表ST_Instance_CT，变更捕获表名规则是实例名+CT。对源表执行 DML 命令时，每行受到 DML 影响的数据都会在变更捕获表中保存数据，不同的 DML命令在变更捕获表中保存的数据不同。命令类型每行数据在变更捕获表的保存情况insert保存行的数据delete保存

38、删除行的数据update保存两行数据，一行是更新前的，一行是更新后的role_name获取变化数据的数据库，如果不，sp_cdc_enable_table_change_data_capture 过程执行后会创建角色index_name唯一索引名，不是必须的，如果源表没有主键可以指定唯一索引以确定哪一行数据是变更数据capture_instance捕获实例，一个源表最多可以有两个实例supports_net_changes是否捕捉净变化数据，如果是的话，源表必须有主键或者指定唯一标识列captured_column_list需要在变更捕获表中保存的列filegroup_name变更捕获表使用的

39、文件组CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧可以看到，变更捕获表中除了我们需要保存源表的三个列外，还多出了 5 个以”$”开头的列，用于元数据。可以先试着执行下面令，以检验CDC 的效果：insert dbo.source_tablevalues ('ck_1','c1_1','c2_1','c3_1');insert dbo.source_tablevalues ('ck_2','c1_2',&

40、#39;c2_2','c3_2');insert dbo.source_tablevalues ('ck_3','c1_3','c2_3','c3_3');列名说明$start_lsn日志中序列号，在对源表执行DML 时，每个事务有一个日志序列号$end_lsn空，不使用$seqval在一个事务中，DML 影响行的序列号$operation识别执行的是何种DML，1=delete，2=insert，3=update（更新前），4=update（更新后）$update_mask用 2 进制标识哪一列发生变更C

41、DC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧update dbo.source_tableset column_2='c2_2_c'where column_key='ck_2'update dbo.source_tableset column_key='ck_3_c'where column_key='ck_3'delete dbo.source_tablewhere column_key='ck_3_c'select

42、 * from cdc.st_instance_ct;将cdc.st_instance_ct 表中数据列出来后如下： $end $oper $update column colum colum $start_lsn $seqval_lsnation_mask_keyn_1n_20x0000002D0000101A1002C0x0000002D0000NULL20x07ck_1c1_1c2_101A100120x0000002D0000201A900040x0000002D0000NULL20x07ck_2c1_2c2_201A900020x0000002D0000301AB00040x0000

43、002D0000c1_3c2_3NULL20x07ck_301AB00020x0000002E00004007E00220x0000002E0000c1_2c2_2NULL30x04ck_2007E00200x0000002E00005007E00220x0000002E0000c1_2c2_2_cNULL40x04ck_2007E0020CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧0x0000002E00006008300070x0000002E0000c1_3c2_3NULL30x01ck_3

44、008300020x0000002E00007008300070x0000002E0000ck_3_cc1_3c2_3NULL40x01008300020x0000002E00008008A00060x0000002E0000ck_3_cc1_3c2_3NULL10x07008A0004其中$update_mask 是受影响的列，对insert 和delete 来说，影响全部的列，所以值是111（十进制的 7）；对 column_key 更新时，值是 001（十进制 1）；对 column_2 更新时，值是 100（十进制 4）。变更数据变更捕获表是那些CDC 实例名+CT 的表，表中了源表的

45、数据变更历史。但通常要按照一定的要求这些变更。在数据库的CDC 构架中，除了变更捕获表外，还可看到有 5 个在数据库启用CDC 时建立的表：除了关联这些CDC 表外，SQL Server 2K8 里还有多个CDC 函数和储存过程，用于变更数据。1 日志序列号与事务时间的变换表名作用captured_columns所有CDC 实例要保存的列change_tables所有的CDC 实例ddl_history所有源表由 DDL 产生的变更index_columnsCDC 实例使用的唯一索引lsn_time_mapping日志序列号的时间，每个DML 事务一个日志序列号CDC 使用手册天善智能专注 B

46、I 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧在change tables 中没有事务发生的时间，只了事务的日志序列号（lsn），而日志序号号对应的时间在lsn_time_mapping 表中。sys.fn_cdc_map_lsn_to_time 和sys.fn_cdc_map_time_to_lsn 是两个用于转换日志序列号与事务时间的函数；sys.fn_cdc_map_time_to_lsn 用于获取某一时间段内的所有日志序列号。2 最小和最大日志序列号sys.fn_cdc_get_min_lsn 和 sys.fn_cdc_g

47、et_max_lsn 函数获得目前的最大和最小日志序列号。3变更数据cdc.fn_cdc_get_all_changes_<capture_instance>函数用于实例中满足要求的所有变更。cdc.fn_cdc_get_net_changes_<capture_instance>函数用于实例中满足，所谓的净变更既是最后一次DML 操作后源表的要求的净变更，比如在对一行数据进行了多次update 后，使用cdc.fn_cdc_get_all_changes_<capture_instance>会返回所有更新前和更新后的数据，而净变更只返回最后一次更新后的。4

48、 获取变更列在对源表进行update 操作后，有时需要知道更新的是哪一列。在变更捕获表中 $update_mask 字段保存变更列的 2 进制编码。sys.fn_cdc_is_bit_set 用于返回列序的二进制值，比如要知道第3 列是否变更，使用sys.fn_cdc_is_bit_set( 3, $update_mask )，若返回 1，则表明第 3 列变更，返回 0，则表明没有变更。另外要知道实例中的列是第几列，可使用sys.fn_cdc_get_column_ordinal 函数。5 获取源表 DDL 变更历史sys.sp_cdc_get_ddl_history 函数用于对源表使用数据定

49、义语句的历史，通常在用 DDL改变源表时，也要使用同样的 DLL 改变变更捕获表。比如删除源表中某一列，或者将某一列的值类型由int 改成long，那么变更捕获表也要跟着变化。变更数据的CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧变更捕获表中数据要周期性的加载到数据仓库中，被加载后的数据就要掉，否则用于 cdc的数据会越来越多。使用 sys.sp_cdc_cleanup_change_table过程清除变更数据。此外，在启用数据库CDC 时，系统自动在SQL Server Agent 中加入清除变

50、更数据的作业。停用 CDCsys.sp_cdc_disable_table_change_data_capture过程用于停用CDC 实例。sys.sp_cdc_disable_db_change_data_capture过程用于停用数据库CDC 功能。小结CDC 是在SQL Server 2K8 CTP3 中才加入特性，在前两个CTP 中还没有出现过，可以看到还有些不尽人意，没有 Oracle 那么完善，希望在正式版中能进一步改进。关于 CDC 功能的答疑昨天在讲解SQL Server BI新特性的时候，提到了SQL Server 2008数据库引擎的一个增强，就是CDC(Change Da

51、ta Capture)：变更数据捕获。看起来大家对这个功能都挺感，不少朋友提问。我整理一下有关键点1. CDC的目的是什么？CDC就是极大地方便了我们获取某个表数据更新情况的一个机制。它通过一个的进程，异步日志文件，而不是触发器的方式工作。而且它的数据是会持久化保存到一个系统表的。2. CDC是不是SQL Server 2008特有的功能，对别的数据库或者早期版本是否起作用？CDC是SQL Server 2008特有的功能，而且是企业版特有的功能。开发版也有该功能，但仅用于测试场合。3. CDC日志，那么如何日志被截断了会怎么样？如果某部分日志，CDC的进程还没有，那么在截断日志时就会忽略这个

52、部分。捕获进程是一个的，它随着服务启动而启动。两次扫描之间间隔5分钟。CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？那么赶紧加入我们吧4. 系统表中的数据是否会？，它会被保留3天。会有一个的作业，每天晚上2点进行扫描。最后，补充一点的是，CDC功能依赖Agent服务，因为它有两个操作都是通过作业来启动的。追踪每笔业务操作数据改变的利器SQLCDC对于大部分企业应用来用，有一个基本的功能必不可少，那就是Audit Trail或者AuditLog，中文翻译为追踪检查、审核检查或者审核。我们采用Audit Trail每一笔

53、业务操作的基本信息，比如操作的基本描述、操作时间、操作者等。对于一些安全级别比较高的应用，或者操作一些比较敏感的数据，我们甚至需要该笔业务操作引起的数据的改变。具体来说，这里的“数据改变”指的是每一条影响的在操作执行前后的变化。对于添加的，需要记录下新的；对于删除的，需要下原来的；对于更新的，则需要同时记录下更新前后的。说到这里，很多人都会想到采用触发器的方式来实现对数据改变的捕捉。但是这种实现方案具有一个最大的局限：由于触发器是在数据操作所在事务范围内执行的，所有会带来性能的问题，严重的话还会因为触发器的执行导致事务超市。所以在这里，我们SQLCDC。一种更好的解决方案：目录一、SQLCDC

54、简介二、在数据库级别开启CDC 三、为某个数据表开启CDC四、五、六、添加的数据改变删除数据的数据改变更新的数据改变一、SQLCDC简介CDC的全名为Change Data Capture，顾名思义，就是用于追踪和捕捉数据改变。CDC是在SQL Server 2008中才出现的新特性，而这个特性则在很早之前就出现在了Oracle中。对于SQL Server之前版本来说，在没有CDC的情况下，如果需要基于某个数据表的数据改变，我们只能采用触发器，具体来说就是通过手工创建After Insert、After Update和AfterDelete触发器去变化的数据。而CDC给了我们一种更为方便、易用和省心的方式去某个数据表的历史操作。二、在数据库级别开启CDC在默认的情况下，数据库的CDC特性是被关闭的，你可以通过系统表sys.databases的CDC 使用手册天善智能专注 BI 商业智能和数据库性能优化想学习 BI 商业智能？想掌握真正的数据库技术?想薪水尽快翻番？