SQL Server数据库开启CDC变更数据捕获操作指引

目录

目录

前言：

1、开启代理服务器

1.1、开启前

​编辑

1.2、执行开启命令

1.3、开启后

1.4、手动启动代理服务

2、检查和开启数据库的CDC服务

2.1、查询数据库的CDC开启状态

2.2、开启数据库级别的CDC功能

3、添加CDC专用的文件组和文件

4、操作开启表级别CDC（注意：表中必须有主键或者唯一索引）

4.1、查看已开启CDC的表

4.2、开启表级别CDC（表中必须要有主键或唯一索引）

5、验证是否成功开启CDC

附加命令

温馨提示（踩坑记录）：

1、Linux下安装的数据库执行报错：SQLServerAgent is not currently running so it cannot be notified of this action

2、如果发现下图的提示：

3、如果是Windows端的SQL Server，并且电脑重启了

---

前言：

1、开启代理服务器

1.1、开启前

​编辑

1.2、执行开启命令

1.3、开启后

1.4、手动启动代理服务

2、检查和开启数据库的CDC服务

2.1、查询数据库的CDC开启状态

2.2、开启数据库级别的CDC功能

3、添加CDC专用的文件组和文件

4、操作开启表级别CDC（注意：表中必须有主键或者唯一索引）

4.1、查看已开启CDC的表

4.2、开启表级别CDC（表中必须要有主键或唯一索引）

5、验证是否成功开启CDC

附加命令

温馨提示（踩坑记录）：

---

前言：

        我们在同步SQL Server数据到MySQL或者将SQL Server应用在大数据领域或者其他场景时需要实时监控源数据库的数据变更，那么SQL Server的CDC就能派上用场了，这篇文章是我对SQL Server CDC使用的一些操作作为自己的随记，也许能够帮到你，后续我会介绍如何在代码中应用CDC。

        如果你的SQL Server使用的是Linux版本的，该方案同样适用，若想使用SSMS就需要用一台Windows机去远程连接你的SQL server了。

1、开启代理服务器

1.1、开启前

1.2、执行开启命令

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

Go

sp_configure 'Agent XPs',1;

Go

RECONFIGURE;

Go

1.3、开启后

1.4、手动启动代理服务

-- 查看sqlserver代理的进程是否启用（如果显示stoped(需手动启动)，则需启动SQL Server代理）

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT'

可右击“SQL Server代理”点击启动手动启动代理；

2、检查和开启数据库的CDC服务

2.1、查询数据库的CDC开启状态

select is_cdc_enabled from sys.databases where name='HM_5001';

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

2.2、开启数据库级别的CDC功能

-- 开启数据库CDC

ALTER AUTHORIZATION ON DATABASE::[HM_5001] TO [sa];

if exists(select 1 from sys.databases where name='HM_5001' and is_cdc_enabled=0)

begin

    exec sys.sp_cdc_enable_db

end

;

-- 查询数据库是否开启CDC
select is_cdc_enabled from sys.databases where name='HM_5001';

3、添加CDC专用的文件组和文件

-- 查询已存在的文件
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('HM_5001');

-- 添加CDC文件组和文件
ALTER DATABASE HM_5001 ADD FILEGROUP CDC1;

ALTER DATABASE HM_5001
ADD FILE
(
  NAME= 'HM_5001_CDC1',

  FILENAME = 'D:\DATA\HM_5001_CDC1.ndf',

  SIZE = 100MB
)
TO FILEGROUP CDC1;

4、操作开启表级别CDC（注意：表中必须有主键或者唯一索引）

4.1、查看已开启CDC的表

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1;

4.2、开启表级别CDC（表中必须要有主键或唯一索引）

-- 开启t1表的CDC

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

备注：多张表依次替换“t1”继续执行即可

开启后可看到下图的内容：

 每开启一张表的CDC就会在这里新增一个 cdc.dbo_表名_CT 的表，这个表中就是记录表数据表更的表。

如果有数据的插入、更新、删除会在这里记录。

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

对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）

但是微软不建议直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_<捕获实例> 和cdc.fn_cdc_get_net_changes_<capture_instance>  来查询。

5、验证是否成功开启CDC

-- 查看哪些表开启了CDC
select name, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = '1';

-- 查看指定表是否开启了CDC
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('dbo.t1');

附加命令（关闭CDC）

-- 关闭表(“dbo.t1”)的CDC
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 't1', @capture_instance = 'all';

-- 禁用数据库所有实例CDC
EXEC sys.sp_cdc_disable_db;

-- 查看CDC的job
EXEC sys.sp_cdc_help_jobs
GO

温馨提示（踩坑记录）：

