深入浅出 SQL Server CDC 数据同步

ClouGence

专业数据同步、迁移、管理工具开发商

简介

SQL Server 是一款老牌关系型数据库,自 1988 年由 Microsoft、Sybase 和 Ashton-Tate 三家公司共同推出，不断迭代更新至今，拥有相当广泛的用户群体。

如今，我们提到 SQL Server 通常指 Microsoft SQL Server 2000 之后的版本。

SQL Server 2008 是一个里程碑版本，加入了大量新特性,包括 新的语法 、 更丰富的类型 以及本文所提及的 CDC 能力 ，这个能力让数据从 SQL Server 实时同步到外部更加方便。

本文将介绍 CloudCanal 在新版本中对于 SQL Server 数据同步更进一步的优化和实践。

SQL Server CDC 长什么样？

原始日志

常见的数据库往往存在以下两种日志

redo 日志

记录数据的正向变更，简单来说，事务的 commit 通常先记录在这个文件，再返回应用程序成功，可确保数据 持久性

undo 日志

用于保证事务的 原子性 ，如执行 rollback 命令即反向执行 undo 日志中内容以达成数据回滚

一条 DML 语句写入数据库流程如下

大部分关系型数据库中，一个或多个变更会被隐式或显式包装成一个事务
事务开始，数据库引擎定位到数据行所在的 文件位置 并根据已有的数据生成 前镜像 和 后镜像
后镜像 数据记录到 redo 日志中， 前镜像 数据记录到 undo 日志中
事务提交后，日志提交位点（检查点）向前推进，已提交的日志内容即可能被覆盖或者释放

SQL Server redo/undo 日志采用了 ldf 格式 ,文件循环使用。

ldf 日志文件由多个 VLF(逻辑日志) 组合在一起，这些 VLF 首尾相连形成完整的数据库日志记录
ldf 在逻辑日志末端到达物理日志文件末端时，新的日志记录将回到物理日志文件开始，复写旧的数据

ldf 文件即 CDC 所分析的增量日志文件。

启用 CDC

在数据库上执行 exec [console].sys.sp_cdc_enable_db 命令为 console 数据库启用 CDC 功能，这个语句实际上会创建两个作业: cdc.console_capture , cdc.console_cleanup

使用 exec sp_cdc_help_jobs 命令可查看这两个作业详细信息。

cdc.console_capture

负责分析 ldf 日志 并解析 console 数据库事件,再将其写入到 CDC 表中
间隔 5 秒钟执行一次扫描，每次扫描 10 轮，每轮扫描最多 500 个事务

cdc.console_cleanup

负责定期清理 CDC 表中较老的数据
默认保留 3 天 CDC 日志数据（4320秒）

开启 CDC 功能后，SQL Server 数据库会多出一个名称为 cdc 的 schema，里面会多出下列这些表。

change_tables

记录每一个启用了 CDC 的源表及其对应的 捕获表

captured_columns

记录对应 捕获表 中每个列的信息

index_columns

记录源表含有的主键信息(如果有)

lsn_time_mapping

记录每个事务的开始/结束时间及 LSN 位置信息

ddl_history

记录源表发生的 增/减列 对应的 DDL 信息，除此之外的 DDL 都不会被记录

有了上述准备动作和信息，即可开始对原始表开启 change data capture(CDC) ，即增量数据捕获了。

捕获表变更

有如下源表

create table [dbo].[test_table] (
  [id] [bigint] NOT NULL primary key,
  [test] [nchar](10) NULL
)

执行下列命令即可为它启用 CDC

exec [console].[sys].[sp_cdc_enable_table]
    @source_schema = [dbo],
    @source_name = [test_table],
    @role_name = NULL,
    @capture_instance = [dbo_test_table], -- 可选项
    @supports_net_changes = 0;

cdc schema 下多出一个名为 dbo_test_table_CT 的表，即 捕获表

对源表 [dbo].[test_table] 做若干 DML 操作，通常是 5 秒内就可在捕获表中看到变更记录
对源表做一些 增/减列 操作,对应的 DDL 会出现在 ddl_history 表中

其他表也可通过类似设置，获取到相应的增量变更。整个机制看上去相当直观和简单。

挑战是什么？

难点1：DDL 同步困难

CDC 捕获表只反馈数据的变化，无 DDL 信息

DDL 需额外获取即和 DML 的顺序关系要额外处理

解决这个问题，需要通过执行以下的 SQL 将 DDL 和 DML 事件混合到一起并保证顺序，但是实际使用中会面临严重的性能问题。

select * from (
    select __$start_lsn lsn,__$operation oper,__$update_mask mask, null ddl ,id data_id,test data_test 
    from [console].[cdc].[dbo_test_table_CT]
    union
    select ddl_lsn lsn, -1 oper,null mask,  ddl_command ddl ,null data_id,null data_test 
    from [console].[cdc].[ddl_history]
) t order by lsn