What is service broker? Disadvantages of Service Broker and Trouble shooting in SQL Server

What is service broker? 

Service Broker is a process of sending and receiving guaranteed, asynchronous messages by using extensions to the 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.

Service Broker in Microsoft SQL Server is a new technology that provides messaging and queuing functions between instances. 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.

Disadvantages of SQL Server Service Broker in contrast to MSMQ for following criteria.

1. Development
2. Troubleshooting
3. Performance (let's say we need to process 100,000 messages daily, having avg size around 25 KB)
4. Scalability

Development

Service Broker requires a lot of boilerplate, which is a pain, but unless you're planning on having lots of different queues it's manageable. Sql Server projects in Visual Studio take a lot of the pain of deploying away.

Troubleshooting

Service Broker is a black box - messages go in, and they usually come out, but if they don't then troubleshooting can be problematic, and all you can do is query the system views - and sometimes you simply can't find out what has gone wrong. This is annoying, but MSMQ has the same kind of issues..

Performance

Service Broker performance is excellent. We are processing a lot more than 100,000 messages per day, more than 30,000 per hour at our SLA load, and our message sizes are large. I would estimate we process close to 100,000 messages per hour during heavy load testing.

For best performance I would advise you to use a Dialog Pool like this one as creating a Service Broker dialog can be an expensive operation.

You will also want to use the Error Handling procedures detailed by Remus Rusanu. (If you do use Service broker, you might as well read everything Remus has written on the subject before you start, as you'll end out reading it eventually!)

Scalability

You can certainly use more than one server to scale up if required, though we haven't had to do so, and from the load size you mention I don't think you would need to either.

I don't think I have really managed to answer your question, as I haven't highlighted enough disadvantages of Service Broker queues. I would say the impenetrable nature of its internal workings is the thing that most annoys me - when it works, it works very well, but when it stops working it can be very hard to figure out why. Also, if you have a lot of messages in a queue, using ALTER QUEUEtakes a very long time to complete.

1. Configuring the Service Broker.
2. Sending and Receiving Messages.

Configuring the Service Broker

To configure the service broker to send and receive messages between databases (either in local/remote instances), it needs a few components in place to start the service. These are listed below.

1. Enable the Service Broker on the database
2. Create valid Message Types.
3. Create a Contract for the conversation.
4. Create Queues for the Communication.
5. Create Services for the Communication.

Note: For my test I've created a database named ServerBrokerTest, which I'm going to use in this article. I've used a single database to make this easy to understand.  We can call it a single database conversation.

---

1. Enabling Service Broker

Service broker is a database level feature not an instance level. So it has to be enabled before starting the other configurations.

Use the following code to enable the Service Broker.

--Enabling service broker USE master ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER;

Enabling service broker option can be verified with the Is_Broker_Enabled column (set to true [1]) in sys.databases catalog view.

---

2. Create Valid Message Types

Service Broker needs specific Message Types to send and receive messages, which have to be created before the communication starts. The initiator (Sender/Requestor) and the target (Receiver/Reply) have to use the same Message Type or else the communication will fail. The most common used Message Type is WELL_FORMED_XML.

The following example code is used to create a Message Type in the same database for the Initiator (Requestor/Sender) and the Target (Reply/Receiver). Use the following code to create the Message Types.

--Create Message Types for Request and Reply messages USE ServiceBrokerTest -- For Request CREATE MESSAGE TYPE [//SBTest/SBSample/RequestMessage] VALIDATION=WELL_FORMED_XML; -- For Reply CREATE MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] VALIDATION=WELL_FORMED_XML;

---

3. Create a Contract for the Conversation

Service Broker requires a contract to send and receive messages in a single or between multiple databases. The contract will ensurewhich Message Type is going to be used to send and receive messages between the Initiator (Requestor/Sender) and the Target (Reply/Receiver). Use the following code to create the contract.

--Create Contract for the Conversation USE ServiceBrokerTest CREATE CONTRACT [//SBTest/SBSample/SBContract] ( [//SBTest/SBSample/RequestMessage] SENT BY INITIATOR ,[//SBTest/SBSample/ReplyMessage] SENT BY TARGET );

---

4. Create Queues for the Communication

The Service Broker Queue is a Message Storage container which is used to keep the messages while sending and receiving. The below code creates two queues, one is the Initiator (Requester/Sender) and the other is Target (Receiver/Replier). Queues will be used by the Service Broker Services.

Use the following code is to create the Queues.

USE ServiceBrokerTest --Create Queue for the Initiator CREATE QUEUE SBInitiatorQueue; --Create Queue for the Target CREATE QUEUE SBTargetQueue;

---

5. Create Services for the Communication

The Service Broker Services route the Messages to the specific Queues. The Service and Queue are bonded to each other. When the Initiator or the Target send a Message, the service will route the messages to the appropriate Queues.

Use the following code to create the Service Broker Service.

--Create Service for the Target and the Initiator. USE ServiceBrokerTest --Create Service for the Initiator. CREATE SERVICE [//SBTest/SBSample/SBInitiatorService] ON QUEUE SBInitiatorQueue; --Create Service for the Target. CREATE SERVICE [//SBTest/SBSample/SBTargetService] ON QUEUE SBTargetQueue ([//SBTest/SBSample/SBContract]);

Note: In the above code I've not specified the Contract name for the Initiator Service, but I specified for the Target Service, which means if no Contact name is specified on a Service then the Service can only initiate conversations but no other Services can use that service as a Target (Reply/Receiver).

---

Sending and Receiving Messages

In this section, I'll describe how to Send (Request - from the Initiator) and Reply (from the Target) and Receive a message between the Initiator and the Target. We can understand the conversation cycle between the Initiator and Target.

Sending Message to Target

The following code sends a request message from the Initiator to the Target. The code can be split into three parts.

1. Determine the Services and contract.
2. Prepare the Message
3. Send the Message.

Sending a message is a single transaction, which includes all 3 items above.

--Sending a Request Message to the Target USE ServiceBrokerTest DECLARE @InitDlgHandle UNIQUEIDENTIFIER DECLARE @RequestMessage VARCHAR(1000) BEGIN TRAN --Determine the Initiator Service, Target Service and the Contract BEGIN DIALOG @InitDlgHandle FROM SERVICE [//SBTest/SBSample/SBInitiatorService] TO SERVICE '//SBTest/SBSample/SBTargetService' ON CONTRACT [//SBTest/SBSample/SBContract] WITH ENCRYPTION=OFF; --Prepare the Message SELECT @RequestMessage = N'<RequestMessage> Send a Message to Target </RequestMessage>'; --Send the Message SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//SBTest/SBSample/RequestMessage] (@RequestMessage); SELECT @RequestMessage AS SentRequestMessage; COMMIT TRAN

Note: TO SERVICE needs to be specified in the single quotes because it is case sensitive, service broker uses a byte-by-byte comparison with the Target service name. The above code will give the below result set.

SentRequestMessage <RequestMessage> Send a Message to Target </RequestMessage>

Receiving and Sending Message to Initiator

The following code receives a message from the Initiator and sends a Reply message to it.

--Receiving a Message and sending a Reply from the Target USE ServiceBrokerTest DECLARE @TargetDlgHandle UNIQUEIDENTIFIER DECLARE @ReplyMessage VARCHAR(1000) DECLARE @ReplyMessageName Sysname BEGIN TRAN; --Receive message from Initiator RECEIVE TOP(1) @TargetDlgHandle=Conversation_Handle ,@ReplyMessage=Message_Body ,@ReplyMessageName=Message_Type_Name FROM SBTargetQueue; SELECT @ReplyMessage AS ReceivedRequestMessage; -- Confirm and Send a reply IF @ReplyMessageName=N'//SBTest/SBSample/RequestMessage' BEGIN DECLARE @RplyMsg VARCHAR(1000) SELECT @RplyMsg =N'<RplyMsg> Send a Message to Initiator</RplyMsg>'; SEND ON CONVERSATION @TargetDlgHandle MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] (@RplyMsg); END CONVERSATION @TargetDlgHandle; END SELECT @RplyMsg AS SentReplyMessage; COMMIT TRAN;

The above will give two result sets as below, 1 is received message from the Initiator and 2 is the sent message to the Initiator from the Target.

ReceivedRequestMessage <RequestMessage> Send a Message to Target </RequestMessage> SentReplyMessage <RplyMsg> Send a Message to Initiator</RplyMsg>

Receiving a Reply Message from the Target

The below code receives a reply message from the Target.

--Receiving Reply Message from the Target. USE ServiceBrokerTest DECLARE @InitiatorReplyDlgHandle UNIQUEIDENTIFIER DECLARE @ReplyReceivedMessage VARCHAR(1000) BEGIN TRAN; RECEIVE TOP(1) @InitiatorReplyDlgHandle=Conversation_Handle ,@ReplyReceivedMessage=Message_Body FROM SBInitiatorQueue; END CONVERSATION @InitiatorReplyDlgHandle; SELECT @ReplyReceivedMessage AS ReceivedRepliedMessage; COMMIT TRAN;

Below is the output of the above transaction, which confirms that the reply message received from the Target.

ReceivedRepliedMessage <RplyMsg> Send a Message to Initiator</RplyMsg>

That is pretty much all there is to setting up Service Broker and sending and receiving some simple messages.

---

Some Useful Catalog Views

The following catalog views are helpful to check the usage of the Service Broker Objects.

--Checking the usage of the Messages, Contracts and Queues using System views. USE ServiceBrokerTest SELECT * FROM sys.service_contract_message_usages SELECT * FROM sys.service_contract_usages SELECT * FROM sys.service_queue_usages

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. 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.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.

---------------------------- Service Broker -----------------------
-- In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
-------------------------------------------------------------------CREATE DATABASE ServiceBrokerTest
GOUSE ServiceBrokerTest
GO-- Enable Service BrokerALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO-- Create Message TypeCREATE MESSAGE TYPE SBMessage
VALIDATION = NONE
GO-- Create ContractCREATE CONTRACT SBContract(SBMessage SENT BY INITIATOR)GO-- Create Send QueueCREATE QUEUE SBSendQueue
GO-- Create Receive QueueCREATE QUEUE SBReceiveQueue
GO-- Create Send Service on Send QueueCREATE SERVICE SBSendServiceON QUEUE SBSendQueue (SBContract)GO-- Create Receive Service on Recieve QueueCREATE SERVICE SBReceiveServiceON QUEUE SBReceiveQueue (SBContract)GO-- Begin Dialog using service on contractDECLARE @SBDialog uniqueidentifierDECLARE @Message NVARCHAR(128)BEGIN DIALOG CONVERSATION @SBDialogFROM SERVICE SBSendServiceTO SERVICE 'SBReceiveService'ON CONTRACT SBContractWITH ENCRYPTION = OFF-- Send messages on DialogSET @Message = N'Very First Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Second Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Third Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)GO-- View messages from Receive QueueSELECT CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Clean UpUSE master
GODROP DATABASE ServiceBrokerTest
GO

-------------------Script for SERVICE BROKER------------







select Is_Broker_Enabled,* from sys.databases 

where Is_Broker_Enabled=1

SELECT * FROM sys.service_contract_message_usages 

SELECT * FROM sys.service_contract_usages

SELECT * FROM sys.service_queue_usages

-- Message Types

SELECT * 

FROM sys.service_message_types;

-- Contracts

SELECT * 

FROM sys.service_contracts;

-- Queues

SELECT * 

FROM sys.service_queues;

-- Services

SELECT * 

FROM sys.services;

-- Endpoints

SELECT * 

FROM sys.endpoints;

SELECT conversation_handle, is_initiator, s.name as 'local service', 

far_service, sc.name 'contract', state_desc

FROM sys.conversation_endpoints ce

LEFT JOIN sys.services s

ON ce.service_id = s.service_id

LEFT JOIN sys.service_contracts sc

ON ce.service_contract_id = sc.service_contract_id;

-- Error messages in the queue

SELECT * 

FROM sys.transmission_queue;

Trouble Shooting in Service Broker:

Service Broker offers a number of techniques to troubleshoot your Service Broker applications whether they are in development or production stages of the life cycle.  In addition, to troubleshooting the application it is also wise to validate the Service Broker 'infrastructure' objects are created as expected as well to ensure your application is operating as expected.  Below are a set of code, objects and techniques that can be used to troubleshoot your Service Broker applications.

Validate Infrastructure Objects

Service Broker is dependent on five of infrastructure objects in order to operate properly.  As such, once you have created your Service Broker objects, it is wise to validate that all of the objects have been created.  The queries below would validate that the objects exist.  These queries should be issued in both the initiator and target databases to validate that the objects exist in both SQL Server environments.

-- Message TypesSELECT * FROM sys.service_message_types; -- ContractsSELECT * FROM sys.service_contracts; -- QueuesSELECT * FROM sys.service_queues; -- ServicesSELECT * FROM sys.services; -- EndpointsSELECT * FROM sys.endpoints;

Troubleshooting the Service Broker Queues

Once you start adding messages to your queues and receiving data from your queues, it is necessary to ensure you are not having any issues with your endpoints, services and contracts.  If you are experiencing issues, then this query may identify the conversations that are having issues and additional research may be necessary to troubleshoot the issues further.

SELECT conversation_handle, is_initiator, s.name as 'local service', far_service, sc.name 'contract', state_descFROM sys.conversation_endpoints ceLEFT JOIN sys.services sON ce.service_id = s.service_idLEFT JOIN sys.service_contracts scON ce.service_contract_id = sc.service_contract_id;

Another key queue to keep in mind when troubleshooting Service Broker is the sys.transmission_queue.  This is the queue that receives any records that are not written to the user defined queue appropriately.  If your overall Service Broker infrastructure is setup properly, then this may be the next logical place to start troubleshooting the issue.  You are able to validate the conversation as well as take a peek at the xml (message_body) and find out the error message (transmission_status) for the record.

-- Error messages in the queueSELECT * FROM sys.transmission_queue;

One final item is to query the queues that you have created to validate data is being written to or received from the queues.  Depending on the application, records should be added and removed from your user defined queues by default.  Be sure to issue simple SELECT * FROM statements against your queues for validation of the overall process.

Removing all records from the sys.transmission_queue

Odds are that if your Service Broker infrastructure is setup properly and records are in the sys.transmission_queue, they probably need to be removed to continue to build and test the application.  As such, the END CONVERSATIONcommand should be issued with the conversation handle and the 'WITH CLEANUP' parameter.  Below is an example command:

END CONVERSATION 'conversation handle' WITH CLEANUP;

*** NOTE *** - This command should be issued during development and testing phases of the project and not arbitrarily when the application is in production because the associated data will be lost.

SQL Server Error Log

The next place that should be researched when troubleshooting Service Broker is the SQL Server error log.  Some of the issues may not be written to the views above, so the SQL Server error log is another valuable source of information.  Below outlines two examples, although based on the issue, the errors could differ:

Date 1/1/2007 00:00:00 AM Log SQL Server (Current - 1/1/2007 00:00:00 AM Source spid62 Message Service Broker needs to access the master key in the database 'YourDatabaseName'. Error code:25. The master key has to exist and the service master key encryption is required Date 1/1/2007 00:00:00 AM Log SQL Server (Current - 1/1/2007 00:00:00 AM Source spid16 Message The Service Broker protocol transport is disabled or not configured.

TRY and CATCH Blocks for Error Handling

Since Service Broker is coded via T-SQL another technique to take advantage of in your code is using the TRY and CATCH block to capture errors and handle them appropriately.  Check out the SQL Server 2005 - Try Catch Exception Handling to learn about the coding technique and the associated  information that is available from a troubleshooting perspective.

Notification services is one of the deprecated features in SQL Server 2008, essentially it is supported for now but will not be in the next release of SQL Server. If you are using notification services currently and you are looking to upgrade to SQL Server 2008 then you can get it from here.

Alternatives for Notification Services in SQL Server 2008:

But why deprecate the feature? Basically not many people were using and it wasn’t seen as being easy to use or flexible enough.  But what are your migration options if you are using it?

That’s going to depend on what you are using it for:

• Alerting of system health. A good alternative here would be use the policy management features in SQL Server 2008 and either schedule them (which is built into the UI) or set them to OnDemand: notify if the policy supports that (as per this post of mine.
• Audit.  You can trap audit details to file, or the event logs (including the security log)in SQL Server 2008.  From there you could run a report on a schedule to show you any issues or use the capabilities of the event logs.  Change Tracking can do similar things for reporting on changes to actual data
• Performance.  Extended events and the Data collection elements of SQL Server 2008 will allow you to trap detailed telemetry of what's happening to your database and the wider context of what’s happening server.

Having collected the information you want to track, the challenge then is to get this information back to you when things go wrong.  This could simply be a case of making use of an agent job to do a test to see if there’s a problem, and then to conditionally running a reporting services report of the problem(s), or a send mail procedure to do it that way.  DDL triggers might also be an option, so there are lots of options but no obvious single thing to take your existing setup and migrate it to any of the above I’m afraid.