开启 SQL Server 的 Change Data Capture, 并验证 CDC 开启后的效果

时间:2024-01-10 01:03:04 标签:  DataBase  数据仓库  数据库  大数据  

1. 什么是变更数据捕获 (CDC)

变更数据捕获 (CDC) 使用 SQL Server 代理来记录应用于表的插入、更新和删除活动。 这样,就可以按易于使用的关系格式提供这些更改的详细信息。将为修改的行捕获列信息以及将更改应用于目标环境所需的元数据,并将其存储在镜像所跟踪源表的列结构的更改表中。

系统提供了一些表值函数,以便使用者可以系统地访问更改数据。此技术针对的数据使用者的一个典型示例是提取、转换和加载 (ETL) 应用程序。ETL 应用程序以增量方式将 SQL Server 源表中的更改数据加载到数据仓库或数据市场。 虽然数据仓库中的源表的表示形式必须反映源表中的更改,但刷新源副本的端到端技术并不适用。 相反,您需要一种具有特定结构的可靠更改数据流,以便使用者可以将其应用于不同的目标数据表示形式。 SQL Server 变更数据捕获就提供了这一技术。

2.变更数据捕获的主体数据流

在这里插入图片描述
变更数据捕获的更改数据源为 SQL Server 事务日志。 在将插入、更新和删除应用于跟踪的源表时,将会在日志中添加说明这些更改的项。 日志用作捕获进程的输入来源。它会读取日志,并在跟踪的表的关联更改表中添加有关更改的信息。 系统将提供一些函数,以枚举在更改表中指定范围内发生的更改,并以筛选的结果集的形式返回该值。 通常,应用程序进程使用筛选的结果集在某种外部环境中更新源表示形式。

3.开启CDC的必要条件

3.1 SQL server 2008 以上的企业版、开发版和评估版中可用

在这里插入图片描述
版本检查:已安装的SQL Server版本为2019开发版

3.2 需要开启代理服务(作业)

在这里插入图片描述

3.3 CDC需要业务库之外的额外的磁盘空间,保存日志文件

3.4 表必须要有主键或者是唯一索引

在这里插入图片描述
已经设置数据库DB_Student的表tb_teacher的列teaid为主键。

4.检查和开启数据库的CDC服务

4.1 查询数据库的CDC服务状态

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

在这里插入图片描述

查询结果为0,表示尚未开启数据库DB_StudentCDC服务。

4.2 开启数据库级别的CDC功能

ALTER AUTHORIZATION ON DATABASE::[DB_Student] TO [sa];
if exists(select 1 from sys.databases where name='DB_Student' and is_cdc_enabled=0)
begin
    exec sys.sp_cdc_enable_db
end
;
select is_cdc_enabled from sys.databases where name='DB_Student';

在这里插入图片描述

结果为0,表示已经开启数据库DB_StudentCDC服务

5. 添加CDC专用的文件组和文件

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('DB_Student');
ALTER DATABASE DB_Student ADD FILEGROUP CDC1;
ALTER DATABASE DB_Student
ADD FILE
(
  NAME= 'DB_Student_CDC1',
  FILENAME = 'D:\DATA\DB_Student_CDC1.ndf'
)
TO FILEGROUP CDC1;

在这里插入图片描述

6. 操作开启表级别CDC(注意:表中必须有主键或者唯一索引)

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 0;
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tb_teacher' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = 'dbo', 
        @source_name = 'tb_teacher', 
        @capture_instance = NULL, 
        @supports_net_changes = 1,
        @role_name = NULL, 
        @index_name = NULL, 
        @captured_column_list = NULL, 
        @filegroup_name = 'CDC1' 
END;

DECLARE @tableName nvarchar(36) 
DECLARE My_Cursor CURSOR 
    FOR (SELECT 'new_srv_workorderBase' name
union select 'tablename1'
union select 'tablename2'
union select 'tablename3'
 ) 
OPEN My_Cursor; 
FETCH NEXT FROM My_Cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_cdc_enable_table
         @source_schema = 'dbo',
         @source_name = @tableName, 
         @capture_instance = NULL, 
         @supports_net_changes = 1, 
         @role_name = NULL, 
         @index_name = NULL, 
         @captured_column_list = NULL, 
         @filegroup_name = 'CDC1' 
    FETCH NEXT FROM My_Cursor INTO @tableName;
END
CLOSE My_Cursor; 
DEALLOCATE My_Cursor; 
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1 ORDER BY NAME;

在这里插入图片描述

7.验证是否成功开启CDC

7.1 查看tb_teacher表开启CDC状态结果为1,表示已经成功CDC服务。

select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('DB_Student')

在这里插入图片描述

7.2 成功开启数据库DB_StudentCDC服务后,在SQL Server代理—作业处有cdc.DB_Student_capturecdc.DB_Student_cleanup的作业

在这里插入图片描述

7.3 成功开启后CDC服务后,在数据库“DB_Student—可编程序—函数—表值函数”会有如下函数生成。

在这里插入图片描述
cdc.fn_cdc_get_all_changes_dbo_tb_teacher:针对在指定日志序列号 (LSN) 范围内应用到源表的每项更改均返回一行。如果源行在该间隔内有多项更改,则每项更改都会表示在返回的结果集中。
cdc.fn_cdc_get_net_changes_dbo_tb_teacher:针对指定 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> 返回此范围内的数据更改。

7.4 成功开启后CDC服务后,在数据库“DB_Student—表—系统表”会有如下表生成。

