SQLServer2008引入了数据变更捕获功能（后面简称为CDC）,所以支持2008后的版本
基础信息参考网址
基础信息参考网址
启动数据库CDC功能的主要过程
- 启动SQL Server 代理, 右击启动
- 启用想要监控的数据库的CDC功能
- 启动想要测试数据表的CDC功能
- 关闭想要测试数据表的CDC功能
- 关闭想要监控的数据库的CDC功能
具体执行脚本
目前测试使用的数据库是CDC_DB,数据表是dbo.duliyan
查看数据库是否启用CDC
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDC_DB'-- is_cdc_enabled='Y' OR 1
设置数据库启用CDC
USE CDC_DB
GO
EXECUTE sys.sp_cdc_enable_db； -- --启用数据库CDC
GO
启用表的CDC功能--sp_cdc_enable_table的字段介绍
sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema', ---表所属的架构名dbo
[ @source_name = ] 'source_name' ,----表名
[ @role_name = ] 'role_name'---是用于控制更改数据访问的数据库角色的名称。
[,[ @capture_instance = ] 'capture_instance' ]--是用于命名变更数据捕获对象的捕获实例的名称，这个名称在后面的存储过程和函数中需要经常用到。
[,[ @supports_net_changes = ] supports_net_changes ]---指示是否对此捕获实例启用净更改查询支持如果此表有主键，或者有已使用 @index_name 参数进行标识的唯一索引，则此参数的默认值为 1。否则，此参数默认为 0。
[,[ @index_name = ] 'index_name' ]--用于唯一标识源表中的行的唯一索引的名称。index_name 为 sysname，并且可以为 NULL。如果指定，则 index_name 必须是源表的唯一有效索引。如果指定 index_name，则标识的索引列优先于任何定义的主键列，就像表的唯一行标识符一样。
[,[ @captured_column_list = ] 'captured_column_list' ]--需要对哪些列进行捕获。captured_column_list 的数据类型为 nvarchar(max)，并且可以为 NULL。如果为 NULL，则所有列都将包括在更改表中。
[,[ @filegroup_name = ] 'filegroup_name' ]--是要用于为捕获实例创建的更改表的文件组。
[,[ @partition_switch = ] 'partition_switch' ]--指示是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit，默认值为 1。
查看数据库表是否启用cdc
select * from sys.tables where is_tracked_by_cdc =1
对指定源表启用cdc
exec sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'duliyan' ,
@role_name ='cdc_admin',
@capture_instance=DEFAULT,
@captured_column_list = 'id,name'
启动数据表的CDC功能后，会自动创建一些系统表
select * from [cdc].[change_tables]；-- 记录其实例表的详细信息
select * from [cdc].[captured_columns]； -- 捕获的表已跟踪的列
select * from [cdc].[ddl_history]； --与DDL有关的操作记录
select * from [cdc].[dbo_duliyan_CT]； -- 捕获实例表：这是最重要的表，该表就是记录源表的所有DML操作记录。每个表对应一个实例表，命名方式为“架构名_表名_CT”
__$start_lsn ：与相应更改的提交事务关联的日志序列号 (LSN)
__$end_lsn ： （在 SQL Server 2008中，此列始终为 NULL）
__$seqval ：对事务内的行更改顺序
__$operation ：源表DML操作
1 = 删除
2 = 插入
3 = 更新（旧值）
4 = 更新（新值）
__$update_mask ：基于更改表的列序号的位掩码，用于标识那些发生更改的列
SELECT * FROM SYS.all_objects WHERE name LIKE '%CDC%'OR SCHEMA_ID IN(SELECT SCHEMA_ID FROM SYS.schemas WHERE name='CDC')
ORDER BY TYPE；--与cdc相关的所有表
SELECT * FROM msdb.SYS.objects WHERE name='cdc_jobs'
- 新添加的列（如 info），在更改表中不会添加。但仍可进行跟踪记录操作，只是不在表cdc.captured_columns 中的列则不跟踪记录。
- 删除了列（如 value），更改表中则标识为null 。即时再添加创建原来的字段，也无效。
启用表的cdc功能存储过程
exec open_cdc_table @table_name='duliyan2',@source_schema_in='dbo',@captured_column_list_in='id,name'
create procedure open_cdc_table(@table_name varchar(50),@source_schema_in varchar(50),@captured_column_list_in nvarchar(500) )
as
begin
declare @is_tracked_by_cdc int
select @is_tracked_by_cdc = is_tracked_by_cdc from sys.tables where name = @table_name;
if (@is_tracked_by_cdc <> 1)
exec sys.sp_cdc_enable_table
@source_schema = @source_schema_in,
@source_name = @table_name ,
@role_name ='cdc_admin',
@capture_instance=DEFAULT,
@captured_column_list = @captured_column_list_in
end
关闭表的cdc功能
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo'
, @source_name = N'duliyan2'
, @capture_instance = N'dbo_duliyan2' ;
exec close_cdc_table @table_name='duliyan2',@source_schema_in='dbo';
alter procedure close_cdc_table(@table_name varchar(50),@source_schema_in varchar(50) )
as
begin
declare @is_tracked_by_cdc int
declare @capture_instance_in varchar(100)
set @capture_instance_in=@source_schema_in+'_'+@table_name;
select @is_tracked_by_cdc = is_tracked_by_cdc from sys.tables where name = @table_name;
if (@is_tracked_by_cdc = 1)
EXEC sys.sp_cdc_disable_table
@source_schema = @source_schema_in
, @source_name = @table_name
, @capture_instance = @@capture_instance_in
end
--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo',
'FactInternetSales','All'
EXEC sys.sp_cdc_disable_db
http://technet.microsoft.com/zh-cn/library/cc645937.aspx
- 其实，它是有一个独立的进程的。它是异步地读取日志文件。如果某部分更改没有被进程读到，那么此时日志截断也是没有效果的，很显然需要这样来保证。
- net_changes是什么意思呢？说的是针对一行记录，如果有多个更改的话，那么以最后的一条为准。
- 这个更改是不是会永远保存？不会的，它会定期清除的
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天（4320 分钟），可使用单个删除语句最多删除 5000 项。 - 如果启用了之后，修改了表的结构，会怎么样？
为适应固定列结构更改表，在为源表启用变更数据捕获后，负责填充更改表的捕获进程将忽略未指定进行捕获的任何新列。如果删除了某个跟踪的列，则会为在后续更改项中为该列提供 Null 值。但是，如果现有列更改了其数据类型，则会将更改传播到更改表，以确保捕获机制没有导致跟踪的列发生数据丢失。捕获进程还会将检测的跟踪表列结构的任何更改发送到 cdc.ddl_history 表。如果使用者希望得到下游应用程序中可能需要进行的调整的通知，请使用 sys.sp_cdc_get_ddl_history 存储过程。
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
update dt_user_in
set user_lname ='wangwu'
where ID between 100001 and 200000;
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'
