本文详细介绍了如何在SQLServer中启用变更数据捕获(CDC)，包括设置前置条件、开启数据库和表级CDC、配置捕获作业参数，以及如何查询和关闭CDC。还提供了相关链接供进一步学习。
摘要由CSDN通过智能技术生成
什么是变更数据捕获 (CDC)？
变更数据捕获使用 SQL Server 代理记录表中发生的插入、更新及删除。 因此，它使得可以通过关系格式轻松使用这些数据更改。 将为修改的行捕获将这些更改数据应用到目标环境所需的列数据和基本元数据，并将其存储在镜像所跟踪源表的列结构的更改表中。 此外，表值函数可供使用者系统访问此更改数据。
开启CDC
1.前置条件
sqlsever 2008以上版本
需要开启代理服务（作业）
表必须要有主键或者是唯一索引
2.开启CDC
2.1 开启数据库CDC
-- Enable Database for CDC
EXEC sys.sp_cdc_enable_db
查询CDC状态
---dbname为数据库名称，返回结果1表示开启
select is_cdc_enabled from sys.databases where name='dbname'
2.2开启代理服务
--开启SQL server agent服务（逐条执行）
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Agent XPs', 1;
RECONFIGURE
2.3添加CDC文件组和文件
---添加文件组
ALTER DATABASE dbname ADD FILEGROUP CDCGroup;
---向文件组添加文件
ALTER DATABASE dbname
ADD FILE
NAME= 'HospitalInterfaceDb_CDC',
FILENAME = 'E:\SQLSERVER_DATAs\HospitalInterfaceDb_CDC.ndf'
TO FILEGROUP CDCGroup;
---查询db的物理文件，不清楚物理存储路径的可以先查询，特别说明，当删除了物理文件，这个查询仍会有记录直到下一次DB进行备份才会更新
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');
2.4开启表CDC
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'table_name', -- table_name
@capture_instance = NULL, -- capture_instance 可以为NULL
@supports_net_changes = 1, -- supports_net_changes
@role_name = NULL, -- role_name
@index_name = NULL, -- index_name
@captured_column_list = NULL, -- captured_column_list
@filegroup_name = 'CDCGroup' -- filegroup_name
END; -- 开启表级别CDC
--查询表CDC状态
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('table_name')
2.5 CDC表格说明
开启之后会在作业里面生成对应的_capture和_cleanup作业，表值函数会新增实例计算函数，系统表会添加CDC相关表格
cdc.change_tables：表开启cdc后会插入一条数据到这张表中，记录表一些基本信息
cdc.captured_columns：开启cdc后的表，会记录它们的字段信息到这张表中
[cdc].[dbo_ORTT_CT]： ORTT是table名，这里就是捕获的修改日志
其中：
__$start_lsn列：保存其事务日志的开始序列号（LSN）,可以通过函数sys.fn_cdc_map_lsn_to_time(__$start_lsn) 转换为时间；
__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；
2.6 CDC 配置
--查看CDC 作业配置
sys.sp_cdc_help_jobs
maxtrans:捕获作业每次循环时要处理的最大事务数
maxscans:每次循环数
continuous:1:连续运行，0：间隔运行
rerention:变更保留时长，单位是（分钟）
可以通过执行语句调整时长、执行次数等参数:
EXECUTE sys.sp_cdc_change_job @job_type = N'', -- nvarchar(20)
@maxtrans = 0, -- int
@maxscans = 0, -- int
@continuous = NULL, -- bit
@pollinginterval = 0, -- bigint
@retention = 0, -- bigint
@threshold = 0 -- bigint
关闭CDC
-- 关闭数据库CDC,CDC 关闭后相关表会自行删除
EXEC sys.sp_cdc_disable_db
--删除文件和文件组
ALTER DATABASE dbname REMOVE FILE file_name
ALTER DATABASE dbname REMOVE FILEGROUP group_name
相关阅读：
1、https://blog.wanwuguiyi.com/db-sql-batch-save/
2、https://blog.wanwuguiyi.com/sql-batch-insert/
3、https://blog.wanwuguiyi.com/sql-server-quoted-identifier/
4、https://blog.wanwuguiyi.com/union-unionall-study02/
1.开启cdc
-- 查看是否开启cdc
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDCTEST'
-- 开启数据库级别cdc
exec sys.sp_cdc_enable_db
-- 开启表级别cdc
-- 表需有主键或唯一约束 ， 为表添加主键：
ALTER TABLE CDCTEST.prices
ADD CONSTRAINT PK_CDCTEST_PRICES
SQLServer中开启CDC之后，在某些情况下会导致事务日志空间被占满的现象为：
在执行增删改语句（产生事务日志）的过程中提示，The transaction log for database ‘***’ is full due to ‘REPLICATION'(数据库“***”的事务日志已满，原因为“REPLICATION”).
CDC以及复制的基本原理粗略地讲，对于日志的使用步骤如下：
1，每当基础表（开启了CDC或者replication的表）产生事务性操作（增删改）之后，对应的事务日志写入日志文件，
2，此时的日志被状态被标记为Replication，也即处于待复制状态，这个
--查询数据库是否启用CDC：1 成功； 0 失败
select name,is_cdc_enabled from sys.databases where name=db_name;
--开启库级别CDC:
EX...
