在 SQL Server 中实现基于触发器的变更数据捕获的更简单方法

假设您有一个包含连接到数据仓库的客户信息的 SQL。您的分析师利用该数据库为您的客户创建个性化的营销活动。新客户数据定​​期出现，旧客户数据经常变化。为了应对这些变化，您必须定期更新数据仓库，这可能会导致延迟问题并减慢分析速度。结果？您依赖过时的数据来做出关键决策。

为了确保您始终做出可靠的决策，您可以使用变更数据捕获 (CDC)——一种更简单的更新目标位置数据的方法。 CDC 检测源表数据的更改并相应更新您的目标目的地。它确保您的数据永远不会过时，并且您的决策始终基于可靠的最新信息。变更数据捕获有不同类型，例如基于日志、数据修改、差异和基于触发器。

本博客将概述 CDC 在数据管理中的作用，并将讨论基于触发器的变更数据捕获 SQL Server。

疾病预防控制中心和数据管理

CDC 是数据管理领域的游戏规则改变者。使用 CDC，您只需更新目标目的地中的更改。因此，您可以快速执行 ETL 流程。

它可以在应急计划中发挥关键作用。想象一下您的数据中心丢失了一些数据，并且备份丢失了一些文件。借助 CDC，您可以保持主数据和备份源同步，并确保您的灾难恢复计划万无一失。

CDC 通过在数据库之间实时移动数据来加速商业智能 (BI)。它对于医疗保健行业也很有帮助，因为数据的准确性和及时性对于提供最佳的患者护理至关重要。如果数据库连接到仪表板，CDC 可以确保从医疗设备流式传输的所有数据在数据仓库中及时更新。

停机是数据迁移过程中最大的挑战之一。使用 CDC，您可以进行几乎零停机的迁移，因为它不会中断工作负载。

现在您已经对 CDC 有了基本的了解，让我们来探讨一下 CDC 的类型。

疾控中心的类型

SQL Server 中的 CDC 具有三种主要形式：基于触发器、基于日志和基于查询。每种类型都提供针对 SQL Server 环境中不同数据环境量身定制的独特优势。 SQL Server 中 CDC 类型的选择取决于 SQL Server 数据环境的特定需求和约束。为了帮助组织做出明智的决策，以下部分将深入研究 SQL Server 中的这些 CDC 类型，重点介绍它们的功能、优势和理想用例。

1. 基于触发的 CDC

SQL Server 中基于触发器的 CDC 涉及使用数据库触发器来捕获更改。触发器是数据库中的一个存储过程，它自动响应特定事件，例如插入、更新或删除操作。当发生任何这些事件时，触发器将触发并将更改记录在一个单独的表（称为更改表）中。虽然此方法实施起来相对简单并且适用于不同类型的数据库，但由于管理触发器的额外开销，它可能会影响 SQL Server 性能。

对于需要立即数据更新的 SQL Server 应用程序（例如面向客户的应用程序，其中实时数据对于维持良好的用户体验至关重要），基于触发器的 CDC 是一个绝佳选择。

2. 基于日志的CDC

SQL Server 中基于日志的 CDC 通过读取数据库事务日志来捕获更改。事务日志是记录所有事务以及每个事务对数据库所做的修改的文件。通过读取此日志，基于日志的 CDC 可以识别哪些数据已更改、更改时间以及更改发生的顺序。此方法通常比基于触发器的 CDC 更高效且侵入性更小，因为它不会影响 SQL Server 性能。然而，它需要对数据库内部操作有深入的了解。

基于日志的 CDC 非常适合事务量较大的 SQL Server 环境，例如频繁发生更改的金融系统。它还适用于需要按照更改发生的确切顺序捕获更改的场景，例如事件顺序很重要的审计跟踪或合规性报告。

3. 基于查询的 CDC

SQL Server 中基于查询的 CDC 通过定期查询源数据库并将当前数据状态与以前记录的状态进行比较来捕获更改。当无法实现基于触发器和基于日志的CDC时，例如当源SQL Server数据库不支持触发器或事务日志时，通常会使用此方法。但是，基于查询的 CDC 可能会占用大量资源，并且如果查询之间发生多个更改，则可能无法捕获所有更改。

