如何将数据库表更改(sql 2005)通知 Windows 服务(c#)? [英] How to notify a windows service(c#) of a DB Table Change(sql 2005)?
问题描述
我在 SQL2005 数据库中有一个负载很重(许多插入/更新/删除)的表.我想尽可能接近实时地对所有这些更改进行一些后期处理(异步，以免以任何方式锁定表).我查看了许多可能的解决方案，但似乎无法找到一种感觉合适的简洁解决方案.
I have a table with a heavy load(many inserts/updates/deletes) in a SQL2005 database. I'd like to do some post processing for all these changes in as close to real time as possible(asynchronously so as not to lock the table in any way). I've looked a number of possible solutions but just can't seem to find that one neat solution that feels right.
这种后处理也相当繁重，以至于 Windows 侦听器服务实际上会将处理传递给许多机器.然而，应用程序的这一部分已经启动并运行，完全异步，而不是我需要帮助的部分 - 我只是想提到这一点，因为它会影响设计决策，因为我们不能只是加载一些 CLR 对象DB 完成处理.
The kind of post processing is fairly heavy as well, so much so that the windows listener service is actually going to pass the processing over to a number of machines. However this part of the application is already up and running, completetly asynchronous, and not what I need help with - I just wanted to mention this simply because it affects the design decision in that we couldn't just load up some CLR object in the DB to complete the processing.
所以，简单的问题仍然存在:表中的数据发生变化，我想在远程服务器上用c#代码做一些处理.
So, The simple problem remains: data changes in a table, I want to do some processing in c# code on a remote server.
目前我们已经提出了使用 sql 触发器，它执行xp_cmdshell"来启动一个 exe，该 exe 引发 Windows 服务正在侦听的事件.这感觉很糟糕.
At present we've come up with using a sql trigger, which executes "xp_cmdshell" to lauch an exe which raises an event which the windows service is listening for. This just feels bad.
但是，我在网上看到的其他解决方案也感觉相当复杂.例如，设置 SQLCacheDependancy 还涉及必须设置服务代理.另一种可能的解决方案是使用 CLR 触发器，它可以调用网络服务，但网上有很多警告说这是一种糟糕的方法，尤其是在性能至关重要的情况下.
However, other solutions I've looked at online feel rather convoluted too. For instance setting up SQLCacheDependancy also involves having to setup Service broker. Another possible solution is to use a CLR trigger, which can call a webservice, but this has so many warnings online about it being a bad way to go about it, especially when performance is critical.
理想情况下，我们不会依赖表的更改，而是会拦截应用程序内部的调用并从那里通知服务，不幸的是，尽管我们也有一些遗留应用程序对数据进行了更改，并且监控表是唯一的目前集中的地方.
Idealy we wouldn't depnd on the table changes but would rather intercept the call inside our application and notify the service from there, unfortunately though we have some legacy applications making changes to the data too, and monitoring the table is the only centralised place at the moment.
任何帮助将不胜感激.
总结:
- 需要实时响应表数据变化
- 性能至关重要
- 预计会有大量流量
- 轮询和计划任务不是一个选项(或实时)
- 实现的服务代理太大(但可能是唯一的解决方案?)
- 尚未排除 CLR 代码，但如果建议，则需要执行
- 监听器/监视器可能是远程机器(可能是同一个物理网络)
推荐答案
在 SQL 2005 中检测更改的方法确实不多.您已经列出了其中的大部分.
You really don't have that many ways to detect changes in SQL 2005. You already listed most of them.
查询通知.这是支持 SqlDependency 及其衍生产品的技术，您可以在 阅读更多详细信息神秘通知.但 QN 旨在使结果无效，而不是主动通知更改内容.您只会知道该表发生了变化，而不知道发生了什么变化.在繁忙的系统上这是行不通的，因为通知会持续不断地出现.
Query Notifications. This is the technology that powers SqlDependency and its derivatives, you can read more details on The Mysterious Notification. But QN is designed to invalidate results, not to pro-actively notify change content. You will only know that the table has changes, without knowing what changed. On a busy system this will not work, as the notifications will come pretty much continously.
日志阅读.这就是事务复制所使用的，并且是检测更改的侵入性最小的方法.不幸的是，仅适用于内部组件.即使您设法理解日志格式，问题是您需要引擎的支持才能将日志标记为正在使用"，直到您阅读它，否则它可能会被覆盖.只有事务复制可以做这种特殊的标记.
Log reading. This is what transactional replication uses and is the least intrusive way to detect changes. Unfortunately is only available to internal components. Even if you manage to understand the log format, the problem is that you need support from the engine to mark the log as 'in use' until you read it, or it may be overwritten. Only transactional replication can do this sort of special marking.
数据比较.依靠时间戳列来检测更改.也是基于拉取的，非常具有侵入性，并且在检测删除时存在问题.
Data compare. Rely on timestamp columns to detect changes. Is also pull based, quite intrussive and has problems detecting deletes.
应用层.这是理论上最好的选择，除非应用程序范围之外的数据发生了变化，在这种情况下它会崩溃.在实践中，总是在应用范围之外发生变化.
Application Layer. This is the best option in theory, unless there are changes occuring to the data outside the scope of the application, in which case it crumbles. In practice there are always changes occuring outside the scope of the application.
触发器.最终，这是唯一可行的选择.所有基于触发器的更改机制都以相同的方式工作，它们将更改通知排队发送到监控队列的组件.
Triggers. Ultimately, this is the only viable option. All change mechanisms based on triggers work the same way, they queue up the change notification to a component that monitors the queue.
总是有人建议做一个紧密耦合的同步通知(通过 xp_cmdshell、xp_olecreate、CLR、WCF 通知等等)，但所有这些方案在实践中都失败了，因为它们存在根本性的缺陷:
- 它们不考虑事务一致性和回滚
- 它们引入了可用性依赖关系(除非通知的组件在线，否则 OLTP 系统无法继续)
- 它们的执行非常糟糕，因为每个 DML 操作都必须等待某种形式的 RPC 调用完成
There are always suggestions to do a tightly coupled, synchronous notification (via xp_cmdshell, xp_olecreate, CLR, notify with WCF, you name it), but all these schemes fail in practice because they are fundamentally flawed:
- they do not account for transaction consistency and rollbacks
- they introduce availability dependencies (the OLTP system cannot proceed unless the notified component is online)
- they perform horribly as each DML operation has to wait for an RPC call of some form to complete
如果触发器实际上并没有主动通知侦听器，而只是将通知排队，则在监视通知队列时存在问题(当我说队列"时，我指的是充当队列的任何表).监控意味着在队列中拉取新条目，这意味着正确平衡检查频率与更改负载，并对负载峰值做出反应.这根本不是小事，实际上是非常困难的.但是，SQL Server 中有一个语句具有阻塞语义，无需拉取，直到更改可用:等待(接收).这意味着服务代理.您在帖子中多次提到 SSB，但您理所当然地害怕部署它，因为存在巨大的未知数.但事实是，到目前为止，它最适合您所描述的任务.
If the triggers do not actually actively notify the listeners, but only queue up the notifications, there is a problem in monitoring the notifications queue (when I say 'queue', I mean any table that acts as a queue). Monitoring implies pulling for new entries in the queue, which means balancing the frequency of checks correctly with the load of changes, and reacting to load spikes. This is not trivial at all, actually is very difficult. However, there is one statement in SQL server that has the semantics to block, without pulling, until changes become available: WAITFOR(RECEIVE). That means Service Broker. You mentioned SSB several times in your post, but you are, rightfuly so, scared of deploying it because of the big unknown. But the reality is that it is, by far, the best fit for the task you described.
您不必部署完整的 SSB 架构，在这种架构中，通知一直传送到远程服务(无论如何，这都需要远程 SQL 实例，甚至是 Express 实例).您需要做的只是将检测到更改的那一刻(DML 触发器)与传递通知的那一刻(提交更改之后)解耦.为此，您只需要一个本地 SSB 队列和服务.在触发器中，您SEND 向本地服务发送更改通知.原始DML事务提交后，服务过程激活并下发通知，例如使用 CLR.您可以在 Asynchronous T-SQL.
You do not have to deploy a full SSB architecture, where the notificaition is delivered all the way to the remote service (that would require a remote SQL instance anyway, even an Express one). All you need to accomplice is to decouple the moment when the change is detected (the DML trigger) from the moment when the notification is delivered (after the change is commited). For this all you need is a local SSB queue and service. In the trigger you SEND a change notification to the local service. After the original DML transaction commits, the service procedure activates and delivers the notification, using CLR for instance. You can see an example of something similar to this at Asynchronous T-SQL.
如果你沿着这条路走下去，你需要学习一些技巧来实现高吞吐量，并且你必须理解 SSB 中消息的有序传递的概念.我建议您阅读以下链接:
If you go down that path there are some tricks you'll need to learn to achieve high troughput and you must understant the concept of ordered delivery of messages in SSB. I reommend you read these links:
关于检测更改的方法，SQL 2008 显然添加了新选项:更改数据捕获和更改跟踪.我强调显然"，因为它们并不是真正的新技术.CDC 使用日志阅读器并基于现有的事务复制机制.CT 使用触发器并且与现有的 Merge 复制机制非常相似.它们都适用于需要同步的偶尔连接的系统，因此不适合实时更改通知.他们可以填充变更表，但您需要完成监控这些表的变更的任务，这正是您开始的地方.
About the means to detect changes, SQL 2008 apparently adds new options: Change Data Capture and Change Tracking. I emphasizes 'apparently', since they are not really new technologies. CDC uses log reader and is based on the existing Transactional replication mechanisms. CT uses triggers and is very similar to existing Merge replication mechanisms. They are both intended for occasionally connected systems that need to sync up and hence not appropiate for real-time change notification. They can populate the change tables, but you are left with the task to monitor these tables for changes, which is exactly from where you started.
这篇关于如何将数据库表更改(sql 2005)通知 Windows 服务(c#)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
