主页 / dba / 问题 / 120248
Accepted
JohnG
Asked: 2015-11-06 13:25:25 +0800 CST

Service Broker 是审计 SQL Server Express 上数据更改的最佳选择吗？

  • 772

我的项目是在不延长事务的情况下审计我们系统中的 5 到 10 个现有表。无论使用哪种方法，它都必须在 SQL Server Express 2005 到（最终）2016 上运行。

我对变更数据捕获 (CDC) 和变更跟踪进行了研究。更改跟踪不捕获特定更改，CDC 仅在企业版中可用。

然后我偶然发现了 Service Broker。我对 Service Broker 很感兴趣，所以我开始创建一个原型。Service Broker 工作正常，但在我的其他两个帖子中收到的答案让我相信这可能不是正确的方法。太复杂了无所谓。我仍处于分析阶段并尝试不同的事情作为我分析的一部分。

目前，服务代理的结果并不令人信服……将 105000 件商品批量更新到价格表需要 38 秒，而处理队列（审计部分）需要 17 秒……但 38 秒包括插入 2 个 #temp 表的双重处理，然后用于插入 TMPins 和 TMPdel。所以我想我可以把它减半......我现在质疑服务代理的使用......从逻辑上讲，只需将信息直接插入审计表，触发器可能会花费相同的时间...... .

澄清一下，当我说批量插入时，它不是“批量插入”功能。我说的是一次插入或更新的大量数据。在更新价格表中的 105000 项时，我想审核发生的更改。当我说发生了变化时，我决定在审计表中插入新值（如果它是插入或更新）或插入所有其他字段为空的主键（对于已删除的记录）......所以是的！它可以在数据加载之后，但我不想失去任何审计（我不希望交易乱序传递）

另外两篇文章将有助于了解我正在尝试做的事情和我尝试过的事情：

我重视每一个想法。

sql-server trigger

1 个回答

  1. Best Answer

    我猜以下帖子是您当前使用的基础：使用 Service Broker 进行集中式异步审计。

    虽然我真的很喜欢 Service Broker，但我认为它不是解决这种特殊情况的最佳选择。至少在这种特定情况下，我对 Service Broker 的主要担忧是：

    • 如果主要用于将 DML 事件与审计事件分开，它可能过于复杂。如果不将数据移动到另一个系统，它似乎不会提供太多好处，因为您仍然需要在 DML 触发器中保留INSERTED和表。DELETED
    • 它是基于事件的，事件的大小和频率差异很大。您可能有一百万条 1 记录操作，或者一两个 100 万条记录操作。由于每个事件都是每个单独的 DML 操作，因此您没有任何机会跨多个 DML 操作删除重复和/或否定条目。

    我的偏好是将更改转储到队列表中，然后在计划每 X 分钟运行一次的单独进程中，读取 Y 行并处理它们。

    1. 创建一个队列表来保存特定表的审计数据（而不是每个单独的 DML 操作）。该表应具有：

      • 该LOCK_ESCALATION选项设置为DISABLE(via ALTER TABLE {name} SET (LOCK_ESCALATION = DISABLE)) 以避免触发器记录新数据与从审计处理中删除数据之间的冲突。此选项是在 SQL Server 2008 中引入的，因此它不能在 2005 实例上使用，但没有理由不在 2008 和更新的实例中使用它，因为它不会改变任何一种情况下的功能。
      • PK是AuditID以下之一：
        • 从INT IDENTITY-2147483648 开始
        • 一个BIGINT IDENTITY
        • aINT的值来自SEQUENCE设置为的 aCYCLE
      • 只有您正在跟踪更改的字段（如果不是全部）
      • 如果您需要在每次处理时跟踪之前和之后的值，则“旧”和“新”的字段。根据您审计更改的方式，如果您有先前值的存档，那么您应该只需要表中的“新”值，因为INSERTED表中的值DELETED应该已经在您先前的审计数据中。

    2. 创建一个简单地插入到队列表中的触发器。如果您需要同时传递“旧”和“新”值，请在此处加入INSERTED和DELETED表，而不是尝试维护两个单独的队列表，每个伪表一个。此时加入它们并将“旧”和“新”值插入一行会轻微影响性能，但会保证每个操作保持在一起并按时间顺序排列（通过递增的 PK）。

      如果您没有跟踪所有字段的更改，则使用UPDATE()或COLUMNS_UPDATED()来确定被审计的列是否确实已更新（对于UPDATE操作；这些函数对操作中的所有列返回 true INSERT）。请记住，UPDATE()andCOLUMNS_UPDATED()函数不能确定列的值是否发生了变化！！它们仅报告列是否存在于语句的SET子句中UPDATE。确定值是否实际更改的唯一方法是加入INSERTED和DELETED表。但是，如果不跟踪所有列，那么如果没有跟踪的列发生更改，这些函数非常适合在不执行任何工作的情况下退出触发器。意思是，在触发器开始时，你会这样做：

      IF (NOT UPDATE(TrackedColumn1) AND NOT UPDATE(TrackedColumn2))
BEGIN
  RETURN; -- no changes so just exit
END;

      如果您没有将“旧”和“新”值都捕获到队列表中，那么这是消除没有实际更改列的“更新”记录的唯一机会。插入队列表时，您只需过滤WHERE IntColOld <> IntColNew OR StringFieldOld <> StringFieldNew COLLATE Latin1_General_BIN2 OR ISNULL(DateFieldOld, '1900-01-01') <> ISNULL(DateFieldNew, '1900-01-01') OR .... 对字符串字段使用_BIN2排序规则以确保这两个字段实际上是相同的，这一点很重要。并且您需要INSULL仅用于可为空的字段，以便它们可以等同于如果两者都是NULL.

    3. 创建一个运行 X 秒的存储过程，并在这段时间内TOP(@BatchSize)使用ORDER BY AuditID ASC. 您可以通过一个WHILE循环来检查GETDATE()在@StartTime存储过程开始时设置的对象。根据您需要执行的处理，有时更容易为INSERT INTO #TempTable SELECT...工作集创建本地临时表（然后您必须DELETE在每个循环结束时创建这些行）或DELETE FROM使用OUTPUT INTO #TempTable.

      在这里您可以删除重复的修改。如果您要跟踪每行的“旧”和“新”值，您还应该消除没有任何列实际更改的行。你可以通过测试来做到这一点WHERE IntColOld = IntColNew AND StringFieldOld = StringFieldNew COLLATE Latin1_General_BIN2 AND ...。对字符串字段使用_BIN2排序规则以确保这两个字段实际上是相同的，这一点很重要。非二进制排序规则，即使区分大小写和区分重音等，仍然允许语言规范化，在常规比较时应该比较相同，但在审计时则不行。不要使用_BIN排序规则，因为它们自 SQL Server 2005 以来已被弃用，这是_BIN2排序规则出现的时候。

      在循环内完成的所有事情都需要在显式BEGIN TRAN/ COMMIT/内ROLLBACK。使用 TRY / CATCH 块来管理它。这将防止丢失某些记录或处理某些记录两次。

    4. 安排存储过程每 X 分钟运行一次。通常这是通过SQL Server 代理作业完成的，但 SQL Server 代理不适用于 Express 版本。在这种情况下，您可以使用Windows 任务计划程序或获取类似Quartz.NET的东西。

    以这种方式设置流程可以让您拥有一个更加一致且可调整的流程，该流程应该每 Y 分钟稳定地处理 X 条记录。所以不管你有 100 万个单行 DML 操作还是单个 100 万行 DML 操作；这个过程只会继续前进，做它所做的。

    • 7

相关问题