CODEWITHSUNDEEP
c#.netsql-server-2008
NickV
NickV

Reputation: 649

SQL Server 2008 Event-Driven Architecture

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.

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?

Upvotes: 4

Views: 1561

Answers (1)

Jason Kleban
Jason Kleban

Reputation: 20788

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

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.

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.

Upvotes: 3

Related Questions