SQL SERVER 开启CDC 实操详细

^{_{what CDC？}}

变更数据捕获（Change Data Capture ，简称 CDC）记录 SQL Server 表的插入、更新和删除活动。SQLServer的操作会写日志，这也是CDC捕获数据的来源。

开启CDC检测

　　1.开启的必要条件

- sqlsever 2008以上版本
- 需要开启代理服务（作业）
- 磁盘要有足够的空间，保存日志文件
- 表必须要有主键或者是唯一索引

2.开启数据库CDC

2.1、查询数据库的CDC查询状态（db_name：数据库名）

select is_cdc_enabled from sys.databases where name='db_name'

　　　　查询结果为“1”，表示开启成功

--开启SQL server agent服务
sp_configure 'show advanced options', 1;
GO -- 2.1.1
RECONFIGURE;
GO -- 2.1.2
sp_configure 'Agent XPs', 1;
GO -- 2.1.3
RECONFIGURE
GO -- 2.1.4

2.2、开启、关闭数据库CDC

1 --开启数据库CDC
2 if exists(select 1 from sys.databases where name='HospitalInterfaceDb' and is_cdc_enabled=0)
3 begin
4     exec sys.sp_cdc_enable_db--开启数据库CDC
5 END
6 
7 -- 关闭数据库CDC  
8 EXEC sys.sp_cdc_disable_db

　　 2.3、开启表CDC 　　*注意：表中必须有主键或者唯一索引

--添加CDC专用的文件组和文件
-- 2.3.1 查询dbname库的物理文件
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('db_name');

ALTER DATABASE db_name ADD FILEGROUP CDC1;
-- 2.3.2 为该库添加名为CDC1的文件组
ALTER DATABASE db_name
ADD FILE
(
NAME= 'HospitalInterfaceDb_CDC1',
FILENAME = 'C:\WorkFIles\MSSQL_CDC\HospitalInterfaceDb_CDC1.ndf'
)
TO FILEGROUP CDC1;
-- 2.3.3 将新增文件，并映射到文件组。重复2.3.1查询操作

 1 --操作开启表CDC
 2 IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
 3 BEGIN
 4 EXEC sys.sp_cdc_enable_table
 5 @source_schema = 'dbo', -- source_schema
 6 @source_name = 'table_name', -- table_name
 7 @capture_instance = 'test_instance', -- capture_instance
 8 @supports_net_changes = 1, -- supports_net_changes
 9 @role_name = NULL, -- role_name
10 @index_name = NULL, -- index_name
11 @captured_column_list = NULL, -- captured_column_list
12 @filegroup_name = 'CDC1' -- filegroup_name
13 END; -- 开启表级别CDC

1 --查看表开启CDC状态
2 select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('table_name')

2.5 、开启成功之后会存在对应的作业和函数

系统表：

cdc.change_tables：表开启cdc后会插入一条数据到这张表中，记录表一些基本信息

cdc.captured_columns：开启cdc后的表，会记录它们的字段信息到这张表中

cdc.dbo_VW_GHZDK_CT：记录VW_GHZDK表中所有变更的数据，字段“__$operation”为“1”代表删除，“2”代表插入，“3”执行更新操作前的值，“4”执行更新操作后的值。字段“__$start_lsn”由于更改是来源于数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）

函数：

cdc.fn_cdc_get_all_changes_dbo_VW_GHZDK：针对在指定日志序列号 (LSN) 范围内应用到源表的每项更改均返回一行。如果源行在该间隔内有多项更改，则每项更改都会表示在返回的结果集中

cdc.fn_cdc_get_net_changes_dbo_VW_GHZDK：针对指定 LSN 范围内每个已更改的源行返回一个净更改行。也就是说，如果在 LSN 范围内源行具有多项更改，则该函数将返回反映该行最终内容的单一行

sys.fn_cdc_map_time_to_lsn：为指定的时间返回 cdc.lsn_time_mapping 系统表中 start_lsn 列中的日志序列号 (LSN) 值。可以使用此函数系统地将日期时间范围映射到基于 LSN 的范围，以供变更数据捕获枚举函数 cdc.fn_cdc_get_all_changes_<capture_instance> 和 cdc.fn_cdc_get_net_changes_<capture_instance> 返回此范围内的数据更改。