SqlDependency vs SQLCLR调用WebService [英] SqlDependency vs SQLCLR call to WebService

问题描述

我有一个桌面应用程序，任何表更改均应通知该应用程序。因此，我发现只有两种适合我的情况的解决方案： SqlDependency 和 SQLCLR 。 （我想知道.NET堆栈中是否有更好的。）我已经构建了这两种结构并使它们工作。我只能够比较s&＃x332; n&＃x332; gl&＃x332; e&＃x332;的持续时间从SQL Server到客户端的响应。

I have a desktop application which should be notified on any table change. So, I found only two solutions which fits well for my case: SqlDependency and SQLCLR. (I would like to know if there is better in .NET stack) I have built the both structure and made them work. I only able to compare the duration of a s̲i̲n̲gl̲e̲ response from SQL Server to the client.

持续时间：：从100毫秒到4秒

Duration: from 100ms to 4 secs

持续时间：：从10毫秒到150毫秒

Duration: from 10ms to 150ms

我希望这种结构能够处理高速率通知*，我已经阅读了一些SO和博客文章（例如：此处），并且还警告一位同事，在大规模请求时，SqlDependency可能会出错。 此处，MS提供了一些功能我没有得到的那可能是我的问题的另一种解决方案。

I would like this structure to be able to deal with high rate notifications*, I have read a few SO and blog posts (eg: here) and also am warned from a colleague that on mass requests SqlDependency may go wrong. Here, MS offers something which I didn't get that may be another solution to my problem.

*：不是所有时间，而是一个季节；在1-2台服务器上每秒50-200个请求。

基于较高的通知率和性能，这两个中的哪一个我应该继续下去，还是有其他选择？

On the basis of a high rate of notifications and in parallel with performance, which of these two should I go on with, or is there another option?

推荐答案

SqlDependency （即查询通知）和SQLCLR（即调用）通过触发器的Web服务）将针对该流量（每秒50-200 req）起作用。实际上，这两种选择在这些情况下都是非常危险的。

Neither SqlDependency (i.e. Query Notifications) nor SQLCLR (i.e. call a Web Service via a Trigger) is going to work for that volume of traffic (50-200 req per sec). And in fact, both options are quite dangerous at those volumes.

两个链接页面（在SoftwareEngineering.StackExchange.com和TechNet文章上的链接页面）中给出的建议都是所有更好的选择。有关从ms sql数据库获取推送通知到服务器的最佳方法（即每隔几秒钟轮询一次的自定义队列表）与计划通知 TechNet文章（该文章使用Service Broker来处理队列的处理） ）。

The advice given in both linked pages (the one on SoftwareEngineering.StackExchange.com and the TechNet article) are all much better options. The advice on Best way to get push notifications to server from ms sql database (i.e. custom queue table that is polled every few seconds) is very similar to option #1 of the Planning for Notifications TechNet article (which uses Service Broker to handle the processing of the queue).

我最喜欢排队的想法（完全自定义或使用Service Broker），并且已在高度事务性的系统上使用了完全自定义队列（很容易获得预期的数量）取得了很大的成功。这两种选择之间的优缺点（当然，如我所见）是：

I like the queuing idea (fully custom or using Service Broker) the best and have used fully custom queues on highly transactional systems (easily the volume you are anticipating) with much success. The pros and cons between these two options (as I see them, of course) are:

• 服务经纪人
  
  
  
  
  • 专业人士：现有（并经过验证）的框架（可以扩展并绑定到事务中）
  
  
  • 缺点：并非总是易于配置或管理/调试，无法轻松地在1秒内将200个独立事件聚合为一条消息（每个触发事件仍为1条消息）
  
  
  
  
  • Pro：可以将多个同时触发事件聚合到单个消息中给客户端（即，轮询服务获取自上次轮询以来发生的任何更改），可以利用变更跟踪/变更数据捕获作为已更改内容的来源，因此您可能不需要构建队列表。
  
  
  • 缺点：仅具有可扩展性做到这一点（可能与Service Broker一样好，甚至更好，但要高度依赖于您的技能和经验来实现），需要对e进行全面测试dge情况以确保队列处理不会丢失或重复计数事件。
  
  

  您可能可以将Service Broker与变更跟踪/变更检测结合使用。如果有一种简便的方法可以确定最后处理的更改（如更改跟踪 /更改数据捕获表中所述），则可以将SQL Server代理作业设置为每隔几秒钟轮询一次，如果您找到新的更改，然后将所有更改保存到一条消息中，然后发送给Service Broker。

  You might be able to combine Service Broker with Change Tracking / Change Detection. If there is an easy-enough way to determine the last change processed (change as noted in Change Tracking / Change Data Capture table(s)), then you can set up a SQL Server Agent job to poll every few seconds, and if you find that new changes have come in, then grab all of those changes into a single message to send to Service Broker.

  一些文档可帮助您入门：

  Some documentation to get you started:

  
  
  • 跟踪数据更改（包含更改跟踪和更改数据捕获）
  
  
  • SQL Server服务代理
  
  
  • Track Data Changes (covers both Change Tracking and Change Data Capture)
  • SQL Server Service Broker

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