如何通知数据库表更改（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.
那种后期处理是相当重的为好，以至于窗户侦听器服务实际上是要到多台机器的传递处理。然而，应用程序，这部分已经投入运行，completetly异步，而不是我需要帮助 - 我只是想简单地提到这一点，因为它影响的设计决策，我们不能只加载了一些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＃code一些处理的远程服务器上
So, The simple problem remains: data changes in a table, I want to do some processing in c# code on a remote server.
在present我们已经想出了使用SQL触发器，它执行xp_cmdshell的劳克这引起了该窗口服务正在收听一个事件的exe文件。这只是感觉不好。
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触发器，它可以调用Web服务，但是这个网上有一个关于它是一个糟糕的方式去了解它，尤其是当性能至关重要这么多的警告。
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我们不会depnd桌子上的变化，但宁愿拦截我们的应用程序内调用，并从那里通知服务，但遗憾的是我们有一些遗留的应用程序在更改过的数据和监控表是唯一此刻集中的地方。
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.
任何帮助将是最AP preciated。
Any help would be most appreciated.
摘要：
- 需要实时 表中的数据变化作出反应
- 性能是至关重要
- 流量高量有望
- 轮询和计划的任务不是一个选项（或实时）
- 实施服务代理过大（但可能是唯一的解决办法？）
- CLR code尚未排除，但如果需要建议被perfomant
- 监听器/监测器可能是远程计算机（可能是同一phyisical网）
- Need to respond to table data changes in real time
- Performance is critical
- High volume of traffic is expected
- Polling and scheduled tasks are not an option(or real time)
- Implementing service broker too big (but might be only solution?)
- CLR code is not yet ruled out, but needs to be perfomant if suggested
- Listener / monitor may be remote machine(likely to be same phyisical network)
推荐答案
您真的没有很多的方法来检测SQL 2005年的变化你已经列出了一大半。
You really don't have that many ways to detect changes in SQL 2005. You already listed most of them.
查询通知即可。这是权力的SqlDependency及其衍生物，你可以阅读更多细节神秘的通知。但QN的设计的无效的结果，而不是积极主动地通知更改内容。你只知道表中有变化，不知道是什么改变了。在繁忙系统这将无法工作，因为通知会pretty多continously。
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.
数据比较。依靠时间戳列来检测的变化。也是基于拉，相当intrussive和有问题检测删除。
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服务器的一个语句，直到变化变为可用： WAITFOR（接收）。这意味着服务代理。你在你的文章中提到SSB几次，但你是，那么rightfuly，害怕因为大未知部署它。但现实是，它是，到目前为止，你所描述的任务最合适的。
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架构，其中notificaition被一路传递到远程服务（即无论如何都会需要一个远程SQL实例，即使是前preSS之一）。所有你需要帮凶当检测到变化时（DML触发器）的那一刻起，当通知交付（变化将提交后）去耦的时刻。对于这一切，你需要的是一个本地队列SSB和服务。在触发您发送更改通知到本地服务。原始的DML事务提交之后，该服务程序激活并提供该通知，使用例如CLR 。你可以看到在异步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.
如果你走这条路还有一些你需要学习如何实现高吞吐量的技巧和必须understant从国家统计局的消息有序传递的概念。我reommend你读这些链接：
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:
- Reusing Conversations
- Writing Service Broker Procedures
- SQL Connections 2007 Demo
关于手段来检测更改，SQL 2008的显然的补充新的选项：变更数据捕获和更改跟踪的。我强调明显，因为他们不是真正的新技术。 CDC使用日志读取器，并基于现有的事务性复制的机制。 CT使用触发器和非常类似于现有的合并复制机制。它们都适用于偶尔连接系统需要保持同步，因此不appropiate实时更改通知。他们可以填充变更表，但你留下来监控这些表更改任务，而这正是从你开始的地方。
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屋！
