SQLServer如何开启cdc功能(批量开启cdc)

什么是变更数据捕获 (CDC)？ - SQL Server | Microsoft Learnyi

一、定义

数据变更捕获

二、开启的必要条件

sqlsever 2008以上版本

需要开启代理服务（作业）

磁盘要有足够的空间，保存日志文件

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

三、开启cdc

1、开启库级cdc

--查看库是否开启cdc

select * from sys.databases where is_cdc_enabled = 1

--开启指定库cdc

USE XXX;

GO

EXEC sys.sp_cdc_enable_db

--关闭指定库cdc

USE XXX;

GO

EXEC sys.sp_cdc_disable_db;

2、开启表级cdc

--查看开启cdc的表

select name, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = '1'

--开启指定表cdc

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'mytable_int', @role_name = null;

--关闭指定表cdc

EXEC sys.sp_cdc_disable_table @source_schema = 'XXXX', @source_name = 'XXXX', @capture_instance = 'schema_table';

PS:批量开启库下所有表的脚本

BEGIN TRY  
DECLARE @source_name varchar(400);  
declare @sql varchar(1000)  
declare @dbname varchar(100), @enable bit
set @dbname='XXX';
set @enable = 1
DECLARE the_cursor CURSOR FAST_FORWARD FOR  
SELECT table_name  
FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@dbname and table_schema='dbo' and table_name != 'systranschemas'  
OPEN the_cursor  
FETCH NEXT FROM the_cursor INTO @source_name  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
if @enable = 1  
  
set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_enable_table  
            @source_schema = N''dbo'',@source_name = '+@source_name+'  
          , @role_name = N'''+'dbo'+''''  
            
else  
set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_disable_table  
            @source_schema = N''dbo'',@source_name = '+@source_name+',  @capture_instance =''all'''  
exec(@sql)  
  
  
  FETCH NEXT FROM the_cursor INTO @source_name  
  
END  
  
CLOSE the_cursor  
DEALLOCATE the_cursor  
  
      
SELECT 'Successful'  
END TRY  
BEGIN CATCH  
CLOSE the_cursor  
DEALLOCATE the_cursor  
  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH  

四、开启cdc的常见报错

开启cdc报错

1、消息 22830，级别 16，状态 1，过程 sp_cdc_enable_db_internal，行 193 [批起始行 2] Could not update the metadata that indicates database ASIAMINER_SSRS is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request. 完成时间: 2023-11-08T10:37:28.9865197+08:00

解决：ALTER AUTHORIZATION ON DATABASE::[XXXX] TO [sa]

2、部分表已开启

Could not create a capture instance because the capture instance name 'dbo_SCREEN_CONTROL_EVENT_PARAMETERS' already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

解决：关闭库级，开启库级