SQL SERVER 2008 数据变更捕获(CDC )

SQL SERVER 2008 异步捕获表数据修改

 

写的不对的地方请各位指正，写的也比较乱。讲究这看吧。^ ^

/*

SQL SERVER 2008 异步捕获表数据修改

 

 

SQL server 2008为异步跟踪所有发生在用户表上的数据修改提供了内建的方法,

而不需要编写自定义的触发器或者查询,变更数据捕获拥有最小性能开销,可以

用于其它数据源的持续更新,例如，将OLTP数据库中的数据变更迁移到数据仓库

数据库.下面我们演示该功能.

 

 

*/

 

 

/*

  1 建立测试数据库

*/

IF NOT EXISTS (SELECT NAME FROM SYS.databases WHERE name='CDC_TEST_DB')

 

BEGIN

    CREATE DATABASE CDC_TEST_DB

END

 

/*

  2 在把CDC增加到CDC_TEST_DB数据库的表中,首先应该验证数据库是否启用了数据

    捕获

*/

 

    --验证是否开启

    SELECT IS_CDC_ENABLED FROM SYS.databases WHERE name='CDC_TEST_DB'

    /*

    IS_CDC_ENABLED

    --------------

    0

 

    (1 行受影响)

    */

   

    --启用数据变更,在CDC_TEST_DB数据库中执行SYS.SP_CDC_ENABLE_DB存储过程:

   

    USE CDC_TEST_DB

    GO

   

    EXEC sys.SP_CDC_ENABLE_DB

    GO

   

    /*

      CDC_TEST_DB开启数据捕获后,你将会在CDC_TEST_DB->安全性->架构下面看到新

      建立了一个CDC架构.

     

      同样在CDC_TEST_DB会建立一些CDC架构的下的系统表:

       cdc.captured_columns

       返回指定的跟踪列

      

       cdc.change_tables

       返回启用CDC的表.使用sys.sp_cdc_help_change_data_capture比直接查询好.

      

       cdc.ddl_history

       返回每个表再启用CDC后的DDL变更.可以使用sys.sp_cdc_get_ddl_history代替查询该表.

      

       cdc.index_columns

       返回启用CDC的表的相关索引列.同样用sys.sp_cdc_help_change_data_capure来获取比较好.

      

       cdc.lsn_time_mapping

       为每个在更改表中存在行的事务返回一行.该表用于在日志序列号(LSN) 提交值和提交事务的时间之间建立映射.

       要避免直接查询该表,使用sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn函数.

        

    */

   

    --下面再验证看看是否已经开启数据库开启

    SELECT IS_CDC_ENABLED FROM SYS.databases WHERE name='CDC_TEST_DB'

   

    /*

    IS_CDC_ENABLED

    --------------

    1

 

    (1 行受影响)

    */

 

/*

  3 开启数据捕获之后,可以在数据库中通过使用SYS.SP_CDC_ENABLE_TABLE系统SP对表进行

     变更捕获.SP描述如下

       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' ]

       [,[ @allow_partition_switch = ] 'partition_switch' ]

   

    @source_schema :

    是源表所属的架构的名称.source_schema 的数据类型为sysname，无默认值，并且

    不能为NULL.

 

    @source_name :

    是对其启用变更数据捕获的源表的名称.source_name 的数据类型为sysname，无默

    认值，并且不能为NULL.   source_name 必须存在于当前数据库中.不能对cdc 架构

    中的表启用变更数据捕获.

 

    @role_name :

    是用于控制更改数据访问的数据库角色的名称.role_name 为sysname，并且必须指定.

    如果显式设置为NULL，则没有控制角色用于限制对更改数据的访问.如果当前存在该角

    色，则使用它.如果不存在该角色，则会尝试创建具有指定名称的数据库角色.在尝试创

    建该角色之前，将删除角色名称字符串右侧的空格.如果调用方无权在数据库中创建角色，

    则存储过程操作将失败.

 

    @capture_instance :

    是用于命名特定于实例的变更数据捕获对象的捕获实例的名称.capture_instance 为

    sysname，并且不能为NULL.源表最多可以有两个捕获实例.

 

    @supports_net_changes :

    指示是否对此捕获实例启用净更改查询支持.supports_net_changes 为bit，如果此表有

    主键，或者有已使用@index_name 参数进行标识的唯一索引，则此参数的默认值为1.否

    则，此参数默认为0.如果为0，则只生成查询所有更改的支持函数.如果为1，则还会生

    成查询净更改所需的函数.

 

    如果将supports_net_changes 设置为1，则必须指定index_name，或者源表必须具有已

    定义的主键.

 

    @index_name :

    用于唯一标识源表中的行的唯一索引的名称.index_name 为sysname，并且可以为NULL.如

    果指定，则index_name 必须是源表的唯一有效索引.如果指定index_name，则标识的索引列

    优先于任何定义的主键列，就像表的唯一行标识符一样. 

   

    @captured_column_list :

    标识将包括在更改表中的源表列.captured_column_list 的数据类型为nvarchar(max)，并且

    可以为NULL.如果为NULL，则所有列都将包括在更改表中.

 

    @filegroup_name : 这个选项允许指定CDC的数据存储到哪里.filegroup_name 为sysname，并且

    可以</