可靠性的SqlDependency？ [英] SqlDependency Reliablity?

问题描述

我现在的情况是，我有需要时，新数据在数据库表时发出通知的应用程序。数据从外部源（我有没有控制权 - 这是这只集成选项）。当新的数据到达时，我的应用程序需要采取一定的措施 - 基本上查询新的数据，处理它，结果插入到本地表等

我要尽可能地避免轮询，因为数据预计在实时进行处理。 。也就是说，确保没有数据不断被错过的是1号优先级

我的问题：

1. 是的SqlDependency普遍认为是可靠的？


2. 请我需要关心的比赛条件，例如：我处理一个变化当另一个到达？


3. 当数据库被重新启动，会发生什么？将我的应用程序恢复并重新开始接收的变化，或将我需要某种将重新订阅通知定期？


4. 大部分文章我看过关于这个专题的故障安全定时器地址SQL Server 2005中，我使用的SQL Server 2008 R2。是否有优于的SqlDependency一个较新的技术？


5. （编辑）此外，如果应用程序中有哪些了？我想我会以查询关于启动？遗漏数据

解决方案

1）是，我认为这是非常可靠在它正确的目的是为了做（缓存失效）

2）号这就是为什么你只能通过发出一个查询订阅这确保有数据的获取和新更新的通知之间没有比赛

3）数据库（或实例）重启信号，所有等待的查询通知与<一个HREF =htt​​p://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx> SqlNotificationInfo 值> 重新启动。阅读如何的SqlDependency并且是基于更好地了解查询通知。由于的SqlDependency 持开放连接到数据库的时候，数据库的可用性将的SqlDependency 进行检测，甚至之前的任何明确查询通知

4）号更多关于这进一步下跌...

5）没有错过了数据。查询通知（因此的SqlDependency）从来没有通知您的什么的数据变化。它仅通知您的改成的。你总是应该回去看的所有的数据传回
看发生了什么变化（我是指你回的问题/回答否2）。新启动的应用程序尚未查询以开始与数据，所以没有被通知的变化。只有的之后的它第一次查询的数据可以收到通知。

从你的问题的描述，我不相信你需要查询通知。在我看来，要采取行动的任何变化，没有关系，当它发生了，即使你的应用程序没有运行即可。这当然不是缓存失效，这就是变化的跟踪。因此，你需要部署一个变化跟踪技术，如变更数据捕获或更改跟踪，这两者都是SQL服务器2008及更高版本（SQL Server 2005中不可用）。在SQL Server 2005的情况并不少见部署触发和排队消息服务代理来处理你正在试图处理（检测到的变化，应对新的每一行数据）同样的问题。

My current situation is that I have an application that needs to be notified when new data arrives in a database table. The data is coming from an external source (that I have no control over--this is this only integration option). When new data arrives, my application needs to take certain actions--basically query for the new data, handle it, insert the result into a local table, etc.

I want to avoid polling if possible, as the data is expected to be handled in real time. That said, making sure no data ever gets missed is the #1 priority.

My questions:

1. Is SqlDependency generally considered reliable?
2. Do I need to be concerned about race conditions, e.g. I am handling one change when another arrives?
3. What happens when the database gets rebooted? Will my app recover and start receiving changes again, or will I need a fail-safe timer of some sort that will resubscribe to notifications periodically?
4. Most of the articles I have read on the topic address SQL Server 2005. I am using SQL Server 2008 R2. Is there a newer technique that is preferred over SqlDependency?
5. (Edit)Also, What if the application goes down?  I guess I would have to query for missed data on start up?

解决方案

1) Yes, I consider it reliable as in it does correctly the purpose was designed to do (cache invalidation)

2) No. This is why you can only subscribe by issuing a query, this ensures that there is no race between the fetching of the data and new updates notifying

3) Database (or instance) restart signals all pending query notifications with an SqlNotificationInfo value of Restart. Read how SqlDependency and is based on Query Notification for a better understanding. As SqlDependency keeps an open connection to the database all the time, a database unavailability will be detected by SqlDependency even before any explicit query notification

4) No. More on this further down...

5) There is no 'missed data'. Query Notification (and hence SqlDependency) never notify you about what data changed. It only notifies you that it changed. You are always supposed to go back and read all the data back to see what had changed (and I refer you back to question/answer no. 2). A newly started application had not yet queried the data to begin with, so there is no change to be notified of. Only after it has first queried the data can it receive a notification.

From the description of your problem I'm not convinced you need query notifications. It seems to me that you want to act on any change, not matter when it happened, even if your application was not running. This is certainly not cache invalidation, it is change tracking. Therefore you need to deploy a change tracking technology, like Change Data Capture or Change Tracking, both of which are SQL Server 2008 and later only (not available in SQL Server 2005). With SQL Server 2005 is not uncommon to deploy a trigger and queue a message for Service Broker to handle the same problem you are trying to handle (detect changes, react to each row of new data).

这篇关于可靠性的SqlDependency？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！