CDC操作实例

 

使用SQL Server 2008的朋友应该知道CDC是在SQL Server 2008 CTP3中才加入特性，在前两个CTP中还没有出现过，本文主要给大家介绍如何在SQL Server 2008中实现CDC。

变更数据查询

变更捕获表是那些CDC实例名+CT的表，表中记录了源表的数据变更历史。但通常要按照一定的要求查询这些变更。

在数据库的CDC构架中，除了变更捕获表外，还可看到有5个在数据库启用CDC时建立的表：

表名 作用

captured_columns 记录所有CDC实例要保存的列

change_tables 记录所有的CDC实例

ddl_history 记录所有源表由DDL产生的变更

index_columns 记录CDC实例使用的唯一索引

lsn_time_mapping 记录日志序列号的时间，每个DML事务都有一个日志序列号

除了关联查询这些CDC表外，SQL Server 2008里还有多个CDC函数和储存过程，用于查询变更数据。

　　1. 日志序列号与事务时间的变换

　　在change tables中没有记录事务发生的时间，只记录了事务的日志序列号(lsn)，而日志序号号对应的时间记录在lsn_time_mapping表中。sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn是两个用于转换日志序列号与事务时间的函数;sys.fn_cdc_map_time_to_lsn用于获取某一时间段内的所有日志序列号。

　　2. 最小和最大日志序列号

　　sys.fn_cdc_get_min_lsn和 sys.fn_cdc_get_max_lsn函数获得目前存在的最大和最小日志序列号。

　　3. 查询变更数据

　　cdc.fn_cdc_get_all_changes_函数用于查询实例中满足要求的所有变更记录。cdc.fn_cdc_get_net_changes_函数用于查询实例中满足要求的净变更记录，所谓的净变更记录既是最后一次DML操作后源表的记录，比如在对一行数据进行了多次update后，使用cdc.fn_cdc_get_all_changes_会返回所有更新前和更新后的数据记录，而净变更只返回最后一次更新后的记录。

　　4. 获取变更列

　　在对源表进行update操作后，有时需要知道更新的是哪一列。在变更捕获表中__$update_mask字段保存变更列的2进制编码。sys.fn_cdc_is_bit_set用于返回列序的二进制值，比如要知道第3 列是否变更，使用sys.fn_cdc_is_bit_set( 3, __$update_mask )，若返回1，则表明第3列变更，返回0，则表明没有变更。另外要知道实例中的列是第几列，可使用sys.fn_cdc_get_column_ordinal函数。

　　5. 获取源表DDL变更历史

　　sys.sp_cdc_get_ddl_history函数用于查询对源表使用数据定义语句的历史，通常在用DDL改变源表时，也要使用同样的DLL改变变更捕获表。比如删除源表中某一列，或者将某一列的值类型由int改成long，那么变更捕获表也要跟着变化。

　　变更数据的清理

　　变更捕获表中数据要周期性的加载到数据仓库中，被加载后的数据就要清理掉，否则用于cdc的数据会越来越多。使用sys.sp_cdc_cleanup_change_table存储过程清除变更数据。此外，在启用数据库CDC时，系统自动在SQL Server Agent中加入每日清除变更数据的作业。

　　停用CDC

　　sys.sp_cdc_disable_table_change_data_capture存储过程用于停用CDC实例。sys.sp_cdc_disable_db_change_data_capture存储过程用于停用数据库CDC功能。

　　小结

　　CDC是在SQL Server 2008 CTP3中才加入特性，在前两个CTP中还没有出现过，可以看到还有些不尽人意，没有Oracle那么完善，希望在正式版中能进一步改进。

 

--创建测试表

Create		TABLE employee (
	employeeid       INT, 
	nationalidnumber NVARCHAR(15), 
	contactid        INT, 
	managerid        INT, 
	title            NVARCHAR(50), 
	birthdate        DATETIME, 
	maritalstatus    NCHAR(1), 
	gender           NCHAR(1), 
	hiredate         DATETIME, 
	salariedflag     bit, 
	vacationhours    SMALLINT, 
	sickleavehours   SMALLINT, 
	currentflag      bit, 
	rowguid          UNIQUEIDENTIFIER primary key, 
	[user]           NVARCHAR(50)) 


