如何通知 Windows 服务（c#）数据库表更改（sql 2005）？

Question

我在 SQL2005 数据库中有一个负载很重（许多插入/更新/删除）的表。我想尽可能实时地对所有这些更改进行一些后处理（异步以免以任何方式锁定表）。我已经研究了许多可能的解决方案，但似乎找不到一种感觉正确的简洁解决方案。

这种后处理也相当繁重，以至于 Windows 侦听器服务实际上会将处理传递给许多机器。然而，应用程序的这一部分已经启动并运行，完全异步，而不是我需要的帮助 - 我只是想提一下这一点，因为它影响了设计决策，因为我们不能只在DB完成处理。

所以，简单的问题仍然存在：表中的数据更改，我想在远程服务器上用 C# 代码进行一些处理。

目前我们已经提出使用 sql 触发器，它执行“xp_cmdshell”来启动一个 exe，该 exe 会引发 Windows 服务正在侦听的事件。这只是感觉很糟糕。

然而，我在网上看到的其他解决方案也感觉相当复杂。例如，设置 SQLCacheDependancy 还涉及必须设置 Service Broker。另一种可能的解决方案是使用 CLR 触发器，它可以调用 Web 服务，但网上有很多警告称这是一种不好的方法，尤其是在性能至关重要的情况下。

理想情况下，我们不会依赖于表更改，而是宁愿拦截应用程序内部的调用并从那里通知服务，不幸的是，尽管我们有一些遗留应用程序也对数据进行更改，并且监视表是唯一集中的位置那一刻。

任何帮助将不胜感激。

摘要：

• 需要实时响应表数据更改 性能
• 至关重要 预计
• 会有大量流量
• 轮询和计划任务不是一种选择（或实时）
• 实现服务代理太大（但可能是唯一的解决方案？）
• CLR 代码是尚未排除，但如果建议的
• 侦听器/监视器可能是远程计算机（可能是相同的物理网络），则需要执行

Answer 1

您确实没有那么多方法来检测 SQL 2005 中的更改。您已经列出了其中的大部分。

查询通知。这是为 SqlDependency 及其衍生产品提供支持的技术，您可以在 上阅读更多详细信息神秘通知。但 QN 的设计目的是使结果无效，而不是主动通知更改内容。你只会知道表发生了变化，而不知道变化了什么。在繁忙的系统上，这将不起作用，因为通知几乎会连续出现。

日志阅读。这就是事务复制所使用的方法，也是检测更改的侵入性最小的方法。不幸的是仅适用于内部组件。即使您设法理解日志格式，问题是您需要引擎的支持才能将日志标记为“正在使用”，直到您读取它，否则它可能会被覆盖。只有事务复制才能进行这种特殊标记。

数据比较。依靠时间戳列来检测更改。也是基于拉的，相当具有侵入性，并且在检测删除方面存在问题。

应用层。从理论上讲，这是最好的选择，除非数据发生超出应用程序范围的更改，在这种情况下它就会崩溃。在实践中，总是在应用程序范围之外发生变化。

触发器。最终，这是唯一可行的选择。所有基于触发器的更改机制都以相同的方式工作，它们将更改通知排队到监视队列的组件。

总是有人建议进行紧密耦合的同步通知（通过 xp_cmdshell、xp_olecreate、CLR、WCF 通知，等等），但所有这些方案在实践中都失败了，因为它们存在根本缺陷：
- 它们没有考虑事务一致性和回滚
- 它们引入了可用性依赖性（除非通知的组件在线，否则 OLTP 系统无法继续）
- 它们执行得非常糟糕，因为每个 DML 操作都必须等待某种形式的 RPC 调用完成

