SQL Server 2008事件驱动的体系结构 [英] SQL Server 2008 Event-Driven Architecture

问题描述

我正在创建一个基于消息的体系结构，该体系结构当前使用轮询客户端来检索消息.出于明显的原因，我想将客户端注册到SQL Server 2008，以便在将消息插入表中时收到事件.

I am creating a message-based architecture that currently uses polling clients to retrieve messages. For obvious reasons, I would like to register my clients to SQL Server 2008 in order to receive an event when a message is inserted into a table.

我一直在网上进行全方位的研究，以研究SQL Server Message Broker，CLR存储过程和StreamInsight，但似乎找不到我想要的东西:SQL Server提醒我的服务的一种方式已收到一条消息.基本上是事件驱动而不是轮询的模型.

I have been round-and-round the web researching SQL Server Message Broker, CLR Stored Procedures, and StreamInsight, but I can't seem to find what I am looking for: a way for SQL Server to alert my services that a message has been received. Basically an event-driven rather than polling model.

这存在吗?关于从哪里开始的任何想法?有例子吗?

Does this exist? Any ideas on where to start? Are there any examples?

推荐答案

是的，这确实存在.我已经成功使用SQL Service Broker.我不熟悉您列出的其他选项.

Yes, this does exist. I've had success using SQL Service Broker. I'm unfamiliar with the other options you listed.

设置SSB非常麻烦，因为有许多活动部件和细节，但是效果很好.帮助您避免轮询的主要部分是您创建并从C#调用的存储过程.在此简短过程中，将执行RECEIVE WAITFOR语句，该语句将阻塞打开的事务处理连接，直到队列中有消息可用或超时命中为止.在C#中，无论您是获得结果还是超时，请立即再次运行该过程以等待下一个项目.

Setting up SSB is a pain because there are so many moving parts and details but it works nicely. The main part that helps you avoid polling is a stored procedure that you create and call from C#. In that short procedure is a RECEIVE WAITFOR statement which blocks your open and transacted connection until a message is available in your queue OR your timeout hits. In C#, whether you get a result or a timeout immediately run the procedure again to wait for the next item.

如果可能的话，您希望将必须打开的SQL ...的连接数限制为1.如果您有多个相关方，请通过该连接推送所有内容，并通过其他方式将其分发给C#服务器.

You'll want to limit the number of open connections you have to SQL ... to 1 if possible. If you have multiple interested parties, push all their stuff through that one connection and distribute it with a C# server by some other means.

这篇关于SQL Server 2008事件驱动的体系结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！