ALTER TABLE dbo.employee 
ADD [NewCol] INT; 



exec sys.sp_cdc_enable_db 


EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo'
  , @source_name = N'employee'
  , @role_name = N'cdc_admin'
  , @capture_instance = N'employee' 
  , @supports_net_changes = 1
  --, @index_name = N'AK_Department_Name' 
  --, @captured_column_list = N'DepartmentID, Name, GroupName' 
  , @filegroup_name = N'PRIMARY';
GO



Alter FUNCTION [dbo].[Udf_employee_newcol] 
               (@start_time DATETIME, 
                @end_time   DATETIME) 
RETURNS @Employee TABLE(employeeid       INT, 
                        nationalidnumber NVARCHAR(15), 
                        contactid        INT, 
                        managerid        INT, 
                        title            NVARCHAR(50), 
                        birthdate        DATETIME, 
                        maritalstatus    NCHAR(1), 
                        gender           NCHAR(1), 
                        hiredate         DATETIME, 
                        salariedflag     bit, 
                        vacationhours    SMALLINT, 
                        sickleavehours   SMALLINT, 
                        currentflag      bit, 
                        rowguid          UNIQUEIDENTIFIER, 
                        [user]           NVARCHAR(50), 
                        --[NewCol]         INT, 
                        cdc_operation    VARCHAR(1)) 
AS 
  BEGIN 
    --declare local variables to hold LSNs 
    DECLARE  @from_lsn BINARY(10), 
             @to_lsn   BINARY(10) 
     
    --Map the time interval to a change data capture query range. 
    IF (@start_time IS NULL) 
      BEGIN 
        SELECT @from_lsn = sys.Fn_cdc_get_min_lsn('employee') 
      END 
    ELSE 
      BEGIN 
        SELECT @from_lsn = sys.Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
      END 
     
    IF (@end_time IS NULL) 
      BEGIN 
        SELECT @to_lsn = sys.Fn_cdc_get_max_lsn() 
      END 
    ELSE 
      BEGIN 
        SELECT @to_lsn = sys.Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time) 
      END 
     
    --if same then exit 
    IF (@from_lsn = sys.Fn_cdc_increment_lsn(@to_lsn)) 
      BEGIN 
        RETURN 
      END 
     
    -- Query for change data 
    INSERT INTO @Employee 
    SELECT employeeid, 
           nationalidnumber, 
           contactid, 
           managerid, 
           title, 
           birthdate, 
           maritalstatus, 
           gender, 
           hiredate, 
           salariedflag, 
           vacationhours, 
           sickleavehours, 
           currentflag, 
           rowguid, 
           [user], 
           --[NewCol], 
           CASE __$operation 
             WHEN 1 
             THEN 'D' 
             WHEN 2 
             THEN 'I' 
             WHEN 4 
             THEN 'U' 
             ELSE NULL 
           END AS cdc_operation 
    FROM   cdc.fn_cdc_get_all_changes_employee(@from_lsn,@to_lsn,'all') 
     
    RETURN 
  END 

GO 

SELECT * 
FROM   [dbo].[Udf_employee_newcol](NULL,NULL) 



insert into employee
select 3,N'tes7',1,1,N'Title',GETDATE(),N'P',N'e',GETDATE(),1,12,11,1,NEWID(),N'dba'


update employee set gender ='g'
where employeeid = 1


SELECT * 
FROM   [dbo].[Udf_employee_newcol](NULL,NULL) 





exec sys.sp_cdc_disable_table @source_schema='dbo',@source_name ='employee',@capture_instance ='employee'

drop table employee



SELECT from_lsn = sys.Fn_cdc_get_min_lsn('employee') 


select * from cdc.employee_CT