主页 / dba / 问题 / 33987
Accepted
RThomas
Asked: 2013-02-02 10:08:55 +0800 CST

捕获 SQL Server CDC 中更改的日期时间

  • 772

因此，我们开始探索在我们的一个生产数据库上使用变更数据捕获。我们想知道每次更改的日期时间。通读演练和教程等似乎标准方法是使用 LSN 与cdc.lsn_time_mapping系统表相关。这种方法有效，但在谈论每天成千上万的更改时不是很简单也不是很高效。

在测试环境中，我对更改跟踪表进行了以下调整。我发表了一条ALTER TABLE声明，在名为的末尾添加一列并将其设为[__ChangeDateTime]默认值GetDate()。该方法似乎有效，更改跟踪仍然正常运行，正在捕获日期时间。 但是乱搞系统表让我有点紧张。

如果这不是微软从一开始就添加的系统字段，他们一定有他们的理由。由于他们改为选择 LSN 到 cdc.lsn_time_mapping 方法，我是否通过这种方式创建自己的 hack 来解决问题？

更新：

在测试期间发现 GetDate() 有时不足以满足我们的需求 - 多个更改共享同一时间。建议使用sysdatetime() 和 datetime2将值移到纳秒级。显然只有 2008+ 的选项。

sql-server-2008 change-data-capture

5 个回答

  1. Best Answer

    请记住，CDC 使用日志读取器代理来填充更改表。为什么这很重要？通过这种机制，行在更改表中的显示与基表中所做的更改异步。

    实际上可以记录 3 个不同的时间点，按时间倒序排列：

    1. 更改交付到更改表的时间（这是您正在记录的内容）。
    2. 包含更改的事务提交的时间（使用cdc.lsn_time_mapping）。
    3. 您在基表中手动填充列的时间（使用默认约束、触发器等）。

    所以首先要明确你想要记录的内容。通常我们会关心#2 或#3。

    如果 LSN 映射机制 (#2) 对您来说表现不够好，唯一受支持的替代方法是向基表添加一列并自己填充它 (#3)。

    关于更改内部表，作为一项政策，我认为最好避免在有支持的替代方案时使用内部表。您最不想看到的是一个重要的生产系统出现故障，需要致电产品支持，并因此而被拒绝提供服务。不要介意它可能破坏事物（升级）或因为意外而被破坏的问题（关闭 CDC，然后再次打开，如另一个答案中所述）。

    • 8

  2. 一个实际的例子：

    USE Database;
GO

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('schema_tablename')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    ,[Field1]
    ,[Field2]
    ,[Field3]
FROM [cdc].[fn_cdc_get_all_changes_schema_tablename]
  (@from_lsn, @to_lsn, N'all');
    • 3

  3. 我要给出的唯一警告是，当 CDC 被禁用时，这些表会自动删除。当您重新启用它时，该列不会自动重新创建

    • 2

  4. 受加雷斯回答的启发，这里有一个较短的版本。DECLARE如果您需要创建VIEW( DECLAREis not allowed in VIEW) ，这不使用which is great

    SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    , *
FROM [cdc].[fn_cdc_get_all_changes_dbo_yourtablename
    (sys.fn_cdc_get_min_lsn('dbo_yourtablename')
    , sys.fn_cdc_get_max_lsn(), N'all')
    • 0

  5. 在前面的答案的基础上，我创建了这个脚本，它将查看所​​有 CDC 表并显示表名和最后更新：

    DECLARE @tableName varchar(max)
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn()
DECLARE @results TABLE
(
    [Table] varchar(max),
    [LastUpdated] datetime
)

-- Get list of change tables
DECLARE cdc_tables_cursor CURSOR
FOR SELECT
        [capture_instance]
    FROM cdc.change_tables;

OPEN cdc_tables_cursor
FETCH NEXT FROM cdc_tables_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get Table Name / Last Updated
    SET @from_lsn = sys.fn_cdc_get_min_lsn(@tableName)
    DECLARE @sql nvarchar(max) = '
    SELECT
        top 1
        REPLACE(@tableName,''dbo_'','''') as [Table],
        sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS ''Time''
    FROM [cdc].[fn_cdc_get_all_changes_'+@tableName+'](@from_lsn, @to_lsn, N''all update old'')
    order by 2 desc'

    -- Insert results of dynamic sql
    INSERT @results
    EXECUTE sp_executesql   
        @sql,  
        N'@tableName varchar(max), @from_lsn binary(10), @to_lsn binary(10)',  
        @tableName, @from_lsn, @to_lsn;

    FETCH NEXT FROM cdc_tables_cursor INTO @tableName
END

CLOSE cdc_tables_cursor;

DEALLOCATE cdc_tables_cursor;

-- List Tables/Last Updated
SELECT 
    * 
FROM @results
order by 1
    • 0

相关问题