介绍

每个使用过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“如下所示。

--ToEnableCDC
USE[CDC_TEST]
GO
EXECsys.sp_cdc_enable_db
GO

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

USE[CDC_TEST]
EXECsys.sp_cdc_enable_table
@source_schema='dbo',--Isthenameoftheschematowhichthesourcetablebelongs.
@source_name='Customer',--Isthenameofthesourcetableonwhichtoenablechangedatacapture
@role_name=NULL--Isthenameofthedatabaseroleusedtogateaccesstochangedata,wecanmentionnullifwewantalltheusershavingaccesstothedatabasetoviewtheCDCdata

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

sql更改数据,sqlserver更改在线监测数据

这些表格包括以下内容：

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操作

INSERTINTO[dbo].[Customer]
([CustName]
,[CustMobNo]
,[Address]
,[SubAreaId])
VALUES
('testcdc'
,'9876543215'
,'HomeAddress'
,1)

UPDATE[dbo].[Customer]
SET
CustName='testcdc2',
CustMobNo='9876543216',
[Address]='Addressupdated',
SubAreaId=2
WHERECustId=1

DELETE[dbo].[Customer]WHERECustId=1

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

sql更改数据,sqlserver更改在线监测数据

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

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

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

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

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

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

DECLARE@from_lsnbinary(10),@to_lsnbinary(10)
SET@from_lsn=sys.fn_cdc_get_min_lsn('dbo_Customer')--schemenamewithtable
SET@to_lsn=sys.fn_cdc_get_max_lsn()

SELECT*
FROMcdc.[fn_cdc_get_all_changes_dbo_Customer](@from_lsn,@to_lsn,'all')
ORDERBY__$seqval

禁用CDC

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

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

注意

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

sql更改数据 (sqlserver开启cdc的前提)

介绍

启用CDC

检测更改

禁用CDC