SSIS学习(三)
来源:互联网 发布:安永 咨询 知乎 编辑:程序博客网 时间:2024/06/05 10:13
变更数据捕获(Change Data Capture ,简称 CDC)记录 SQL Server 表的插入、更新和删除活动。使用变更数据捕获可以更有效跟踪表对象DML历史操作,对 ETL 等数据转移也非常有用。
变更数据捕获适用版本:
SQL Server 2008 以上的 Enterprise Edition、Developer Edition 和 Evaluation Edition
变更数据捕获原理:
变更数据捕获的更改数据源为 SQL Server 事务日志。当对表启用变更数据捕获时,系统将生成一个与该表结构类似的副本。当对源表进行插入、更新和删除 时,在事务日志会记录相关操作信息。变更数据捕获代理使用异步进程读取事务日志,将相关操作结果应用到副本表(捕获实例表)中,这样就完成了对源表操作的记录跟踪。
变更数据捕获实例:
在数据库 MyDatabase 中创建测试表:
查看数据库或表是否启用了cdc:
对当前数据库启用cdc :
可能出现以下错误及解决办法:
启动数据库cdc后,接着对指定源表启用 cdc :
创建完成后,将看到数据库中创建了以下对象:
下面介绍系统表中,各表的主要信息:
【cdc.captured_columns】
select * from cdc.captured_columns
每个启用变更数据库捕获的表,其已跟踪的列都可在该系统表中查看(参考: cdc.captured_columns )
EXEC sys.sp_cdc_get_captured_columns N'dbo_CDC_Test';
建议使用该存储过程返回列的元数据信息 (参考:sys.sp_cdc_get_captured_columns )
【cdc.change_tables】
select *fromcdc.change_tables
每对一个源表启用变更数据捕获时,该表都会记录其实例表的详细信息(参考:cdc.change_tables)
EXEC sys.sp_cdc_help_change_data_capture
EXEC sys.sp_cdc_help_change_data_capture @source_schema='dbo',@source_name='CDC_Test'
最多可为每个源表返回两行,为每个捕获实例返回一行 (参考:sys.sp_cdc_help_change_data_capture)
【cdc.index_columns】
select *fromcdc.index_columns
对源表中的主键列或者启用变更数据捕获时指定的唯一索引列(指定索引优于主键)记录一行,变更数据捕获使用这些索引列来唯一标识源表中的行。默认情况下,将包括源表的主键列。(参考:cdc.index_columns)
EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo' ,@source_name = 'CDC_Test'
或者使用该存储过程查看(如上图)
【cdc.ddl_history】
select *fromcdc.ddl_history
针对启用了变更数据捕获的表所做的每一数据定义语言(DDL)更改返回一行。可以使用此表来确定源表发生DDL更改的时间以及更改的内容。(参考:cdc.ddl_history)
EXEC sys.sp_cdc_get_ddl_history @capture_instance='dbo_CDC_Test'
【cdc.<capture_instance>_CT】
select *fromcdc.dbo_CDC_Test_CT
捕获实例表:这是最重要的表,该表就是记录源表的所有DML操作记录。每个表对应一个实例表,命名方式为“架构名_表名_CT”
应用于源表的每个插入或删除操作在更改表中各占一行。插入操作生成的行的数据列包含插入后的列值。删除操作生成的行的数据列包含删除前的列值。更新操作需要两行数据:一行用于标识更新前的列值,另一行用于标识更新后的列值。
现在对源表进行插入、更新、删除后,查看该跟踪实例表:
__$start_lsn :与相应更改的提交事务关联的日志序列号 (LSN)
__$end_lsn : (在 SQL Server 2008中,此列始终为 NULL)
__$seqval :对事务内的行更改顺序
__$operation :源表DML操作
1 = 删除
2 = 插入
3 = 更新(旧值)
4 = 更新(新值)
__$update_mask :基于更改表的列序号的位掩码,用于标识那些发生更改的列
再测试 DDL 对更改表的影响:
结果总结:
1. 新添加的列(如 info),在更改表中不会添加。但仍可进行跟踪记录操作,只是不在表cdc.captured_columns 中的列则不跟踪记录。
2. 删除了列(如 value),更改表中则标识为null 。即时再添加创建原来的字段,也无效。
增加或者删除一列后,没有记录跟踪,这种情况增么办?
一种方法是:
1. 增删某字段
2. 再对同一个表启用另一个变更数据捕获(新的变更表为源表当前的结构)
3.再根据 ID/时间/唯一键 等从新的跟踪表取数据(非实时获取数据情况,如定期转移数据等)
4. 如觉得每次调用都更改表名,可以使用视图,调用视图查询更改表,视图只要更改对应的表就行。
捕获实例表中的一些约束:
【cdc.lsn_time_mapping】
select * from cdc.lsn_time_mapping
当捕获进程提交每批新的更改数据时,将在该表中为每个具有更改表项的事务添加新的项(参考:cdc.lsn_time_mapping)
建议执行 sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn系统函数
结合 cdc.fn_cdc_get_all_changes_<capture_instance> 和cdc.fn_cdc_get_net_changes_<capture_instance>,可获取一段范围内的数据变化情况。
对作业的更改 (参考 sys.sp_cdc_change_job)
在可更新订阅的复制中,跟踪表记录有些不同:
在发布启用cdc:
若创建了发布,则cdc的capture作业将不创建,共同使用日志读取器代理(使用sp_replcmds 读取日志)
在发布库更新一行记录,则在跟踪表记录6行(更新了3次)
在订阅库更新一行记录,同步到发布,则在跟踪表记录2行(更新1次)
在订阅启用cdc:
在发布库更新一行记录,同步到订阅,则在跟踪表记录6行(更新了3次)
在订阅库更新一行记录,则在跟踪表记录4行(更新2次)
主要原因是:可更新订阅的校验列 msrepl_tran_version 更新多次。
最后一项,禁用(删除)变更数据捕获:
- SSIS学习(三)
- SSIS 学习
- SSIS学习之SSIS介绍
- SSIS学习之SSIS项目
- SSIS学习之SSIS介绍
- 实例学习SSIS(三)--使用包配置
- 实例学习SSIS(三)--使用包配置
- 数据库:SSIS学习之SSIS介绍
- SSIS 学习之旅 SSIS 简介
- SSIS学习第一步
- SSIS 学习(1) 概要
- SSIS学习概要:
- SSIS学习数据流
- SSIS学习数据流2
- SSIS学习:容器
- SSIS 学习(1) 概要
- SSIS学习(一)
- SSIS学习(二)
- 设计自己的ImageLoader图片加载框架
- ES6学习4章:字符串的扩展
- 对不起梦想,欠你一张电影票——《缝纫机乐队》观后
- 结构体的大小,对齐方式,点运算符和箭头运算符,#pragma pack(4)
- 2018年需要关注的10个设计趋势
- SSIS学习(三)
- 提高数据库处理查询速度
- 经常使用的20个css高级技巧(上)
- ES6学习5章:正则&数值扩展
- gpio 注册中断 例子
- 8年前比QQ还屌”飞信”,现不得不靠中国移动再次注资
- Struts2 XML配置详解
- 数据库——CAP理论
- [App] linux 下 ActiveMQ 安装