在这里插入图片描述
cdc.change_tables:表开启cdc后会插入一条数据到这张表中,记录表一些基本信息
cdc.captured_columns:开启cdc后的表,会记录它们的字段信息到这张表中
cdc.dbo_VW_GHZDK_CT:记录VW_GHZDK表中所有变更的数据,字段“__$operation”为“1”代表删除,“2”代表插入,“3”执行更新操作前的值,“4”执行更新操作后的值。字段“__$start_lsn”由于更改是来源于数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN)。

8. 验证CDC实现数据变更捕获的功能

8.1查询系统表cdc.dbo_tb_teacher_CT

 SELECT * FROM cdc.dbo_tb_teacher_CT

在这里插入图片描述

由查询结果可以看到系统表cdc.dbo_tb_teacher_CT没有任何记录。因为表刚刚创建,没有对原表dbo.tb_teacher做任何增删改操作。

8.2 向tb_teacher表插入记录和再次查询cdc.dbo_tb_teacher_CT

在这里插入图片描述
在这里插入图片描述
在对表tb_teacher插入数据之后,对系统表cdc.dbo_tb_teacher_CT进行查询操作,此时可以看到多了一条记录。结合7.4可以知道向数据表tb_teacher各插入和更新一条数据记录。

来源:https://blоg.сsdn.nеt/wеixin_51571728/аrtiсlе/dеtаils/126535938

智能推荐

1. 环境检查1.1 版本检查SELECT @@VERSION;Microsoft SQL Server 2016 (SP2-GDR)1.2 检查CDC服务开启状态select is_cdc_enabled from sys.databases where name=dbname;--0为关闭,1为开启。数据库名为dbname

标签:sql  Server  CDC  

一、需求背景&#xff1a;

标签:spring boot  Kafka  nio  java  

在el-element官网中&#xff0c;el-cascader开启多选模式的代码是这样的&#xf

标签:前端  elementui  vue.js  前端  javascript  elementui  

1.说明 有些时候我们希望计算机开机后就启动一些服务或应用程序。 2.开机启动 使用

标签:重启  服务器  计划  程序  项目  

什么是变更数据捕获 (CDC)?变更数据捕获使用 SQL Server 代理记录表中发生的插入、更新及删除。 因此,它使得可以通过关系格式轻松使用这些数据更改。 将为修改的行捕获将这些更改数据应用到目标环境所需的列数据和基本元数据,并将其存储在镜像所跟踪源表的列结构的更改表中。 此外,表值函数可供使用者系统访问此更改数据。开启CDC1.前置条件sqlsever 2008以上版本需要开启代理服务(作业)表必须要有主键或者是唯一索引2.开启CDC2.1 开

标签:操作  sql  SEVER  CDC  

因为报错被我解决了,所以没有截图,那我就打字吧无法启动调试的具体报错:&nbsp; &nbsp; &nbsp;弹出来的第一个框:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 无法启动调试&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

标签:无法启动  sql  Server  

1.使用redis-cli检查redis是否运行 如果redis配置文件正确无误&#

标签:springboot项目中遇到的问题  java  

三、Docker安装 Docker官方和国内daocloud都提供了一键安装的脚本&

标签:docker  容器  运维  

前言android 系统安全内容总结

标签:android系统安全  安全  android  android  系统安全  安全  

&#x1f31f;你是否对云安全领域充满热情&#xff1f;是否希望提升自己在云安全领域的专业性和竞

标签:CCSK  安全架构  安全性测试  安全威胁分析  web安全  大数据  python  java  

猜你喜欢

记录以下遇到的这个问题2021.4.29问题的起因还是在于hadoop安全模式的问题,我的安全模式之前出了问题,丢失了数据,导致我每次启动hadoop集群,都需要自己手动关闭hadoop的安全模式,于是在网上搜索,找到说可以先关闭集群,然后格式化namenode,按照这样的方式做了以后,就出现了标题的问题。解决方法其实也很简单因为格式化的问题,导致了主节点和从节点的clusterId不一致,所以才导致datanode没有启动成功进入到你的集群的current目录下去找VERSION这个文件

标签:节点  启动了  hadoop  DataNode  

一. 查看Visual Studio自带数据库相关信息 1. 打开Visual

标签:工具学习  数据库  visual studio  sqlserver  

General_log 详解 1.介绍 开启 gener

标签:mysql  general_log  

标签:linux  SSH  服务器  

1.下载地址:https://github.com/tporadowski/redis/releases,目前最新版本Redis 5.0.10 for Windows2.运行redis 命令如下:redis-server.exe&nbsp; redis.windows.conf3.将redis加入到windows的服务中(service和loglevel前都是两个-)开机自启动redis-server --service-install redis.windows-service.conf --loglevel verbose4.常用的redis服务命令。

标签:windows  redis  

MINIO介绍 MinIO 是在 Apache Licen

标签:java  docker  https  容器  

Step 1&#xff1a; 在google cloud 虚拟服务器环境下&#x

标签:系统  Server&amp;amp;Net  服务器  SSH  linux  运维开发  

进程开启 impor

标签:python  开发语言  爬虫  进程  flask  

要开启 Hadoop&#xff0c;你需要进入 Hadoop 的安装目录&#xff0c

标签:hadoop  hdfs  大数据  MapReduce  分布式  

一.什么是hive Hive是基于Hadoop的一个数据仓库工具&#xff

标签:hive  hadoop  数据仓库  

老孟导读:今天分享一个类似“孔雀开屏”的动画效果,打开新的页面时,新的页面从屏幕右上角以圆形逐渐打开到全屏。先来看下具体的效果

标签:孔雀  效果  开屏  动画  flutter  

最近因为项目原因需要在阿里云服务器上部署MongoDB&#xff0c;操作系统为Ubuntu&#xf

标签:MongoDB  数据库  

相关问题

相关文章

热门文章

推荐文章

相关标签