Flink-CDC 无法增量抽取SQLServer数据

1.问题

• 因部署在WindowsServer服务器SQLServer发生过期后重启，Flink-CDC同步进行作业重启，启动后无报错信息，数据正常抽取。但是观察几天后发现当天数据计算指标无法展示

2.定位

因为没用进行任何修改，故初步判断不是因Flink-CDC的问题导致,进而参照Flink-CDC文档检查以下配置

• 管理员用户登陆SQLServer进行检查，flink-cdc抽取数据用户涉及角色、权限配置是否发生变化

切换到对应数据 : use dbName
查看当前数据库用户、角色：
	EXEC sp_helprole
查看角色权限：
DECLARE @rolename sysname
SET @rolename = '角色名称'
EXEC sp_helprotect @rolename = @rolename

查看角色是否有CDC功能：
exec sp_helpsrvrolemember 'sysadmin'

• 事务日志配置信息

SELECT 
    name AS 'Logical Name',
    physical_name AS 'Physical Name',
    type_desc AS 'Type',
    state_desc AS 'State',
    size AS 'Size (MB)'
FROM sys.master_files
WHERE database_id = DB_ID('数据库名称')
    AND type_desc = 'LOG';

• 数据库、表级别CDC配置信息

数据库：
select is_cdc_enabled, name from sys.databases where name = '数据库名称'
数据表:
select name,is_tracked_by_cdc from sys.tables where name = '数据表名称'';

简洁方式:
		USE 数据库名称;
		EXEC sys.sp_cdc_help_change_data_capture

• 查看SQLServer 代理是否开启,需要管理员权限

SELECT * FROM sys.dm_server_services WHERE SERVICE_NAME = 'SQL Server Agent (MSSQLSERVER)';

或   
  SELECT servicename ,status,status_desc  FROM sys.dm_server_services WHERE servicename = 'SQL Server 代理 (MSSQLSERVER)'

• 查看数据库是否开启完整事务日志

#只有开启完整事务日志( FULL ) 存储，CDC才能进行捕获所有事物日志
select name ,recovery_model_desc from sys_databases where  name = '数据库名称'

3.原因

• 经定位，发现是SQLServer过期激活后，未开启SQLServer Agent服务
• 对于其他未正常配置问题，请参看官方文档进行配置
  https://nightlies.apache.org/flink/flink-cdc-docs-release-3.0/docs/connectors/legacy-flink-cdc-sources/sqlserver-cdc/

4.解决

• 进入WindowServer后，打开SQL Server Configuration Manager ，找到SQLServer配置管理器下的SQL Server服务，打开SQLServer Server 代理

5 SQLServer 开启CDC相关配置

5.1 创建用于承接CDC配置、日志的文件组

 use rytb;
GO
alter database rytb ADD filegroup rytb_cdc3_fg;
alter database rytb ADD FILE
(
NAME=DB_rytb03,
FILENAME="D:\Program Files (x86)\Microsoft SQL Server CDC\rytb_03.mdf",
size=50MB,
MAXSIZE=100GB,
FILEGROWTH=50MB
) TO FILEGROUP rytb_cdc03_fg;
GO
 

-- 查询是否参加成功
 
select name,type_desc from sys.filegroups where is_default=1 and data_space_id in (
 select data_space_id from sys.database_files where name = '数据库名称' )

5.2.开启数据库级别CDC配置

 -- 开启库级别CDC
use 【数据库名称】
GO
exec sys.sp_cdc_enable_db
GO 

-- 开启表级别CDC
use 【数据库名称】
GO
exec sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name=N'【数据表名称】',
@role_name=N'用户名',
@filegroup_name=N'上述创建文件组名称’
GO
 
-- 查看是否成功开启CDC
库级别
select is_cdc_enabled from sys.databases where name = 'rytb';

表级别
select name,is_tracked_by_cdc from sys.tables where name = '数据表名';

5.3 查看是否开启代理

SELECT * FROM sys.dm_server_services WHERE SERVICE_NAME = 'SQL Server Agent (MSSQLSERVER)';

或下面SQL(版本不同，SERVICENAME字段不同)

SELECT * FROM sys.dm_server_services WHERE SERVICENAME = 'SQL Server 代理 (MSSQLSERVER)';

或者登陆SQLServer配置管理器查看，未开启代理无法使用CDC捕获功能，需要开启

6 重新配置CDC

如果以上配置进行检查无误后，仍然无法进行增量捕获数据，则需要检查SQLServer CDC配置是否问题，这里遇到了类似CDC问题；此种情况需要重新禁用、启用CDC配置

• 数据库CDC服务故障，查看该数据库的系统表，查看CDC是否有下面错误
  
• 重新配置CDC

1.先禁用表级别 CDC
	use 数据库名称;
 	EXEC sys.sp_cdc_disable_db;
2.再禁用库级别CDC
use 【数据库名称】
 
GO   
exec sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name=N'数据表,
@capture_instance=N'数据库实例名称'
GO
3.开启库级别CDC
	use 数据库名称;
	exec  exec sys.sp_cdc_enable_db; 
4.开启表级别 CDC
-- 开启表级别CDC
use 【数据库名称】
GO
exec sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name=N'【数据表名称】',
@role_name=N'用户名',
@filegroup_name=N'上述创建文件组名称’
GO

7优化

• 如果事务日志增加较快，一方面可以限制日志大小，一方面可以收缩日志文件大小，压缩日志文件存储空间