SQL server CDC功能

SQL server 字段类型调整语句：

-- 更改字段类型长度
alter table 表
alter column 字段名 类型长度

-- 更改字段类型
alter table 表
alter column 字段名 更改后的类型

-- 添加非空约束
alter table 表
alter column 字段名 int not null

-- 设置主键
alter table 表
add constraint 主键名 primary key（字段名）

-- 删除主键
alter table 表
drop constraint 主键名

-- 更改字段名
exec sp_rename '表名.字段名',
'更改后的字段名','COLUMN'（表示修改类型是字段）,-- 注意COLUMN一定要大写

-- 添加字段名
alter table 表 
add 字段名 字段类型 default null

1.查看数据库是否开启cdc

2.启用数据库cdc功能

报错：精简版无法启用cdc功能

> Msg 22988, Level 16, State 1, Server DESKTOP-GFUG8MF, Procedure sp_cdc_enable_db, Line 12
此 SQL Server 实例为 Express Edition with Advanced Services (64-bit)。变更数据捕获仅在 Enterprise Edition、Developer Edition 和 Enterprise Evaluation Edition 中可用。
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]此 SQL Server 实例为 Express Edition with Advanced Services (64-bit)。变更数据捕获仅在 Enterprise Edition、Developer Edition 和 Enterprise Evaluation Edition 中可用。 (22988)

================================

更换版本为Enterprise Edition

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
	Apr  2 2010 15:48:46 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

2.启用数据库cdc功能

3.开启表的CDC功能

需要开启SQLServerAgent 服务

source_schema:被监听的数据库schema

source_name:被监听的表名

capture_instance:默认为表名，可自定义

capture_columen_list:监听的列，NULL即全部字段

生成默认的shcema和表

schema：cdc

表名：capture_instance_CT

4.检查

5.修改测试表数据

6.查询变更内容

__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；

7.修改表结构

新加的字段不会显示在捕获信息中

8.修改字段类型

正常

9.增加/删除主键

无法添加

10.按时间段进行查询

官方地址：SQL Server | Microsoft Learn

从cdc.fn_cdc_get_all_changes_capture_instance表中查询，对该表进行查询时，必须指定参数：

cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' )  
  
<row_filter_option> ::=  
{ all | all update old }
  • from_lsn

LSN 值，它表示要包含在结果集中的 LSN 范围的低端点。 from_lsn 是 二进制 (10) 。

仅 cdc 中的行capture_instance_CT更改表的值以 __$start_lsn 大于或等于 from_lsn 包含在结果集中。

  • to_lsn

LSN 值，它表示要包含在结果集中的 LSN 范围的高端点。 to_lsn 是 二进制 (10) 。

仅 cdc 中的行capture_instance_CT更改表，其值为 __$start_lsn 大于或等于 from_lsn 且小于或等于 to_lsn 包含在结果集中。

  • <> row_filter_option ：：= { all | all update old }

控制元数据列的内容和结果集中所返回的行的选项。

可以是下列选项之一：

    • all
      返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改，此选项只返回在应用更新之后包含新值的行。
    • all update old
      返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改，此选项将返回在更新之前包含列值的行和更新之后包含列值的行。

示例：

-- 查询1
DECLARE @FromLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2023-01-16 09:00:30');

DECLARE @ToLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2023-01-18 23:59:59');

SELECT CASE [__$operation]
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,
[__$operation],
[__$update_mask],
id,name,sex,age
FROM [cdc].[fn_cdc_get_all_changes_dbo_stuInfo]
(@FromLSN, @ToLSN,  'all update old')

-- 查询2
SELECT 
[__$operation],
[__$update_mask],
id,name,sex,age
FROM [cdc].[fn_cdc_get_all_changes_dbo_stuInfo]
(sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2023-01-16 09:00:30'),
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2023-01-18 23:59:59'),
'all update old')

11.日志清理

查看默认生成的作业

参数含义参考 sys.sp_cdc_help_jobs

retention:保留时间

threshold:每次清理数量

EXEC sys.sp_cdc_help_jobs

GO

查看编辑作业执行计划

posted @ 2023-02-09 09:41  CHEN_zu_he  阅读(612)  评论(0)  编辑  收藏  举报