开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server审计功能入门:CDC(Change Data Capture)

简介: 原文:SQL Server审计功能入门:CDC(Change Data Capture) 介绍 SQL Server 2008引入了CDC(Change Data Capture),它能记录: 1. 哪些数据行发生了改变 2. 数据行变更的历史记录,而不仅仅是最终值。
+关注继续查看
原文:SQL Server审计功能入门:CDC(Change Data Capture)

介绍

SQL Server 2008引入了CDC(Change Data Capture),它能记录:

1. 哪些数据行发生了改变

2. 数据行变更的历史记录,而不仅仅是最终值。

跟CT(Change Tracking)相比,它通过作业实现异步变更跟踪(像事务复制),而CT是同步实现的。因此它对性能的影响较轻并且不会影响事务。

典型应用是在提取、传输和加载数据到其它数据源,就像图中的数据仓库。

clip_image001

实现

微软建议CDC结合快照快照隔离级别使用,可以避免读取变更数据与变更数据写入时的读写阻塞。

需要注意:快照隔离级别会有额外的开销,特别是Tempdb(所有的数据更改都会被版本化存到tempdb)。

use master

go

create database CDCTest

go

alter database CDCTest set allow_snapshot_isolation on

go

--enable CDC on database CDCTest

use CDCTest

go

exec sys.sp_cdc_enable_db

go

启用CDC之后会新增一个叫CDC的Schema和一系列的系统表、SP和View。官方建议不要直接查询系统表而是使用对应的系统SP/FN来获取CDC数据。

clip_image002

系统对象

说明

建议使用的对象

cdc.captured_columns

为在捕获实例中跟踪的每一列返回一行

sys.sp_cdc_get_source_columns

cdc.change_tables

为数据库中的每个更改表返回一行

sys.sp_cdc_help_change_data_capture

cdc.ddl_history

针对启用了变更数据捕获的表所做的每一数据定义语言 (DDL) 更改返回一行

sys.sp_cdc_get_ddl_history

cdc.lsn_time_mapping

为每个在更改表中存在行的事务返回一行

sys.fn_cdc_map_lsn_to_time (Transact-SQL) , sys.fn_cdc_map_time_to_lsn (Transact-SQL)

cdc.index_column

为与更改表关联的每一索引列返回一行

sys.sp_cdc_help_change_data_capture

msdb.dbo.cdc_jobs

存储用于捕获和清除作业的变更数据捕获配置参数

NA

cdc.<capture_instance>_CT

对源表启用变更数据捕获时创建的更改表。 该表为对源表执行的每个插入和删除操作返回一行,为对源表执行的每个更新操作返回两行.capture_instance格式=SchameName_TableName

cdc.fn_cdc_get_all_changes_<capture_instance> ,

cdc.fn_cdc_get_net_changes_<capture_instance>

创建测试表并对期启用CDC。使用sys.sp_cdc_enable_table 对表启用CDC。

--Create a test table for CDC

use CDCTest

GO

create table tb(ID int primary key ,name varchar(20),weight decimal(10,2));

go

EXECUTE sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'tb'

  , @role_name = null;

GO

如果源表是数据库中第一个要启用变更数据捕获的表,并且数据库不存在事务发布,则 sys.sp_cdc_enable_table 还将为数据库创建捕获和清理作业。 它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。

对应的跟踪表cdc.dbo_tb_CT包含了源表所有的变更数据。它包含原来所有的列和5个新的列,结构如图:

clip_image003

验证

当在源表中操行数据更改操作,表cdc.dbo_tb_CT会记录下来。试一下:

clipboard[22]

为什么没有数据呢?因为之前介绍过了,CDC是靠作业来捕获变更数据的,我的Agent还没有运行。

手动启用后,就有数据了。

clip_image005

结果列的含义:

列名

数据类型

说明

__$start_lsn

binary(10)

更改提交的LSN。在同一事务中提交的更改将共享同一个提交 LSN 值。

__$seqval

binary(10)

一个事务内可能有多个更改发生,这个值用于对它们进行排序。

__$operation

int

更改操作的类型:

1 = 删除

2 = 插入

3 = 更新(捕获的列值是执行更新操作前的值)。

4 = 更新(捕获的列值是执行更新操作后的值)。

__$update_mask

varbinary(128)

位掩码,源表中被CDC跟踪的每一列对应一个位。如果 __$operation = 1 或 2,该值将所有已定义的位设置为 1。如果 __$operation = 3 或 4,则只有那些对应已更改列的位设置为 1。

现在再插入一行,并更新它,然后再删除ID=1的行。再查看结果:

clip_image006

简单说明一下跟踪的查询结果:总共5行,第一行和第二行是插入数据,第三行和第四行是更新前后的数据,第五行是删除数据。操作类型由_$operation值可得知。

