MSSQL Service Broker

Introduced with SQL Server 2005, Service Broker adds reliable, scalable, distributed, asynchronous functionality to database applications. Provide Reliable transactional ordered message queue and can talk to other SQL Server brokers with limited GUI. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.
Configuring a Message Queue: Service Broker uses a messaging or dialog metaphor, but there’s much more to Service Broker than just the
messages. The Service Broker objects must be defined in the following order:
-Message Type: Define the legal requirements of the message.
-Contracts: Define the agreement between the initiating service and the target, including the message type, the queue, and the services.
-Queues: Hold the lists of messages.
-Services: communicate with the queue and either send or receive messages as the initiating service or the target service, respectively.
Few more important points related to the Service Broker.
-Routes: To get from one place to another Service Broker routes need to be defined. You’ll need a route to the remote server defined in the database where your Service Broker application is running, and also one to the local server, and the latter needs to be defined in the msdb database. Defining a remote destination in your application database places the route information in sys.routes, but Service Broker always
looks in msdb.sys.routes for any incoming messages to determine where they go.
-Remote Service Binding: Creates a binding that defines the security credentials to use to initiate a conversation with a remote service. A remote service binding is only necessary for initiating services that communicate with target services outside of the SQL Server instance.
-Broker Priorities: Defines a priority level and the set of criteria for determining which Service Broker conversations to assign the priority level. The priority level is assigned to any conversation endpoint that uses the same combination of contracts and services that are specified in the conversation priority. Priorities range in value from 1 (low) to 10 (high). The default is 5.
Monitoring Service Broker: While Management Studio has no visibility in the activity of a queue, nor summary page reports for the queue object, you can select directly from the queue or select a count(). In addition, there are database catalog views to shed light on the queue: sys.dm_broker_activated_tasks; sys.dm_broker_connections; sys.dm_broker_forwarded_messages; sys.dm_broker_queue_monitors
SQL Trace/Profiler has a Broker event class with 10 Service Broker–related events that can be traced.

Leave a Reply

Your email address will not be published. Required fields are marked *