Some useful tips when working with the Service Broker in SQL Server 2005

  • Article

With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

This documentexplains in detail, the terms used with the Service Broker.

Also you can develop the services as applications to handle the application requirements for queuing or asynchronous communications. Service Broker application is made up of below,

· One or more programs that implement a task or related set of tasks. Outside SQL Server, applications can be written in any programming environment that can run Transact-SQL statements in SQL Server. Inside SQL Server, applications can be written as stored procedures using Transact-SQL or a common language runtime (CLR) compliant language.

· A service that exposes the tasks to other services. A service is a Service Broker object that provides an addressable name for a set of related tasks. Other services start conversations with this service to perform the tasks.

· A contract and message types that define the structure and direction of the messages that are used in communications between the services.

· A queue to hold messages for the service.

This document has a walkthrough on the steps to build an application using the service broker. This is very useful for developing the applications that can use this functionality.

Below are some useful tips when you are a Database Administrator, working with the Service Broker,

- Service Broker need not be enabled for the master database.

- Sometimes the query to enable service broker gets stuck due to blocking. So needs to check for blocking on the database.

- Verify that the service setup, spelling, routes in sys.routes are configured correctly. Each database contains the local routing table, sys.routes, for conversations begun in that database. For conversations that originate within the SQL Server instance, SQL Server searches the routing table in the database that created the conversation. For conversations that arrive from outside the instance, SQL Server searches msdb.sys.routes.

- Check the SQL Server error log and event logs for any messages related to the Service Broker.

- Capture a profiler trace with the Broker events, Query Notifications, Security Broker events, errors/warnings, and RPC/Batch completed events. This will give good idea as to exactly where you may be getting the error.

- Verify the endpoints don’t use any TCP ports that are in use by any local SQL Server instance or any other application. You can check the details of the ports being used by the SQL Server instances in the SSCM (SQL Server Configuration Manager).

- If the queries go across databases, we will need to have SET TRUSTWORTHY ON for the database. This can be set with the ALTER DATABASE command.

Some pointers to dive into Service Broker are below,

SQL Server : Service Broker Team Blog:
https://blogs.msdn.com/sql_service_broker/default.aspx

Security Considerations for Service Broker
https://msdn2.microsoft.com/en-us/library/ms166059.aspx

To understand what service broker can do for you, start from this article.

https://msdn2.microsoft.com/en-US/library/ms166104.aspx

The architecture of service broker is highly scalable and provides granular security features. Learn more about it by reading this article.
https://msdn2.microsoft.com/en-us/library/ms166125(SQL.90).aspx

For security considerations for Service Broker, refer to this link.
https://msdn2.microsoft.com/en-us/library/ms166059(SQL.90).aspx

Some very good information on the Service Broker architecture by Roger Wolter,
https://blogs.msdn.com/rogerwolterblog/archive/2006/09/08/Roger.aspx

Sanjaya Padhi
SE, Microsoft SQL Server