简单应用

前文中创建的tb表,记录了每个人的姓名和体重变化信息。另外某一个数据库(表tb_rs),它是体重变化趋势报表的数据源。它每天同步一次数据,更新自己的数据。怎么用CDC来实现这个需求呢?

      CDC中记录了start_lsn,如果能知道tb_rs上次同步完成时,tb中被同步的最大LSN。那下次同步时,只需要同步tb表中大于此LSN的变更记录即可。

问题就简单:获取上次同步完成tb的最大LSN,获取大于此LSN的所有变更记录,更新tb_rs。

insert into tb

values(1,'Ken',70.2),(3,'Joe',66),(4,'Rose',50)

update tb

set weight=70

where ID=3;

delete from tb where name='Rose';

go

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); 

--get the interval

select @begin_time=GETDATE()-1,@end_time=GETDATE();

--map the time to LSN of the CDC table tb

select  @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time),

  @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

--get the net changes within the specified LSNs

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, 'all');

clip_image007

居然没有Rose的记录?Joe的信息被更新过,怎么才一条记录?

这是因为这里得到是净变更行,也就是最终结果的意思。新增然后又删除,不影响最终结果,所以没有。多次更新同一行的某一列数据,只返回最后更新的结果。

得到这个结果,我们就可以根据__$operation和实际数据定义同步数据的逻辑了。比如:

--generate sync statements

SELECT (case __$operation when 2 then 'insert into tb_rs values ('+cast(ID as varchar(2))+', '+Name+', '+cast(weight as varchar(10))+')'

        when 4 then 'update tb_rs set name='+name+',weight='+cast(weight as varchar(10))+' where ID='++cast(ID as varchar(2)) END)

FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, 'all');

对于更新过的行,同步数据时,我想要先判断出列是否被更改过和被更改的时间。更改过的列才需要被同步,而不是所有列同步一次。以name为例:

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); 

--get the interval

select @begin_time=GETDATE()-1,@end_time=GETDATE();

--map the time to LSN of the CDC table tb

select  @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time),

  @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

--get the all changes within the specified LSNs

SELECT *,

(Case sys.fn_cdc_has_column_changed('dbo_tb','name',__$update_mask) when 1 then 'Yes' when 0 then 'No' End) as isNameUpdated,

sys.fn_cdc_map_lsn_to_time(__$start_lsn) as updateTime

FROM cdc.fn_cdc_get_all_changes_dbo_tb(@begin_lsn, @end_lsn, 'all')

where __$operation in(3,4);

go

CDC不仅能记录DML操作,还能记录DDL操作。查询cdc.ddl_history。

clip_image008

但有一点要格外注意:新增的列,能被CDC DDL跟踪到,但是新列的数据变更却不能被CDC跟踪到。如果需要跟踪它,先禁用表上的CDC,再启用即可。

 

CDC Agent Job

在指定的数据库中首次启用CDC,并且不存在事务复制,则会创建capture和cleanup两个作业:

clip_image009

     capture作业是用于扫描日志文件,把变更记录写到变更表中。调用sp_MScdc_capture_job来实现,可以根据当前库的实际事务吞吐量来设置扫描参数和扫描间隔,使得在性能开销和跟踪需求间达到合理平衡。

     cleanup作业是清理变更变表中的数据,默认三天的数据。

所以合理设定cleanup的间隔是非常重要的。

这两个作业的相关的配置存储在msdb.dbo.cdc_jobs中。当前的默认配置如图:

clip_image010

 

总结

    1. CDC使用方便,易于配置,能与同步抽取等应用结合使用。

    2. CDC能满足大多数对数据审计的要求,但不能告诉你“谁”更改了数据。

    3. 虽说CDC是异步的,对应性能影响小,但还是会增加开销,特别是IO读写和容量方面的。开启CDC,每次更改,都至少会额外增加一次数据文件写和日志文件写操作。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
国务院:支持以应收账款进行融资,降低中小企业现金流压力
3月10日,国务院总理李克强主持召开国务院常务会议,对畅通产业链资金链、推动各环节协同复工复产进行进一步部署,并强调对外开放,确定稳外贸稳外资新举措。
187 0
艾伟_转载:Visual Studio DSL 入门 2
相信如果看过一上篇你已经对vs.net dsl有了一定的了解,接下来我们就来开始我们的这个系列的入门,Vs.net Dsl在国内可能使用的人少之又少,不过希望这个系列能够使看到的人能够对dsl有一定的了解,使之能够成为产品方案选型时的一个参考,能够对阅读的人有所帮助.
828 0
+关注
杰克.陈
一个安静的程序猿~
10425
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载