SQLServer 启用 CDC

迷南。 2022-06-17 13:58 256阅读 0赞

用户怀疑系统数据经常性变化，要求我们验证，之前没有对数据库进行过任何设置，以前的情况不想在查了，只能现在开始监视，于是网上看到了sqlserver 2008的新功能，正好可以用上，把网上具有借鉴的整合到一起，供快速设置。

--------------------

**要捕获某个表的变更，必须先开启数据库级别的捕获，再开启表级的捕获**

--------------------

1.  启用数据库：在当前数据库下执行命令

USE financetrade;
        GO

其中financetrade是数据库名称。

EXEC sys.sp_cdc_enable_db

这个过程会在当前数据库下创建6个系统表  
cdc.captured\_columns  
cdc.change\_tables  
cdc.ddl\_history  
cdc.index\_columns  
cdc.lsn\_time\_mapping  
dbo.systranschemas

2查询哪些数据库启用了CDC功能

select * from sys.databases where is_cdc_enabled = 1

3启用表，例如表名为”dbo.t1”

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

注：为第一个表启用CDC后，SQLServer生成两个Agent作业  
cdc.dbname\_capture  
cdc.dbname\_cleanup  
4. 查看表是否启用了CDC

select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('dbo.t1')

5禁用表(“dbo.t1”)

EXEC sys.sp_cdc_disable_table  
        @source_schema = 'dbo',  
        @source_name = 't1',  
        @capture_instance = 'all';

6 禁用数据库  
EXEC sys.sp\_cdc\_disable\_db;  
7一些异常情况的处理  
(1) 数据库从2005恢复到2008，打开表级别的附加日志报错

未针对数据库 'AdventureWorks2014' 启用变更数据捕获。    
    请确保已设置正确的数据库上下文，然后重试该操作。若要报告数据库已启用变更数据捕获这一情况，    
    请查询 sys.databases 目录视图中的 is_cdc_enabled 列.

解决办法：

ALTER AUTHORIZATION ON DATABASE::[数据库名] TO [sa]

相关说明：  
![这里写图片描述][SouthEast]

我们可以来解释一下上面结果的含义：

\_\_$operation=2的情况，表示新增

\_\_$operation=3或者4，表示更新，3表示旧值，4表示新值

\_\_$operation=1的情况，表示删除

很好理解，不是吗？

但是，我们一般都是需要按照时间范围进行检索，对吧，所以，需要使用下面的语法进行查询

DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
    DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
    DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
    SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
    SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
    SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')

[SouthEast]: /images/20220617/7ddc933ed0da461f8cebab586696a149.png