SQL Server CDC（变更数据捕获） 实践笔记

SQL Server CDC（变更数据捕获） 实践笔记

引用

CSDN

1.

https://m.blog.csdn.net/yaoyan11ao11an/article/details/145698450

功能

记录数据变更
CDC能够记录SQL Server表的插入、更新和删除活动 =====》事务日志

要求

SQL Server 2008及以上版本的Enterprise Edition、Developer Edition和Evaluation Edition

前置条件

• 需要开启代理服务
• 磁盘要有足够的空间，保存日志文件
• 表必须要有主键或者是唯一索引

原理

生成表结构副本
事务日志中记录的相关操作信息会被CDC代理异步读取并应用到副本表

操作

1. 开启CDC，创建相关系统表和函数

• 保存CDC元数据信息
• 捕获的变更数据
• 查询CDC

-- 启用CDC功能
EXEC sys.sp_cdc_enable_db;
-- 判断当前数据库是否启用了CDC（如果返回1，表示已启用）
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'cdc_test';

• 若启用CDC失败

USE  [DB]
GO
sp_changedbowner sa

2. 选择跟踪表

-- schema_name 是表所属的架构（schema）的名称。
-- table_name 是要启用 CDC 跟踪的表的名称。
-- cdc_role 是 CDC 使用的角色的名称。如果没有指定角色名称，系统将创建一个默认角色。
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name   = 'orders',
  @role_name     = 'cdc_role';

• 执行完成后，自动生成两个作业。tablename_capture & tablename_cleanup

3. 变更数据的接口

查询函数

• 检索日志 cdc.fn_cdc_get_all_changes_
• 查询开启cdc跟踪的表

SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE is_tracked_by_cdc = 1;

• 获取特定表的CDC配置信息

EXEC sys.sp_cdc_help_change_data_capture @source_schema = N'dbo', @source_name = N'table';

• CDC系统表
  cdc.captured_columns
• 禁用表的CDC功能

USE  [db];
GO
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'tabletest',
    @capture_instance = N'dbo_tabletest';
GO

• 对具体字段开启CDC

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'YourTableName',
    @capture_instance = N'YourCaptureInstance',
    @supports_net_changes = 1,
    @captured_column_list = N'Column1, Column2'; -- 只跟踪Column1和Column2

• 获取数据库的cdc配置信息

EXEC sys.sp_cdc_help_change_data_capture

4. 获取最大日志序列号（LSN）

select sys.fn_cdc_get_max_lsn()

5. 两个作业

• 捕获作业： cdc.dbCC_capture
  捕获变更数据

• 清理作业：cdc.dbCC_cleanup
  清理旧的变更数据

性能

影响较小
扩展性强

注意事项

1. CDC不支持字段类型：image、text、ntext。
2. 配置Mapping时，Timestamp、identity 这种数据库系统字段不支持手动操作，需在配置中移除。
3. 配置Mapping时，表必须选择主键primary key。
4. 存在外键等依赖关系的表，需注意其同步顺序。
5. 启用CDC后，若cdc表发生了字段类型变化，或新增/删除字段，则会影响日志，需关闭后重新启用cdc。
6. 当cdc表并非所有字段都启用cdc时，则配置Mapping要注意其字段需与captured_column_list中保持一致。

Keys

1. 事务日志文件（.ldf）
2. 逻辑日志（VLF）
3. CDC作业
4. CDC表
5. LSN（Log Sequence Number）
6. cdc.lsn_time_mapping
7. cdc.ddl_history

附

CDC 要点简图

CDC接口

CDC & 发布订阅