介绍

每个使用过SQL SERVER的开发人员迟早都会遇到这个问题，在执行任何DML操作之前，它都必须复制一个行，复制它的表通常被标记为“tablename_history”或“tablename_backup”，这是通过在存储过程或触发器中写入insert查询来实现的。

最近，我在SQL SERVER中偶然发现了一个名为Change Data Capture（简称CDC）的系统函数，默认情况下，该函数异步执行上述功能（如果已启用），并且所有高于SQL SERVER 2008的版本都支持该函数。

启用CDC

要实现CDC，我们首先需要在数据库上启用CDC，这是通过执行存储过程来实现的。”sys.sp_cdc_enable_db“如下所示。

-- To Enable CDC  
USE [CDC_TEST]  
GO  
EXEC sys.sp_cdc_enable_db  
GO

现在要在表上启用CDC，我们需要执行存储过程“sys.sp_cdc_enable_table“其输入参数如下所示

USE [CDC_TEST]  
EXEC sys.sp_cdc_enable_table     
  @source_schema = 'dbo', -- Is the name of the schema to which the source table belongs.  
  @source_name = 'Customer', -- Is the name of the source table on which to enable change data capture    
  @role_name     = NULL -- Is the name of the database role used to gate access to change data, we can mention null if we want all the users having access to the database to view the CDC data

一旦存储过程成功执行，就会在System Tables文件夹下生成一个模式为“cdc”的表。

这些表格包括以下内容：

cdc.captured_columns：包含捕获列列表的表
cdc.change_tables：包含允许捕获的表列表的表
cdc.ddl_history：记录自启用捕获数据以来所有DDL更改的历史记录表
cdc.index_columns：包含与change table相关联的所有索引的表
cdc.lsn_time_mapping：用于将LSN号与时间映射，最后为每个支持CDC的表创建一个更改表，用于捕获源表上的DML更改
cdc.dbo_Customer_CT：包含在执行任何DML操作之前的实际数据和一些额外的元数据，如操作、受影响的列数等。根据应用CDC的主表的名称，表的名称可能有所不同，但通常它将为“NameOfSchema_TableName_CT”，因此名称为“dbo_Customer_CT”。

在这些表中，还为下面给出的两个SQL代理作业创建

cdc.CDC_TEST_capture任务负责将DML更改推入更改表
cdc.CDC_TEST_cleanup任务负责清除更改表中的记录。这个作业是由SQL Server自动创建的，以减少更改表中的记录数量，如果执行这个作业失败，将导致一个更大的更改表。

检测更改

现在我们已经在数据库和表上实现了CDC，让我们执行下面给出的一些DML操作

INSERT INTO [dbo].[Customer]  
           ([CustName]  
           ,[CustMobNo]  
           ,[Address]  
           ,[SubAreaId])  
     VALUES  
           ('test cdc'  
           ,'9876543215'  
           ,'Home Address'  
           ,1)  
  
UPDATE [dbo].[Customer]  
SET  
    CustName = 'test cdc 2',  
    CustMobNo = '9876543216',   
    [Address] = 'Address updated',  
    SubAreaId = 2   
WHERE CustId = 1  
  
DELETE [dbo].[Customer] WHERE CustId = 1

执行的DML查询的结果填充在表[cdc].[dbo_Customer_CT]中，表如下图所示：

前5列是更新行的元数据。列'__$operation'是重要的，因为列是用来识别DML操作的。

__$operation = 1：表示删除的行

__$operation = 2：表示新插入的行

__$operation = 3：表示更新前的行

__$operation = 4：表示更新后的行

但是Microsoft不建议查询cdc表，因此我们必须使用在表上启用cdc时创建的表值函数。在本例中，我们有一个名为“fn_cdc_get_all_changes_dbo_Customer”的表值函数，可以如下所示使用

DECLARE @from_lsn binary (10), @to_lsn binary (10)  
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Customer') -- scheme name with table  
SET @to_lsn = sys.fn_cdc_get_max_lsn()  
  
SELECT *  
FROM cdc.[fn_cdc_get_all_changes_dbo_Customer](@from_lsn, @to_lsn, 'all')  
ORDER BY __$seqval

禁用CDC

一旦CDC被启用，我们就不能更改表的主键，截断表，如果我们必须添加或删除列，相应的CD表就不会更新，因此不会检测到新添加列的任何更改。在这些情况下，我们将不得不禁用CDC并进行适当的更改，然后在表上重新启用CDC。下面是一个存储过程，可用于删除表上的CDC。

EXEC sys.sp_cdc_disable_table     
  @source_schema = 'dbo' ,     
  @source_name = 'Customer',  
  @capture_instance ='all'

注意

SQL代理应该一直处于启动和运行状态
cdc_job配置的设置非常重要正确。高估/低估这些配置将对应用程序性能产生不利影响。您可能需要根据您的工作负载进行真正的配置，可以根据您的工作负载进行性能测试以达到最佳值
默认情况下，清理作业安排在每天凌晨02:00运行
捕获作业计划为“在SQL Server代理启动时自动启动”。由于它进一步使用了连续参数，您可能不需要对“Schedule type”进行任何更改。

玖叶教程网

前端编程开发入门

SQL Server中的更改数据捕获(CDC)

介绍

启用CDC

检测更改

禁用CDC