基于查询的 CDC 最适合不支持触发器或事务日志的 SQL Server 数据库，或者不优先考虑实时数据更新的场景。它在 SQL Server 环境中也很有用，在这些环境中，由于安全或技术限制，对事务日志的访问受到限制，例如基于云的数据库或第三方数据库。

聚焦基于触发器的 CDC

基于触发器的 CDC 是一种向目标系统发出源系统已发生更改的信号的方法。顾名思义，变更数据捕获过程是根据特定触发器启动的。

SQL 数据库中有三个触发器：插入表示新条目，更新表示更改，删除表示删除条目。每当执行这些命令时，它们都会记录在影子表或更改表中，该表维护所有更改的详细记录。一旦记录下来，这些更改就会传播到目标目的地。

由于所有触发器都是基于 SQL 的，因此基于触发器的 CDC 是 SQL Server 的首选。

使用 Microsoft SQL Server

SQL Server 是一种用于各种应用程序后端的关系数据库。 SQL Server 允许用户轻松管理和存储数据。该数据库配备了许多数据管理和分析功能，使用户可以更轻松地将其用于商业智能和机器学习计划。

开发人员更喜欢使用 Microsoft SQL Server 的原因有多种：

SQL Server 中的更改数据捕获

Microsoft 在 SQL Server、Azure SQL 和 Azure SQL 托管实例中提供变更数据捕获。

SQL Server 中的更改数据捕获使用 SQL Server 代理来记录表上的任何插入、更新或删除活动，然后以易于使用的格式提供这些活动。

这是 Microsoft SQL Server 中的典型数据库：

每当对此表进行更改时，CDC 都会将其记录在单独的表中。

CDC 极大地方便了从 SQL Server 提取数据并将其加载到数据库或数据仓库中。 ETL/ELT 工具通常从 SQL Server 提取数据并将其增量加载到数据仓库。在增量加载中，您只加载最近的数据，而不是整个数据库，这样可以节省时间并提高性能。 ETL/ELT 和 CDC 的组合确保以最少的资源在数据仓库中提供可靠的服务。

您可以使用 Microsoft 的本机功能或第三方 ETL 工具在 Microsoft SQL Server 中使用 CDC。

选项 1：本机启用 Microsoft SQL Server 更改数据捕获

您需要满足一定的条件才能 在 SQL Server 中本机启用 CDC。 只有具有 sysadmin 固定服务器角色或 db_owner 的用户才能在数据库中启用 CDC。由于 CDC 在 Web 版本中不可用，因此您必须有 SQL Server Developer、Enterprise 或 Standard Edition。

启用 CDC 后，SQL Server 将创建 CDC 架构、元数据表和 CDC 用户。 CDC 架构具有 CDC 的所有元数据表。一旦启用源表进行变更数据捕获，变更表将充当变更数据的存储库。

本地启用 Microsoft SQL Server 更改数据捕获

虽然您可以使用 SQL Server 的本机 CDC 功能，但它有一定的限制。要使用 SQL Server CDC，需要 SQL Server 代理。此外，SQL Server CDC 不会扩展到未托管在 SQL Server 实例上的数据库。如果要将数据从多个源发送到数据仓库，则有必要单独维护每个源的 CDC 流程。

您可以使用第三方工具，例如 Astera Centerprise 来克服这些限制。 Astera Centerprise 是利用 CDC 和 ETL/ELT 组合的更简单方法。该免代码工具配备强大的 ETL/ELT 引擎，可快速处理大量数据。结合直观的用户界面和较短的学习曲线， Astera Centerprise 使得在 SQL Server 中实现 CDC 变得毫不费力。

选项 2：在 SQL Server 中实现基于触发器的变更数据捕获 Astera Centerprise

Astera Centerprise 显着简化了在 SQL Server 中实施 CDC 的过程。只需将 SQL Server 数据库拖放到数据流设计器中，然后从下拉菜单中选择“启用表中的更改数据捕获”选项即可。

在 SQL Server 中实现变更数据捕获 Astera Centerprise

启用 CDC 后，您可以将所有更改写入您选择的目的地。有两个选项：您可以将它们写入文件格式目标、数据库或数据仓库。

从源表读取数据库更改

