理解 SQL Server 中的 CDC

在 SQL Server 中，变更数据捕获 (CDC) 是一项强大的功能，可跟踪对表进行的更改（插入、更新和删除），并提供有关修改的详细信息。CDC 于 SQL Server 2008 推出，在数据仓库、审计、复制和 ETL（提取、转换、加载）场景中特别有用，在这些场景中，维护数据更改的历史记录以供进一步处理至关重要。本文将帮助您理解 SQL Server 中的变更数据捕获 (CDC)。

变更数据捕获 (CDC) 的关键概念

1. 更改表：当在表上启用 CDC 时，SQL Server 会自动创建更改表，这些表存储有关源表修改的信息。每个更改表包含：
   • 已更新记录的修改前和修改后数据（适用于 UPDATE 操作）。
   • 插入的数据（适用于 INSERT 操作）。
   • 已删除记录的数据（适用于 DELETE 操作）。
2. 捕获进程：CDC 机制使用 SQL Server 事务日志异步跟踪更改。它在事务提交时捕获数据，但 CDC 本身不会干扰事务的正常流程。
3. 查询更改的函数：CDC 提供系统函数，例如：
   • cdc.fn_cdc_get_all_changes_<capture_instance>：检索两个日志序列号 (LSN) 之间的所有更改（插入、更新和删除）。
   • cdc.fn_cdc_get_net_changes_<capture_instance>：返回更改的净效果（有助于汇总大量更改）。
4. 保留和清理：更改数据在更改表中保留特定时间（默认为 3 天）。SQL Server 有一个 CDC 清理作业，用于删除旧数据并释放空间。

CDC 的工作原理

1. 启用 CDC:
   要启用数据库上的 CDC，您首先需要使用存储过程 sys.sp_cdc_enable_db 在数据库级别启用它。一旦数据库被启用，就可以使用 sys.sp_cdc_enable_table 存储过程在特定表上启用 CDC。示例：

   -- Enabling CDC on the database
   USE MyDatabase;
   EXEC sys.sp_cdc_enable_db;

   -- Enabling CDC on a specific table
   EXEC sys.sp_cdc_enable_table
       @source_schema = N'dbo',
       @source_name = N'MyTableName',
       @role_name = NULL;

2. 更改表:
   一旦在表上启用了 CDC，SQL Server 就会创建一个相应的更改表。此更改表存储有关该表的历史数据。每次发生更改时，更改表都会填充一条记录，反映旧的和新的数据值以及更改的类型（插入、更新或删除）。
3. 查询 CDC 数据:
   为了访问更改历史记录，SQL Server 提供了系统函数，例如 cdc.fn_cdc_get_all_changes_<capture_instance> 和 cdc.fn_cdc_get_net_changes_<capture_instance>。这些函数允许您查询指定日志序列号 (LSN) 或时间范围内的捕获更改。示例：

   -- Retrieve all changes for a specific table
   SELECT *
   FROM cdc.fn_cdc_get_all_changes_MyTableName
   ( @from_lsn, @to_lsn, 'all');

在此查询中：

• @from_lsn 和 @to_lsn 指定您感兴趣的更改范围。
• 'all' 表示您希望看到所有类型的更改（插入、更新、删除）。

1. 禁用 CDC:
   要停止捕获更改，您可以在表或数据库级别禁用 CDC。这通过使用 sys.sp_cdc_disable_table 和 sys.sp_cdc_disable_db 来完成。示例：

   -- Disable CDC on the table
   EXEC sys.sp_cdc_disable_table
       @source_schema = N'dbo',
       @source_name = N'MyTableName',
       @capture_instance = N'MyTableName';

   -- Disable CDC on the database
   EXEC sys.sp_cdc_disable_db;

使用 CDC 的优势

1. 全面的更改跟踪:
   CDC 捕获每个更改的详细信息，包括修改类型、旧值和新值以及时间戳，从而更容易构建数据审计解决方案。
2. 非侵入性:
   由于 CDC 利用了事务日志，因此对生产系统的性能影响很小。更改是异步记录的，不会干扰正常的数据库操作。
3. 对 ETL 友好:
   CDC 在需要增量数据加载的 ETL 场景中特别有用。您可以轻松识别自上次提取以来已更改的行，并且只处理差异。
4. 时间敏感性分析:
   CDC 提供了基于时间的更改视图，支持对数据随时间演变进行的分析。它可以用于跟踪数据更改趋势和模式。

CDC 的局限性

1. 存储开销:
   虽然 CDC 降低了性能开销，但由于用于存储历史数据的更改表，它确实会带来一些额外的存储要求。
2. 仅限于行级更改:
   CDC 捕获行级更改，但不跟踪架构级别的更改。例如，如果添加或删除了列，CDC 不会记录这些更改。
3. 潜在的数据增长:
   如果管理不当，CDC 更改表可能会迅速增长，占用大量空间。实施定期的清理操作来管理更改表的大小至关重要。
4. CDC 保留期:
   默认情况下，CDC 保留更改 3 天，但可以自定义。然而，如果监控不当，更改可能会在保留期到期后丢失。

结论

SQL Server 中的 CDC 提供了一种强大的机制，可以以最小的性能开销跟踪数据更改，使其成为数据审计、ETL 流程和复制的理想选择。它易于实施，配置要求极低，并提供了一种灵活的方式来管理和跟踪数据库表的更改。但是，仔细监控存储和保留策略对于确保 CDC 功能能够继续高效运行且不过度消耗资源至关重要。

文章 SQL Server 中的 CDC 理解 首次出现在 Vivek Johari 的技术博客。