I've been wondering what happened to the QueryNotification dispatcher proc that's used by SqlDependency in ADO.NET (and in ASP.NET with SQL Server 2005). The one that I wrote about in one of my MSDN articles. Lately, although the dispatcher proc and assembly didn't show up in MSDB, the function kept working. I wondered why, how, what was happening.
This morning I installed the July CTP (which hasn't been reported to work with SQL Server 2005, so I didn't try) and found why. It's been "eased out".
There is now a static method called Start on SqlDependency (and a matching Stop method) that starts off ADO.NET's dependency listener. This creates a Service Broker queue and service (by default) and starts listening on it with a WAITFOR. So the functionality is no longer a passive listener (server pushes notification) but an active listener (strange as that sounds, means client listens and pulls notification). You pass a connection string into Start, but it looks like it will multiplex listeners on the same (1) connection.
Some nice repercussions of this (offhand, there may be more) are:
No dependency of this feature on having SQLCLR enabled on server
No possibility of DOS attacks on client
No firewall issues since the listener uses one "normal" connection
More later…back to the revising/editing table for me.