在上述源表中启用 CDC 后，从属性部分选择基于触发器的 CDC。如果要将现有源数据加载到目标中，请选择“首次运行时执行完全加载”选项，该选项默认启用。如果您只想加载更改，请取消选中此选项。

从源表读取数据库更改

以文件格式写入数据库更改 目标

您可以将数据库更改写入您选择的任何文件格式。 Astera Centerprise 支持各种文件格式，包括 JSON、Excel、CSV 和分隔符。您只需在数据流设计器中拖放目标并将数据从源映射到目标，无需编写任何代码。

将数据库更改写入文件格式目标 Astera Centerprise

将数据库更改写入数据仓库或数据库

将数据写入数据仓库有点不同，但很简单。您将更新旧记录或将新记录添加到数据库或数据仓库中。对于这些目的地， Centerprise 提供更新插入的选项。您可以启用更新插入选项并选择主键。

如果目标中没有记录，则更新插入选项将插入它。如果记录已经存在，则会更新它。

使用更新插入 Astera Centerprise

完成后，您可以轻松映射数据库或数据仓库中的更改。 Astera Centerprise 具有与流行数据库和数据仓库的本机连接，例如 MySQL、Snowflake、Amazon Redshift、PostgresSQL 等。

在数据库/数据仓库目标中写入数据库更改 Astera Centerprise

在 SQL Server 中自动捕获基于触发器的变更数据

实施 CDC 的最佳部分是 Astera Centerprise 是您可以利用其自动化功能。和 Centerprise，您可以轻松设置从 SQL Server 到目标目的地的数据流并安排其自动运行。

Centerprise 有一个内置的作业调度程序，您可以根据您的首选设置运行它。有各种基于事件和时间的触发器供您选择。您可以设置每周、每月、每天甚至每小时的频率间隔。

您还可以设置电子邮件通知，以便在每次数据流运行时向您发出信号。

CDC 作为审计解决方案

SQL Server 中的 CDC 通过提供对数据库内数据所做的所有更改进行系统且详细的跟踪来充当审计解决方案。它的功能如下：

• 详细追踪： SQL Server 中的 CDC 通过创建镜像所跟踪源表的列结构的更改表来进行操作。这些更改表存储所有数据修改的详细记录，包括插入、更新和删除。由于 CDC 提供审计跟踪并允许对数据随时间的变化进行彻底的审查和调查，因此这对于维护数据完整性至关重要。
• 问责： 除了跟踪数据更改之外，SQL Server 中的更改数据捕获还捕获重要的元数据，例如更改的性质（插入、更新、删除）、更改的时间以及与更改相关的特定事务。维护详细记录可以促进问责制，因为用户知道他们在数据库中的行为正在被记录，从而促进责任和透明度的文化。
• 注释： CDC 使用 SQL Server 中称为日志读取器代理的进程来读取事务日志并填充更改表。日志读取器代理是一项在后台持续运行的作业，扫描数据库的事务日志并将任何更改复制到分发数据库。通过主动跟踪和监控系统内所做的修改，组织可以遵守通用数据保护条例 (GDPR) 等监管要求。
• 实时监控： SQL Server 中的更改数据捕获几乎实时运行。它采用捕获实例，每个实例由更改表和查询函数组成，以持续监视特定表的数据更改。当发生更改时，捕获实例会在更改表中记录详细信息。通过实时跟踪数据，组织可以快速检测并响应任何未经授权或不适当的更改，从而增强数据安全性。

为什么你应该选择 Astera Centerprise?

Astera Centerprise 消除了数据管理的复杂性。无代码环境和直观的 UI 允许业务用户负责他们的数据驱动计划。以下是一些功能，使 Astera Centerprise 启用 CDC 的绝佳选择：

• Astera Centerprise 支持流行数据库、数据仓库和文件格式的各种连接器。
• 通过 Astera Centerprise，您可以在一个平台中管理所有关系数据库的 CDC，而不是单独管理它们。
• Astera 支持内置转换，您可以使用它们来清理和操作数据
• 您可以使用 Astera 数据分析和质量功能，以确保数据的准确性和可靠性。
• 你可以利用 Astera Centerprise 自动化和作业调度功能可加速数据传输

下载 Astera Centerprise 今天就免费试用 14 天。