linux-sqlserver开启表CDC

1、在shell中开启sqlserver数据库代理，并且重启数据库。

/opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server

2、添加CDC文件组到testdb数据库中

alter database testdb add filegroup CDC;

3、添加CDC日志到CDC文件组

alter database testdb add file
(
name='testdb_CDC',
filename='/var/opt/mssql/data/testdb_CDC.ndf',
size=5MB,
filegrowth=10%
)to filegroup CDC;

4、开启testtable表的CDC功能

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

5、检查表是否开启CDC

SELECT is_tracked_by_cdc FROM sys.tables WHERE name='testtable';