Sql Server开启CDC
特殊说明:sql server开启cdc表必须存在主键
- 开启数据库CDC
EXEC sys.sp_cdc_enable_db
- 查询CDC状态
dbname为数据库名称,返回结果1表示开启
select is_cdc_enabled from sys.databases where name='dbname'
- 开启代理服务
开启SQL server agent服务(逐条执行)
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Agent XPs', 1;
RECONFIGURE;
- 容器化部署sqlserver采用docker开启服务代理
docker exec -u root -it xxx /bin/bash
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
exit;
docker restart xxx
- 查询db的物理文件,不清楚物理存储路径的可以先查询,特别说明,当删除了物理文件,这个查询仍会有记录直到下一次DB进行备份才会更新
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');
- 添加文件组并向文件组添加文件
ALTER DATABASE dbname ADD FILEGROUP CDCGroup;
ALTER DATABASE dbname ADD FILE (
NAME= 'HospitalInterfaceDb_CDC',
FILENAME = 'file-path'
) TO FILEGROUP CDCGroup;
- 开启表CDC
EXEC sys. sp_cdc_enable table
@source_schema = 'alltype1',
@source_name = 'integration_c',
@capture_instance = NULL,--可为NULL,
@supports_net_changes = 1,--示网络更(即描入、更新删除)
@role_name = NULL;--访问CDC的色,如果为NULL则没有特定的色要求
@filegroup_name = 'CDCGroup' -- filegroup_name
- 查询是否开启CDC
SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name='table';
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('schema.table');