SQL SEVER CDC的启动和关闭操作说明详解

什么是变动数据捕获 (CDC)？

变动数据捕获使用 SQL Server 代理记录表中发生的插入、更新及删除。 因此，它使得可以通过关系格式轻松使用这些数据更改。 将为修改的行捕获将这些更改数据应用到目的环境所需的列数据和根本元数据，并将其存储在镜像所跟踪源表的列结构的更改表中。 别的，表值函数可供使用者系统访问此更改数据。
开启CDC

1.前置条件

sqlsever 2008以上版本
必要开启代理服务（作业）
表必须要有主键大概是唯一索引
2.开启CDC

2.1 开启数据库CDC

1. -- Enable Database for CDC
2. EXEC sys.sp_cdc_enable_db

复制代码

查询CDC状态

1. ---dbname为数据库名称，返回结果1表示开启
2. select is_cdc_enabled from sys.databases where name='dbname'

复制代码

2.2开启代理服务

1. --开启SQL server agent服务（逐条执行）
2. sp_configure 'show advanced options', 1;
3. GO
4. RECONFIGURE;
5. GO
6. sp_configure 'Agent XPs', 1;
7. GO
8. RECONFIGURE
9. GO

复制代码

2.3添加CDC文件组和文件

1. ---添加文件组
2. ALTER DATABASE dbname ADD FILEGROUP CDCGroup;
3. ---向文件组添加文件
4. ALTER DATABASE dbname
5. ADD FILE
6. (
7. NAME= 'HospitalInterfaceDb_CDC',
8. FILENAME = 'E:\SQLSERVER_DATAs\HospitalInterfaceDb_CDC.ndf'
9. )
10. TO FILEGROUP CDCGroup;
11. ---查询db的物理文件，不清楚物理存储路径的可以先查询，特别说明，当删除了物理文件，这个查询仍会有记录直到下一次DB进行备份才会更新
12. SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');

复制代码

2.4开启表CDC

1. IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
2. BEGIN
3. EXEC sys.sp_cdc_enable_table
4. @source_schema = 'dbo',
5. @source_name = 'table_name', -- table_name
6. @capture_instance = NULL, -- capture_instance 可以为NULL
7. @supports_net_changes = 1, -- supports_net_changes
8. @role_name = NULL, -- role_name
9. @index_name = NULL, -- index_name
10. @captured_column_list = NULL, -- captured_column_list
11. @filegroup_name = 'CDCGroup' -- filegroup_name
12. END; -- 开启表级别CDC
13. --查询表CDC状态
14. 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名，这里就是捕获的修改日记
其中：

1. __$start_lsn列：保存其事务日志的开始序列号（LSN）,可以通过函数sys.fn_cdc_map_lsn_to_time(__$start_lsn) 转换为时间；
2. __$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；

复制代码

2.6 CDC 设置

1. --查看CDC 作业配置
2. sys.sp_cdc_help_jobs

复制代码

maxtrans:捕获作业每次循环时要处置惩罚的最大事件数
maxscans:每次循环数
continuous:1:连续运行，0：间隔运行
rerention:变动保留时长，单位是（分钟）
可以通过执行语句调整时长、执行次数等参数:

1. EXECUTE sys.sp_cdc_change_job @job_type = N'',      -- nvarchar(20)
2.                               @maxtrans = 0,        -- int
3.                               @maxscans = 0,        -- int
4.                               @continuous = NULL,   -- bit
5.                               @pollinginterval = 0, -- bigint
6.                               @retention = 0,       -- bigint
7.                               @threshold = 0        -- bigint

复制代码

关闭CDC

1. -- 关闭数据库CDC,CDC 关闭后相关表会自行删除
2. EXEC sys.sp_cdc_disable_db
3. --删除文件和文件组
4. ALTER DATABASE dbname REMOVE FILE file_name
5. 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/

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。