使用SQL Server 的CDC功能实现数据变更捕获

?

USE t;

GO

--开启某个数据库的CDC功能

exec sys.sp_cdc_enable_db

GO

 

--is_cdc_enabled栏位为1代表开启CDC功能了

SELECT  is_cdc_enabled,CASE WHEN is_cdc_enabled=0

THEN 'CDC功能禁用' ELSE 'CDC功能启用'END 描述

FROM    sys.databases

WHERE   NAME = 't'

 

--开启单张表的CDC功能

EXEC sys.sp_cdc_enable_table @source_schema='dbo',

    @source_name = 'so',@role_name = NULL

 

--关闭单张表的CDC功能

EXEC sys.sp_cdc_disable_table @source_schema='dbo',

    @source_name = 'so',@capture_instance = 'dbo_so'

  

--  __$operation”为“1”代表删除，“2”代表插入，“3”执行更新操作前的值，“4”执行更新操作后的值。

SELECT  * FROM  [cdc].[dbo_so_CT]

 

--(DDL) 更改历史记录

SELECT * FROM  [cdc].[ddl_history]

　

?

注意事项

 

1．  SQL Server的版本必须是2008或以上；

 

2．  不能同时使用内存优化表（SQL Server2014或以上版本才有的功能）。否则会出现以下错误：

 

 

 

3.  @@SERVERNAME、serverproperty('servername')两者（本地服务器名和服务器实例的属性必须一致）必须一致。下面脚本可将两者调整成一致。如果执行后两者仍不一致，需要重启SQL Server服务。

 

if serverproperty('servername') <> @@servername

begin

    declare @server sysname

    set @server = @@servername

    exec sp_dropserver@server =@server

    set @server = cast(serverproperty('servername') as sysname)

    exec sp_addserver@server = @server , @local = 'LOCAL'

    PRINT 'ok'

end

  

select @@SERVERNAME,serverproperty('servername')

4.      必须开启SQL Sever代理服务。CDC功能必须通过作业来实现。

 

5.      开启CDC功能的表，无法使用 TRUNCATE TABLE 。可以先禁用，执行完truncate再启用cdc。

 

6.      如果表结构发生变化，则捕获实例表中：新增列无法捕获到、删除列保持NULL、修改列类型会发生强制转换。为保险起见，应禁用捕获实例，然后再启用。

 

7.      在查询CDC相关表时，建议加上With(NOLOCK)，否则易产生阻塞或死锁。

 

8.      一个表最多只能有两个捕获实例。

 

如果更新表时并未实际修改值，则不会有产生捕获（对应的捕获实例表不会增加相应的行）。