656
Part V: Enterprise Data Management
- Grant CONNECT permission on the Service Broker endpoint to PUBLIC or to a login.
- Ensure that the msdb system database contains the AutoCreatedLocal route or a
route to the specifi ed service.
For a service in an Availability Group to send remote messages, there are two main require-
ments as follows:- Create a route to the target service.
- Ensure that the msdb system database contains the AutoCreatedLocal route or a
route to the specifi ed service.
For more information on the requirements for AlwaysOn support in Service Broker refer to
http://msdn.microsoft.com/en-us/library/hh710058.aspx.Poison Message Handling
A poison message is a message that has exceeded the maximum number of delivery attempts
to a target service. A message is considered a poison message when it is continuously being
added back to the message queue, after the transaction that it is part of is rolled back due
to an error. Service Broker provides automatic poison detection and handling. By default,
it will disable all queues from where messages were received by a transaction that is rolled
back fi ve times.Poison message handling can now be controlled by enabling or disabling it via
CREATE QUEUE and ALTER QUEUE statement using the POISON_MESSAGE_
HANDLING (STATUS = ON | OFF) clause.A new column named is_poison_message_handling_enabled has been added to sys.service_
queues to indicate whether poison message handling is enabled or disabled.When poison handling is set to OFF, message queues are not disabled automatically when
they are involved in a poison message transaction.Message Enqueued Time
A new column named message_enqueue_time has been added to the Queues to show how
long a message has been in the queue.Monitoring and Troubleshooting Service Broker
SQL Server has no user interface to monitor Service Broker activity. You can however make
use of one or more of the following resources described in Table 25-1.c25.indd 656c25.indd 656 7/31/2012 9:21:38 AM7/31/2012 9:21:38 AM
http://www.it-ebooks.info