如果触发器实际上并不主动通知侦听器，而只是对通知进行排队，则监视通知队列时会出现问题（当我说“队列”，我的意思是任何充当队列的表）。监视意味着在队列中拉取新条目，这意味着正确平衡检查频率与更改负载，并对负载峰值做出反应。这一点都不简单，实际上非常困难。但是，SQL Server 中有一个语句具有阻塞而不拉取的语义，直到更改可用： 等待（接收）。这意味着服务代理。您在帖子中多次提到 SSB，但您理所当然地害怕部署它，因为存在巨大的未知数。但现实是，到目前为止，它最适合您所描述的任务。

您不必部署完整的 SSB 架构，在这种架构中，通知会一直传递到远程服务（无论如何，这都需要远程 SQL 实例，甚至是 Express 实例）。您所需要做的就是将检测到更改的时刻（DML 触发器）与传递通知的时刻（提交更改后）分离。为此，您所需要的只是本地 SSB 队列和服务。在触发器中，您发送一条更改通知到本地服务。原始 DML 事务提交后，服务过程激活并发送通知，例如使用 CLR。您可以在 异步 T-SQL。

如果您沿着这条路走下去，您需要学习一些技巧来实现高吞吐量，并且您必须了解 SSB 中消息有序传递的概念。我建议您阅读以下链接：

• 重用对话
• 编写 Service Broker 过程
• SQL Connections 2007 演示

关于检测更改的方法，SQL 2008 显然添加了新选项：更改数据捕获和更改跟踪。我强调“显然”，因为它们并不是真正的新技术。 CDC 使用日志读取器并基于现有的事务复制机制。 CT 使用触发器，与现有的合并复制机制非常相似。它们都适用于需要同步的偶尔连接系统，因此不适合实时更改通知。他们可以填充更改表，但您需要监视这些表的更改，这正是您开始的地方。

Answer 2

这可以通过多种方式来完成。下面的方法很简单，因为您不想使用 CLR 触发器和 sqlcmd 选项。

• 您可以创建普通插入触发器来更新每次插入时的专用跟踪表，而不是使用 CLR 触发器。

• 并开发专用的窗口服务，该服务主动轮询跟踪表，如果数据有任何更改，则更新远程服务，并将跟踪表中的状态设置为完成（这样就不会再次被选中）。

编辑：

我认为 Microsoft ADO.Net 同步服务可以为您服务。查看以下链接。它可能会帮助您

• 如何：使用 SQL Server 更改跟踪 - sql server 2008
• 使用自定义更改跟踪系统 - 低于 sql server 2008

Answer 3

在类似的情况下，我们使用 CLR 触发器将消息写入队列 (MSMQ)。用 C# 编写的服务正在监视队列并进行后处理。
在我们的例子中，这一切都是在同一台服务器上完成的，但是您可以将这些消息直接发送到另一台机器上的远程队列，完全绕过“本地侦听器”。

从触发器调用的代码如下所示：

public static void SendMsmqMessage(string queueName, string data)
{
    //Define the queue path based on the input parameter.
    string QueuePath = String.Format(".\\private$\\{0}", queueName);

    try
    {
        if (!MessageQueue.Exists(QueuePath))
            MessageQueue.Create(QueuePath);

        //Open the queue with the Send access mode
        MessageQueue MSMQueue = new MessageQueue(QueuePath, QueueAccessMode.Send);

        //Define the queue message formatting and create message
        BinaryMessageFormatter MessageFormatter = new BinaryMessageFormatter();
        Message MSMQMessage = new Message(data, MessageFormatter);

        MSMQueue.Send(MSMQMessage);
    }
    catch (Exception x)
    {
        // async logging: gotta return from the trigger ASAP
        System.Threading.ThreadPool.QueueUserWorkItem(new WaitCallback(LogException), x);
    }
}

Answer 4

既然您说该表上运行了许多插入，那么批处理可能更适合。

为什么只创建一个计划作业来处理由标志列标识的新数据，并处理大块的数据？

Answer 5

使用典型的触发器在数据库上触发 CLR。此 CLR 将仅使用 Win32_Process 类远程启动程序：

http://motevich.blogspot.com/2007/11/execute-program-on-remote-computer.html