SQL Server 2008 R2 – Is Route Creation Mandatory for Event Notification?

service-brokersql-server-2008-r2

I'm starting with service broker. Already managed to setup an event notification for blocked processes based on this answer and it's working flawlessly. Then I wanted to setup another one to notify for deadlocks and I see a CREATE ROUTE statement:

-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
    WITH SERVICE_NAME = 'DeadLockNotificationsService',
    ADDRESS = 'LOCAL';
GO

But on previous one I did not ran this step, it was not included and it works. Looking on several places here, here, here, here and some other places I see the create route step is not always included. For dynamic routing seems mandatory of course. Reading on MSDN-Create route is not clear for me if it is mandatory or not and when it should be included.

Best Answer

If you are using WMI event alerts for server events then service broker is not required as it uses Service Broker SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in msdb and Service Broker is enabled by default for msdb for 2005 and up.

Read up on - understanding WMI provider for server events

To Answer your questions :

But I see lot of recommendations to use service broker too. What's the difference between them?

Its your choice to use either Service Broker or WMI Providers for server events. I find WMI Providers for server events more easy to implement.

Basically, using Event notifications allows you to capture information using SQL Server Service Broker by creating a service and queue for the events. For e.g. if you set up Event notification to capture DEADLOCK GRAPH then 3 service broker objects are created :

  1. A QUEUE to hold up DEADLOCK_GRAPH event messages

  2. A SERVICE to route the messages to the queue and

  3. An Event NOTIFICATION to capture the deadlock graph and package it in a message that is sent to the Service.

For WMI Providers for server events - Any event that can be captured through event notifications has a corresponding WMI Event Object, and any WMI management application can subscribe to these event objects. SQL Server Agent was updated to manage WMI events, through the use of WMI Query Language (WQL), a query language similar to T-SQL that is used with WMI and Agent Alerts for WMI events.

By using WMI I can avoid the hassle of creating service/queues?

Yes but it is in no way a replacement of Service Broker. Event Notification uses Service Broker. WMI Provider for server events uses WMI event objects.

WMI is broader and allows to track more event types? Or it is something else?

WMI can only handle these DDL Event Groups. Service Broker is much broader.

For monitoring and alerting on Deadlocks, you can use my script.

Refer to : Jonathan's excellent article on Handling Deadlocks in SQL Server

Related Question