cdc:change data capture
复制代码 代码如下:
–步骤：本文中以gposdb为例
–第一步、对目标库显式启用cdc：
–在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。
–注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
–该存储过程的作用域是整个目标库。包含元数据、ddl触发器、cdc架构和cdc用户。
–使用以下代码启用：
use gposdb –要启用cdc的数据库
go
execute sys.sp_cdc_enable_db;
go
–在一开始直接执行时，出现了报错信息：
–消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193 行
–无法更新元数据来指示已对数据库adventureworks 启用了变更数据捕获。
–执行命令’setcdctracked(value = 1)’ 时失败。
–返回的错误为15517: ‘无法作为数据库主体执行，因为主体”dbo”
–不存在、无法模拟这种类型的主体，或您没有所需的权限。’。请使用此操作和错误来确定失败的原因并重新提交请求。
–这里引出了另外一个知识点：错误号 15517 的错误
–这种错误会在很多地方出现，如还原数据库的时候也会有可能出现。
–共同点是：某个/些存储过程使用了具有withexecute as 的选项。
–使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错，解决方法：
alter authorization on database::[gposdb] to [sa]
–经过检查，uspupdateemployeehireinfo这个存储过程的确有：with execute as caller
–使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。
–现在重新执行：
use gposdb
go
execute sys.sp_cdc_enable_db;
go
–启用成功，然后通过以下语句检查是否成功：
select
is_cdc_enabled,
case when is_cdc_enabled=0 then ‘cdc功能禁用’ else ‘cdc功能启用’ end 描述
from sys.databases
where name = ‘gposdb’
–创建成功后，将自动添加cdc用户和cdc架构。
–在用户和架构下面可以看到cdc用户和cdc架构
–创建这两个用户、架构的原因是因为cdc要求独占方式使用这两个架构，所以要单独创建。
–如果存在了非cdc功能创建的cdc用户、架构的话，则需要先删除该cdc命名的架构，才能开启。
–第二步、对目标表启用cdc：
–使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。
–然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。
–默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，
–可以使用@captured_column_list参数指定这些列。
–如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）。
–如果不想控制访问角色，则@role_name必须显式设置为null。
sys.sp_cdc_enable_table
[ @source_schema = ] ‘source_schema’,
[ @source_name = ] ‘source_name’ ,
[ @role_name = ] ‘role_name’
[,[ @capture_instance = ] ‘capture_instance’ ]
[,[ @supports_net_changes = ] supports_net_changes ]
[,[ @index_name = ] ‘index_name’ ]
[,[ @captured_column_list = ] ‘captured_column_list’ ]
[,[ @filegroup_name = ] ‘filegroup_name’ ]
[,[ @partition_switch = ] ‘partition_switch’ ]
–例子：
–把systempara 这个表开启变更捕获。
use gposdb
go
exec sys.sp_cdc_enable_table @source_schema = ‘dbo’,
@source_name = ‘systempara’,@role_name = null
–然后查询是否成功：
select name ,
is_tracked_by_cdc ,
case when is_tracked_by_cdc = 0 then ‘cdc功能禁用’
else ‘cdc功能启用’
end 描述
from sys.tables
where object_id = object_id(‘dbo.systempara’)
–对表开启以后，可以在下图中看到多了很多cdc架构开头的表：
–刷新一下gposdb数据库，在系统表下面可以看到多了下面几张表
[cdc].[dbo_systempara_ct]
[cdc].[change_tables]
[cdc].[captured_columns]
[cdc].[ddl_history]
[cdc].[index_columns]
[cdc].[lsn_time_mapping]
[dbo].[systranschemas]
[dbo].[dtproperties]
–启动之后，可以看到sqlserver代理里面的作业，也出现了这两个作业：
[cdc.gposdb_capture]
[cdc.gposdb_cleanup]
–在可编程性-》函数-》表值函数里，也多了两个函数
[cdc].[fn_cdc_get_all_changes_dbo_systempara]
[cdc].[fn_cdc_get_net_changes_dbo_systempara]
–下面列出相关的存储过程：
–sys.sp_cdc_add_job
–说明及例子
–sys.sp_cdc_generate_wrapper_function
–说明及例子
–sys.sp_cdc_change_job
–说明及例子
–sys.sp_cdc_get_captured_columns
–说明及例子
–sys.sp_cdc_cleanup_change_table
–说明及例子
–sys.sp_cdc_get_ddl_history
–说明及例子
–sys.sp_cdc_disable_db
–说明及例子 建议先禁用表，再禁用库
–sys.sp_cdc_help_change_data_capture
–说明及例子
–sys.sp_cdc_disable_table
–说明及例子
–sys.sp_cdc_help_jobs
–说明及例子
–sys.sp_cdc_drop_job
–说明及例子
–sys.sp_cdc_scan
–说明及例子
–sys.sp_cdc_enable_db
–说明及例子
–sys.sp_cdc_start_job
–说明及例子
–sys.sp_cdc_enable_table
–说明及例子
–sys.sp_cdc_stop_job
–说明及例子
–函数：
–cdc.fn_cdc_get_all_changes_<capture_instance>
–说明及例子
–sys.fn_cdc_has_column_changed
–说明及例子
–cdc.fn_cdc_get_net_changes_<capture_instance>
–说明及例子
–sys.fn_cdc_increment_lsn
–说明及例子
–sys.fn_cdc_decrement_lsn
–说明及例子
–sys.fn_cdc_is_bit_set
–说明及例子
–sys.fn_cdc_get_column_ordinal
–说明及例子
–sys.fn_cdc_map_lsn_to_time
–说明及例子
–sys.fn_cdc_get_max_lsn
–说明及例子
–sys.fn_cdc_map_time_to_lsn
–说明及例子
–sys.fn_cdc_get_min_lsn
–说明及例子
——————–下面开始从头到尾做一个实际案例————————-
–下面开始从头到尾做一个实际案例
–步骤一:对目标库显式启用cdc
use gposdb –要启用cdc的数据库
go
execute sys.sp_cdc_enable_db;
go
–某些数据库可能存在一些存储过程包含有：execute as 等语句，此时会报错：
–文字描述：
–消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186 行
–无法更新元数据来指示已对数据库adventureworks 启用了变更数据捕获。执行命令’setcdctracked(value = 1)’ 时失败。返回的错误为15517: ‘无法作为数据库主体执行，因为主体”dbo” 不存在、无法模拟这种类型的主体，或您没有所需的权限。’。请使用此操作和错误来确定失败的原因并重新提交请求。
–消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0 行
–execute 后的事务计数指示begin 和commit 语句的数目不匹配。上一计数= 0，当前计数= 1。
–消息266，级别16，状态2，过程sp_cdc_enable_db，第0 行
–execute 后的事务计数指示begin 和commit 语句的数目不匹配。上一计数= 0，当前计数= 1。
–消息3998，级别16，状态1，第1 行
–在批处理结束时检测到不可提交的事务。该事务将回滚。
–如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用execute as的库，一般不会有这样的问题：
alter authorization on database::[gposdb] to [sa]
–现在重新执行：
use gposdb
go
execute sys.sp_cdc_enable_db;
go
–通过以下语句检查是否成功：
select
is_cdc_enabled,
case when is_cdc_enabled=0 then ‘cdc功能禁用’ else ‘cdc功能启用’ end 描述
from sys.databases
where name = ‘gposdb’
–步骤二：对表启用cdc
use gposdb
go
exec sys.sp_cdc_enable_table
@source_schema = ‘dbo’,
@source_name = ‘systempara’,
@role_name = null,
@capture_instance=default
go
–然后查询是否成功：
select name ,
is_tracked_by_cdc ,
case when is_tracked_by_cdc = 0 then ‘cdc功能禁用’
else ‘cdc功能启用’
end 描述
from sys.tables
where object_id = object_id(‘dbo.systempara’)
–可以看到gposdb数据库里的系统表里新增了[cdc].[dbo_systempara_ct]表
–步骤三：检验，下面来改动数据
–先查询一下dbo_systempara_ct表
select * from cdc.[dbo_systempara_ct]
–可以看到一条记录都没有，因为刚刚创建，并没有对原表systempara做任何增删改操作
–向[systempara]表插入一条记录
insert into [dbo].[systempara]
( [paravalue] ,
[name] ,
[description]
)
values ( ‘中国’ , — paravalue – varchar(50)
‘中国’ , — name – varchar(50)
‘中国’ — description – varchar(50)
)
–查询一下dbo_systempara_ct表，可以看到多了一条记录
select * from cdc.[dbo_systempara_ct]
–更新[systempara]表的一条记录
update [dbo].[systempara] set [paravalue]=’德国’ where [description]=’中国’
–查询一下dbo_systempara_ct表，可以看到多了两条记录
select * from cdc.[dbo_systempara_ct]
–删除[systempara]表的一条记录
delete from [dbo].[systempara] where [description]=’中国’
–查询一下dbo_systempara_ct表，可以看到多了一条记录
select * from cdc.[dbo_systempara_ct]
–现在来分析一下dbo_systempara_ct表
–可以在联机丛书上查看：
–cdc.<capture_instance>_ct
–可以看到，这样命名的表，是用于记录源表做过更改操作的表。
–对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
–对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
–update语句的__$operation列的值是3和4，所以一条update语句对应两条记录
–对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（lsn）
–但是微软不检查直接查询这类表，建议使用
–cdc.fn_cdc_get_all_changes_<捕获实例>
–cdc.fn_cdc_get_net_changes_<capture_instance>
— 来查询
———————————————————-
–下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。
–日常使用情景：
–1、查询已经开启的捕获实例：
–返回所有表的变更捕获配置信息
use [gposdb]
go
execute sys.sp_cdc_help_change_data_capture;
go
–查看对某个实例(即表)的哪些列做了捕获监控
use [gposdb]
go
exec sys.sp_cdc_get_captured_columns
@capture_instance = ‘systempara’ — sysname
–也可以从下面中查找配置信息
select * from msdb.dbo.cdc_jobs
–2、查看当前配置使用sp_cdc_help_jobs：
–从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：
sp_cdc_help_jobs
–对于一个大型的oltp系统，由于数据更改会非常频繁，变更表中的数据会非常多，
–如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。
–此时可以调整上图中cdc.adventureworks_cleanup 中retention（单位：分钟）。
–3、修改配置：sp_cdc_change_job
–显示原有配置
exec sp_cdc_help_jobs
go
–更改数据保留时间为100分钟
execute sys.sp_cdc_change_job
@job_type = n’cleanup’,
@retention=100
go
–重启一下作业，以使设置生效
–停用作业
exec sys.sp_cdc_stop_job n’cleanup’
go
–启用作业
exec sys.sp_cdc_start_job n’cleanup’
go
–再次查看
exec sp_cdc_help_jobs
go
–可以看到retention（单位：分钟）的值变为100了
–4、停止/启用、删除/创建作业
–停用作业
exec sys.sp_cdc_stop_job n’cleanup’
go
–启用作业
exec sys.sp_cdc_start_job n’cleanup’
go
–删除作业
exec sys.sp_cdc_drop_job @job_type = n’cleanup’ — nvarchar(20)
go
–查看作业
exec sys.sp_cdc_help_jobs
go
–可以看到现在只剩下一个作业了:cdc.gposdb_capture
–创建作业
exec sys.sp_cdc_add_job
@job_type = n’cleanup’,
@start_job = 0,
@retention = 5760
–查看作业
exec sys.sp_cdc_help_jobs
go
–5、ddl变更捕获：
–cdc除了捕获数据变更之外，还能捕获ddl操作的变化。
–前提是先要确保sqlserver 代理的启用，其实cdc功能都需要确保sql 代理正常运行
–因为所有操作都通过代理中的两个作业来实现的。
–现在先来对systempara 表修改一下，把paravalue的长度加长
use [gposdb]
go
alter table [dbo].[systempara] alter column paravalue varchar(120) ;
go
–然后查询ddl记录表
select * from cdc.ddl_history
–6、使用cdc的函数来获取更改
–a、使用 [cdc].[fn_cdc_get_all_changes_dbo_systempara]
–函数报告捕获实例的当前所有可用更改
declare @from_lsn binary(10) ,
@to_lsn binary(10)
set @from_lsn = sys.fn_cdc_get_min_lsn(‘systempara’)
set @to_lsn = sys.fn_cdc_get_max_lsn()
select *
from cdc.fn_cdc_get_all_changes_dbo_systempara(@from_lsn, @to_lsn,n’all update old’);
go
–b、获取某个时间段的更改信息：
–先根据日志序列号（logsequence number ,lsn）来获取跟踪变更数据
–sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小lsn值。可以使用
smallest greater than;
smallest greater than orequal;
largest less than;
largest less than or equal;
–如查询某个时间段插入的数据
insert into [dbo].[systempara]
( [paravalue] ,
[name] ,
[description]
)
values ( ‘中国’ , — paravalue – varchar(50)
‘中国’ , — name – varchar(50)
‘中国’ — description – varchar(50)
)
go
–检查数据
–1删除
–2插入
–3、4更改
–曾经插入过的记录就算delete了也可以查询出来
declare @bglsn varbinary(10)= sys.fn_cdc_map_time_to_lsn(‘smallest greater than or equal’,
‘2013-10-21 12:00:00.997’)
declare @edlsn varbinary(10)= sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,
getdate())
select *
from [cdc].[dbo_systempara_ct]
where [__$operation] = 2
and [__$start_lsn] between @bglsn and @edlsn
–c、sys.fn_cdc_map_lsn_to_time 查询变更时间：
select [__$operation] ,
case [__$operation]
when 1 then ‘删除’
when 2 then ‘插入’
when 3 then ‘更新（捕获的列值是执行更新操作前的值）’
when 4 then ‘更新（捕获的列值是执行更新操作后的值）’
end [类型] ,
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] ,
*
from [cdc].[dbo_systempara_ct]
–d、获取lsn边界
select sys.fn_cdc_get_max_lsn() [数据库级别的最大lsn] ,
sys.fn_cdc_get_min_lsn(‘cdc.dbo_systempara_ct’) [捕获实例的lsn]
这两个值可以用于上面提到的函数里面用于筛选数据之用。
———————————————————-
–1. cdc的目的是什么？
–cdc就是极大地方便了我们获取某个表数据更新情况的一个机制。它通过一个独立的进程，
–异步读取日志文件，而不是触发器的方式工作。而且它的数据是会持久化保存到一个系统表的。
–2. cdc是不是sql server 2008特有的功能，对别的数据库或者早期版本是否起作用？
–cdc是sql server 2008特有的功能，而且是企业版特有的功能。开发版也有该功能，但仅用于测试场合。
–3. cdc读取日志，那么如果日志被截断了会怎么样？
–如果某部分日志，cdc的进程还没有读取，那么在截断日志时就会忽略这个部分,不能截断!!
–捕获进程是一个独立的，它随着代理服务启动而启动。两次扫描之间间隔5分钟。
–4. 系统表中的数据是否会永久存在？–不会，它会被保留3天。会有一个清理的作业，每天晚上2点进行扫描。
–最后，补充一点的是，cdc功能依赖agent服务，因为它有两个操作都是通过作业来启动的。