CDC

CDC（变更数据捕获）

环境：SQL SERVER 2008 及以上

对于某些要求有效性的应用程序来说，查询数据库中已更改的数据的能力是一项很重要的要求。通常，为了确定数据更改，应用程序开发人员必须在其应用程序中使用触发器、时间戳列和其他表的组合来实现自定义跟踪方法。创建这些应用程序通常涉及多项工作，导致架构更新，并且通常带来较高的性能开销：

 

一：安装及配置

CDC功能使用在数据库级别，默认是不可用，为让CDC使用，可以通过任何用户设置，但不能应用在系统数据库，执行T-SQL脚本：

 

--Configure
declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
-- new column added to sys.databases: is_cdc_enabled
select name, is_cdc_enabled from sys.databases

--Result
Database                         value
---------------------------- -----------------------
AdventureWorksDW2008	0
AdventureWorksLT2008	0
AdventureWorks	        0
AdventureWorksDW	        0
AdventureWorksLT	        0
testcsdn	                        1
 



下一步：配置表

这里就使用一个表吧！^_^

create table dbo.customer
(
id int identity not null
, name varchar(50) not null
, state varchar(2) not null
, constraint pk_customer primary key clustered (id)
)
 



配置表，使当前表可用CDC；

--TSQL
exec sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'customer' ,
    @role_name = 'CDCRole',
    @supports_net_changes = 1

select name, type, type_desc, is_tracked_by_cdc from sys.tables

--result
name	        type	  type_desc	is_tracked_by_cdc
---------------  -------- ------------------ ------------------------------
TB	                U 	  USER_TABLE	0
systranschemas	U 	  USER_TABLE	0
change_tables	U 	  USER_TABLE	0
ddl_history 	U 	  USER_TABLE	0
lsn_time_mapping	U 	USER_TABLE	0
captured_columns	U 	USER_TABLE	0
index_columns	        U 	USER_TABLE	0
customer	                U 	USER_TABLE	1
dbo_customer_CT	U 	USER_TABLE	0
TA	                        U 	USER_TABLE	0
 



sys.sp_cdc_enable_table此系统存储过程有一些参数，下面描述一下

    *  @source_schema is the schema name of the table that you want to enable for CDC
    * @source_name is the table name that you want to enable for CDC
    * @role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist.  You can add users to this role as required; you only need to add users that aren't already members of the db_owner fixed database role.
    * @supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
    * @capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
    * @index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
    * @captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
    * @filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
    * @partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed; i.e. allowing you to enable partitioning (TRUE or FALSE).

也可以查看一下，CDC使用的各个对象

select o.name, o.type, o.type_desc from sys.objects o
join sys.schemas  s on s.schema_id = o.schema_id
where s.name = 'cdc'
 



二：CDC Demo


We will perform the following steps to demonstrate the CDC functionality:

让我们来执行一些TSQL

insert customer values ('abc company', 'md')
insert customer values ('xyz company', 'de')
insert customer values ('xox company', 'va')
update customer set state = 'pa' where id = 1
delete from customer where id = 3
 

现在我们查询刚才我们所做的TSQL操作

declare @begin_lsn binary(10), @end_lsn binary(10)
-- get the first LSN for customer changes
select @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
-- get the last LSN for customer changes
select @end_lsn = sys.fn_cdc_get_max_lsn()
-- get net changes; group changes in the range by the pk
select * from cdc.fn_cdc_get_net_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); 
-- get individual changes in the range
select * from cdc.fn_cdc_get_all_changes_dbo_customer( @begin_lsn, @end_lsn, 'all');

__$start_lsn	__$operation	__$update_mask	id	name	state
------------------------------  ----- ---------- ------------------- --------
0x0000001E0000021E0004	2	NULL	2	xyz company	de
0x0000001E000002200004	2	NULL	1	abc company	pa

__$start_lsn	__$seqval	__$operation	__$update_mask	id	name	state
-------------- ---------------- ---------------- ------------------------- -------------- -----------------
0x0000001E0000021A0014	0x0000001E0000021A0013	2	0x07	1	abc company	md
0x0000001E0000021E0004	0x0000001E0000021E0003	2	0x07	2	xyz company	de
0x0000001E0000021F0004	0x0000001E0000021F0003	2	0x07	3	xox company	va
0x0000001E000002200004	0x0000001E000002200002	4	0x04	1	abc company	pa
0x0000001E000002220005	0x0000001E000002220002	1	0x07	3	xox company	va
 



这里可以通过log explore来查看日志，分析所得数据；






下面让我们扩展一下，查看其LSN：

create table dbo.customer_lsn (
last_lsn binary(10)
)
create function dbo.get_last_customer_lsn() 
returns binary(10)
as
begin
 declare @last_lsn binary(10)
 select @last_lsn = last_lsn from dbo.customer_lsn
 select @last_lsn = isnull(@last_lsn, sys.fn_cdc_get_min_lsn('dbo_customer'))
 return @last_lsn
end
 

declare @begin_lsn binary(10), @end_lsn binary(10)
-- get the next LSN for customer changes
select @begin_lsn = dbo.get_last_customer_lsn()
-- get the last LSN for customer changes
select @end_lsn = sys.fn_cdc_get_max_lsn()
-- get the net changes; group all changes in the range by the pk
select * from cdc.fn_cdc_get_net_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); 

--result
__$start_lsn	__$operation	__$update_mask	id	name	state
-------------- ------------------ --------------------------- -------------- -----------------
0x0000001E0000021E0004	2	NULL	2	xyz company	de
0x0000001E000002200004	2	NULL	1	abc company	pa

-- get all individual changes in the range
select * from cdc.fn_cdc_get_all_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); 

__$start_lsn	__$seqval	__$operation	__$update_mask	id	name	state
---------------- -------------- ----------------- ------------------------ ------------ ------------------
0x0000001E0000021A0014	0x0000001E0000021A0013	2	0x07	1	abc company	md
0x0000001E0000021E0004	0x0000001E0000021E0003	2	0x07	2	xyz company	de
0x0000001E0000021F0004	0x0000001E0000021F0003	2	0x07	3	xox company	va
0x0000001E000002200004	0x0000001E000002200002	4	0x04	1	abc company	pa
0x0000001E000002220005	0x0000001E000002220002	1	0x07	3	xox company	va

-- save the end_lsn in the customer_lsn table
update dbo.customer_lsn
set last_lsn = @end_lsn
if @@ROWCOUNT = 0
insert into dbo.customer_lsn values(@end_lsn)




 

可以通过查底当前日志段来分析所执行的TSQL：

 

参考：

http://technet.microsoft.com/zh-cn/library/bb522489.aspx

http://www.mssqltips.com/tip.asp?tip=1474