65.9K
CodeProject 正在变化。 阅读更多。
Home

更改数据捕获 (SQL Server 2008)

Muhammad Shujaat Siddiqi
starIconstarIconstarIconstarIconemptyStarIcon

4.00/5 (7投票s)

2008年8月12日

CPOL

5分钟阅读

viewsIcon

50301

SQL Server 2008 中的变更数据捕获 (shujaatsiddiqi.blogspot.com)

引言

在数据库管理员的工作中，历史以来，变更管理一直是一项艰巨的任务。已经采用了许多解决方案，从在每个表中设置 CreateData 和 ModifiedDate 字段，到为 DML 操作设置 DML 触发器。在 SQL Server 2005 中，DDL 触发器是管理 DDL 操作的唯一选择。随着 SQL Server 2008 引入的许多新功能，CDC (变更数据捕获) 是其中一项值得讨论的功能。

配置

为了理解 SQL Server 2008 中 CDC 的配置，我们创建一个名为 CDC_Practice 的新数据库。

当从上下文菜单中选择“新建数据库”时，将出现以下表单

我们使用默认选项创建数据库（只需指定数据库名称为 CDC_Practice）然后按“确定”按钮。

现在，我们应该为我们的数据库启用 CDC。SQL Server 2008 配备了一个新的存储过程，名为 sys.sp_cdc_enable_db。我们只需执行此存储过程。

USE CDC_Practice
Exec sys.sp_cdc_enable_db

我们可以通过检查 sys.databases 表中新添加的 is_cdc_enable 字段来验证它是否已在我们的数据库中启用。

执行此命令时，您还不能不看一些新创建的系统表。还创建了一个名为 cdc 的用户以及一个名为 cdc 的架构。

通常，我们有许多表需要进行此类审计。能够指定要为其启用 CDC 的特定表是很好的。我们创建一个名为 EX_Table 的表。

Use CDC_Practice
Go
Create table EX_Table
(
ID Int Identity(1,1) Primary Key NOT NULL,
Name varchar(10)
)

正如我之前提到的，我们可以为特定表启用 CDC。所以，我们应该尝试为我们的 EX_table 表启用它。

Use CDC_Practice
Go
EXEC sp_cdc_enable_table 'dbo', 'Ex_table', @role_name = NULL, @supports_net_changes =1
Go

我们可以借助 sys.tables 中新增的一个属性 is_tracked_by_cdc 来验证 CDC 是否已启用。如果结果为 1，则表示已在指定表上启用。我们在 EX_Table 表上进行检查。

SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'EX_Table'

在为表启用 CDC 之前，我们需要确保 SQL Server Agent 正在运行。这是因为它也会为此目的创建两个作业。创建的两个作业如下：

  1. cdc.CDC_Practice_capture
  2. cdc.CDC_Practice_cleanup

DDL 捕获

到目前为止，我们只讨论了捕获数据操作。那么捕获 DDL 相关任务怎么样？在 SQL Server 2005 中，引入了 DDL 触发器。直到现在，这是记录已执行 DDL 语句的唯一选项。在 SQL Server 2008 中，这些 DDL 语句可在 ddl_history 表中找到。该表也可能用于创建一个与第一个数据库完全相同的数据库。

这仍然比 DDL 触发器更有益，因为 DDL 触发器会导致数据库应用程序中的单点故障。如果出现任何问题，则不允许更改数据库架构。有了 CDC，这个问题也得到了缓解。

引入的存储过程

为了支持 CDC，还引入了一些存储过程。它们如下：

  1. sp_cdc_add_job：用于创建捕获或清理作业。
  2. sp_cdc_change_job：用于修改捕获或清理作业配置。
  3. sp_cdc_cleanup_change_table：删除更改表中的所有记录。
  4. sp_cdc_disable_db：禁用当前数据库的 CDC。
  5. sp_cdc_disable_table：禁用指定表的 CDC。
  6. sp_cdc_drop_job：删除更改或捕获作业。
  7. sp_cdc_enable_db：启用指定数据库的 CDC。
  8. sp_cdc_enable_table：启用指定表的更改跟踪。它还会创建一些实用对象，如表等。
  9. sp_cdc_get_ddl_history：提供指定 CDC 实例的 DDL 历史记录。
  10. sp_cdc_help_change_data_capture：提供当前数据库中启用 CDC 的每个表的 CDC 信息。
  11. sp_cdc_help_jobs：提供有关更改和捕获作业的信息。
  12. sp_cdc_scan：由 sp_cdc_enable_table_change_data_capture 内部调用。它执行 CDC 日志扫描操作。
  13. sp_cdc_startjob：为当前数据库启动 CDC 捕获或清理作业。
  14. sp_cdc_stopjob：为当前数据库停止 CDC 捕获或清理作业。

必须注意，上述所有存储过程都定义在 sys 架构中。

引入的函数

对于 CDC 跟踪的每个表，都添加了两个表值函数。它们如下：

  1. fn_cdc_get_all_changes_dbo_TableName：为启用 CDC 的每个新表创建。
  2. fn_cdc_get_net_changes_dbo_TableName

上述两个函数都有三个必需参数。它们包括开始 LSN、结束 LSN 和行筛选器选项。行筛选器选项具有可能的​​值“all”、“all with mask”或“all with merge”。

元数据函数

  1. CHANGE_TRACKING_CURRENT_VERSION
  2. Change_tracking_current_version()
  3. Change_Tracking_Is_Column_In_Mask()

引入的系统表

如果数据库未启用 CDC，我们将为数据库启用变更数据捕获。当我们为数据库启用 CDC 时，将创建名为 'cdc' 的架构，并且还会在该架构中自动创建以下六个系统表。必须记住，直接查询系统表是不推荐的。相反，应该使用 2008 中引入的新存储过程。

  1. cdc.captured_columns：此表包含所有更改表的所有捕获列的信息。
  2. cdc.change_tables：此表包含数据库中的所有更改表。
  3. cdc.ddl_history：启用 CDC 后 DDL 语句的历史记录。
  4. cdc.index_columns：此表包含与更改表关联的每个索引列的信息。
  5. cdc.lsn_time_mapping：包含更改表中每个事务的信息。
  6. cdc.*_CT：此表是为 CDC 跟踪的每个表创建的。它包含启用 CDC 后对数据执行的所有 DML 操作。不再需要 DML 触发器仅用于审计表中的数据更改。CT 是 **Change Table** 的缩写。

对 sys.Databases 表的更改

在 SQL Server 2008 中，向 sys.databases 表添加了一个新字段。该字段为 is_cdc_enabled。此字段指示 CDC 是否已为特定数据库启用。

限制

  1. 不支持发布者/订阅者模型。因此，无法基于某些更改触发任何操作。对于此类需求，我们仍然需要依赖我们旧的触发器方法。
  2. 这是一个异步操作。虽然异步操作最大限度地减少了性能影响，但在某些需求中，只能决定进行同步操作。
  3. CDC 不能在堆上启用，即不能在没有主键的表上启用。

注意

SQL Server 2008 还发布了另一项功能 - 更改跟踪。必须记住，它与 CD 不同

  • 它仅为您提供数据库表中的净更改。
  • 它是同步的。

历史

  • 2008 年 8 月 11 日：初始帖子
© . All rights reserved.