I have many operations in the database that need to trigger application code. Currently I am using database polling, but I hear that SQL Server Service Broker can give me MSMQ-like functionality.
SSB (SQL Service Broker) has a feature named Activation that enabled a stored procedure to be attached to a queue. SQL Server will run this procedure internally when there are messages to consume in a queue. The queue attached procedure can be a CLR procedure, enabling for managed code business logic modules to run (C#, VB.Net etC).
An alternative to an internal activated stored procedure is to have an external client 'listen' on a queue with a WAITFOR(RECEIVE ... ) statement. This syntax is special for SSB and does a non-pooling block until there are messages to receive. Applications then consume the received messages as an ordinary T-SQL result set (like a SELECT). There is also a sample of an External Activator for Service Broker that leverages the event notification mechanism to know when to start an application for consuming messages from a queue.
If you want to see a sample of T-SQL code that leverages SSB internal Activation check out Asynchronous procedure execution.
To answer your questions:
Can I listen to SQL Server Service Broker queues from .NET applications running on a different machine?
Yes.
If so, should I do it?
If not, what would you recommend?
You might consider using SqlDependency. It uses Service Broker behind the scenes, but not explicitly.
You can register a SqlDependency object with a SELECT query or a stored procedure. If another command changes the data that was returned from the query, then an event will fire. You can register an event handler, and run whatever code you like at that time. Or, you can use SqlCacheDependency, which will just remove the associated object from the Cache when the event fires.
You can also use Service Broker directly. However, in that case you will need to send and receive your own messages, as with MSMQ.
In load-balanced environments, SqlDependency is good for cases where code needs to run on every web server (such as flushing the cache). Service Broker messages are better for code than should only run once -- such as sending an email.
In case it helps, I cover both systems in detail with examples in my book (Ultra-Fast ASP.NET).
A easy to use queue library for SQL Service Broker based on rhino-queues