1、Linux下安装的数据库执行报错：SQLServerAgent is not currently running so it cannot be notified of this action

或者

我们看下官方文档（点击进入）：

找到原因是Linux中hostname的问题，Linux版的SqlServer 2019的SqlServer 代理需要用到Linux的hostname，并且只使用hostname的前15位。

代理登录时,由于我这个是新的服务器,hostname已经超过了15个字符,而代理登录时只截取了15字符,导致账户名不匹配,找到问题,那么就解决问题

解决方案1：永久修改hostname（需要重启服务器）

第一步:

vim /etc/hostname

#把你那跟油腻大叔大裤衩子似的name改成不超过15个字符的name

Esc
:wq 回车

第二步：

重启Linux服务器，重启SQL Server

搞定

解决方案2：临时修改（Linux重启后失效）

直接在Linux执行命令：

hostname 你新的名称

使用hostname命令查看是否修改成功

修改完以后再根据微软SqlServer官方文档重启Linux SqlServer服务即可。

2、如果发现下图的提示：

已以用户 XXX 的身份执行。无法为数据库XX 的捕获实例删除一个或多个低水位表计发生变化而废弃的更改表项。在执行命令 'delete top(@p1) from [dbo.XXX] where _$start_lsn < @p2'。 请重新运行该事务。。。

这时就应该意识到产生死锁了

可尝试使用下面的解决办法：

-- ----------对作业的更改------------------ 

EXEC sys.sp_cdc_change_job
 @job_type = 'capture' 
 ,@maxtrans = 1000      --每个扫描循环可以处理的最多事务数 
 ,@maxscans = 10        --为了从日志中提取所有行而要执行的最大扫描循环次数 
 ,@continuous = 1       --连续运行最多处理(max_trans * max_scans)个事务 
 ,@pollinginterval = 5 
 ;
 
 EXEC sys.sp_cdc_change_job  
 @job_type = 'cleanup' 
 ,@retention = 4320     --更改行将在更改表中保留的分钟数
 ,@threshold = 5000     --清除时可以使用一条语句删除的删除项的最大数量

 -- ----上述对作业的更改，更改后需重启作业----- 
 EXEC sys.sp_cdc_stop_job @job_type = N'capture'; 
 EXEC sys.sp_cdc_start_job @job_type = N'cleanup'; 

-- 执行命令：
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@threshold=2000
GO

-- 把threshold设置为2000。

--- 重启作业：

EXEC sys.sp_cdc_stop_job @job_type = N'capture'; 
EXEC sys.sp_cdc_start_job @job_type = N'cleanup'; 

-- 完成

--查看作业

EXEC sys.sp_cdc_help_jobs
GO

3、如果是Windows端的SQL Server，并且电脑重启了

如果是这样：[Microsoft[ODBC Driver 17 for SQL Server[SQL Serverl在文件D:DATAHM 5002 CDC1.ndf 中、偏移量为0x00000000012000 的位置执行 读取 期间，操作系统已经 SQL Server 返回了错误 21设备未就绪。。 SQL Server 错误日志和系统事件日志中的其他消息可能提供了更详细信息。这是一个威胁数据库完整性的严重系统级错误条件，必须立即纠正。请执行完整的数据库一致性检查DBCC CHECKDB)。此错误可以由许多因素导致，有关详细信息，请参阅 SQL Server 联机丛书。(823)

 或者查看job的执行步骤时这样：

发现问题描述：

第一种情况：打开电脑后数据库打开无法查看，点击表后提示上述错误；

第二种情况：表可以正常查看，但是CDC下的表打开提示上述错误；

究其原因--“电脑重启了：这是因为数据库没有挂起的原因（由于硬盘等移动而导致的）”

挂起（等待，阻塞）进程在操作系统中可以定义为暂时被淘汰出内存的进程，机器的资源是有限的，在资源不足的情况下，操作系统对在内存中的程序进行合理的安排，其中有的进程被暂时调离出内存，当条件允许的时候，会被操作系统再次调回内存，重新进入等待被执行的状态即就绪态，系统在超过一定的时间没有任何动作。

解决办法：

方法1：

win+R打开命令行窗口，输入services.msc 回车，打开服务，将SQL Server服务重启，然后重新打开数据库软件即可。包括数据库服务和代理服务，因为代理服务依赖于数据库服务。

方法2：

1）、在操作系统的任务栏中单击“开始”菜单，选择“运行”命令，在下拉列表框中输入“cmd”命令，单击“确定”按钮。

2）、在cmd窗口中，停止并重启SQL Server服务。

停止SQL Server

NET STOP MSSQLSERVER

启动SQL Server

NET START MSSQLSERVER

重新连接数据库即可。