Home > SQL Server Tips > Database Development > Configure SQL Server Service Broker for sending stored procedure data
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

DATABASE DEVELOPMENT

Configure SQL Server Service Broker for sending stored procedure data


Denny Cherry
04.16.2008
Rating: -3.50- (out of 5)


Expert advice on database administration
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


In SQL Server 2005, Microsoft introduced an exciting new feature called the Service Broker, and with it came a new concept for many database administrators. The concept, out-of-process messaging, is an application that developers have been using for many years via products such as Microsoft Message Queue (MSMQ). To set the scene, a SQL Server command is run and the data from that command needs to be processed. However, it doesn't need to be processed right away, as the user isn't expecting any output from the command. This is where message queuing comes into play.

Normally, when users fire a stored procedure in SQL Server, they must wait for the stored procedure to complete the processing of data before the next action can be completed. By using Service Broker, they can queue the actual processing of the data for a later date. In this case, the user will run a stored procedure, but instead of the data processing, we take the input data and package it as a message and send that message to a queue. We configure the queue to read the message automatically and process the data within the message.

Messages can be sent within the same database, from database to database on the same server or from one server to another server including servers across the internet. Think of the process as kind of like email. When I send an email to my friend, he receives it and will read it (processing the message) and later do something based on the content. Service Broker works in much the same way.

Four steps for setting up Service Broker to send and receive messages:

There are several components of Service Broker that must be configured before you can actually send and receive messages. For our example here, we will put messages into a single queue for automatic processing. The processing of these messages can be automatic or on-demand with a Windows application or service reading the messages from the queue.

Like all other objects, Service Broker names must


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
SQL Server Stored Procedures
Top 10 SQL Server development tips of 2008
SQL Server trigger vs. stored procedure to receive data notification
SQL Server errors, failures and other problems fixed from the trenches
SQL Server and data manipulation in T-SQL
How to use SQL Server 2008 hierarchyid data type
SQL Server stored procedures tutorial: Write, tune and get examples
Check SQL Server database and log file size with this stored procedure
SQL Server source code analysis and management adds database security
Find size of SQL Server tables and other objects with stored procedure
Troubleshoot SQL Server 2005 temporary table performance problems

Microsoft SQL Server 2005 (Yukon)
SQL Server Reporting Services Fast Guide
SQL Server Service Broker Tutorial and Reference Guide
Tips for tuning SQL Server 2005 to improve reporting performance
SQL Server consolidation: Why it's an optimization technique
Parent-child dimensions in SQL Server 2005 with Analysis Services MDX
Enforcing data integrity in a SQL Server database
SSIS error message due to installation problem on SQL Server 2005
Should you upgrade to SQL Server 2005 or SQL Server 2008?
Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005
How to configure Database Mail in SQL Server 2005 to send mail
Microsoft SQL Server 2005 (Yukon) Research

Database Development
Managing the development lifecycle with Visual Studio Team System 2008
Processing XML files with SQL Server functions
A first look at Visual Studio Team System 2008 Database Edition
How to create a SQL inner join and outer join: Basics to get started
New datetime data types in SQL Server 2008 offer flexibility
Using DATEADD and DATEDIFF to calculate SQL Server datetime values
SQL Server database design disasters: How it all starts
SQL Server database design disasters: What not to do
Secure SQL Server from SQL injection attacks
Check SQL Server database and log file size with this stored procedure

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
library  (SearchSQLServer.com)
trigger  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


be unique within the database. However, more care should be taken when setting up your Service Broker object names if you are going to be sending messages from server to server. It's usually recommended that some combination of system name and function name be put into the Service Broker object names. This ensures that the object names are unique across your enterprise. It will come in handy when you start sending messages between systems already using the Service Broker internally and you want to avoid object-naming problems. Try using a UNC style name to ensure uniqueness. For this example, we will start our object names with tcp://SearchSQLServer/.

In order to send a message from one queue to another, you must use two commands. The first is the BEGIN DIALOG command, followed by the SEND command. Use the BEGIN DIALOG command to create a conversation between the sending and receiving services. If you are using an already existing conversation, then you don't need to use the BEGIN DIALOG command. After creating the new conversation using the BEGIN DIALOG command, use the SEND command to send the message on that conversation using the conversation handle that is returned from the BEGIN DIALOG command.

DECLARE/font> @dialog_handle UNIQUEIDENTIFIER,
@XMLData XML ;

SET @XMLData = (SELECT * FROM sys.tables FOR XML AUTO)

BEGIN DIALOG @dialog_handle
FROM SERVICE [tcp://SearchSQLServer/SampleService]
TO SERVICE 'tcp://SearchSQLServer/SampleService'
ON CONTRACT [tcp://SearchSQLServer/SampleContract];

SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [tcp://SearchSQLServer/SampleMessageType]
(@XMLData);

In order to view and process the message, the RECEIVE command is used. The RECEIVE command is much like SELECT, in which the data is returned. When using the RECEIVE command, however, the message is received only once. After the message has been received and if it is the last message in the conversation – most people only put a single message in a conversation – use the END CONVERSATION command to close it. The RECEIVE command can be used with the WAITFOR command and a WHILE loop to process all messages in the queue in a single run of the procedure.

DECLARE @dialog_handle UNIQUEIDENTIFIER,
@XMLData XML ;

RECEIVE TOP (1) @dialog_handle = conversation_handle, @XMLData =
CAST(message_body AS XML)
FROM [tcp://SearchSQLServer/SampleQueue]

END CONVERSATION @dialog_handle

SELECT @XMLData

When you end the conversation, a message is actually sent from the receiving queue to the sending queue which informs the sending queue, service and SQL Server that the conversation is closed. This end conversation message must then be processed by the sending queue in order to remove the data from the queue. It is recommended that a basic procedure be used on the sending queue to automatically clear out these messages.

CREATE PROCEDURE usp_ProcessAck
AS
DECLARE @xml AS XML
DECLARE @dialog_handle as uniqueidentifier
WHILE 1=1
SET @dialog_handle = NULL

WAITFOR ( RECEIVE TOP (1) @dialog_handle = conversation_handle, @xml =
cast(message_body as xml)
FROM [tcp://SearchSQLServer/SampleQueue]), TIMEOUT 1000

IF @dialog_handle IS NULL
break

END CONVERSATION @dialog_handle
END
GO

After you create this procedure, use the ALTER QUEUE command to set up the queue to run the procedure automatically when messages arrive by using the ACTIVATION commands of the QUEUE. You can set the number of parallel executions of the procedure by setting the MAX_QUEUE_READERS greater than 1. In systems that are very high load, or when the processing takes some length of time, additional queue readers can speed up the processing of data by processing the data from the queue in parallel.

ALTER QUEUE [tcp://SearchSQLServer/SampleQueue]
WITH ACTIVATION (STATUS=ON,
PROCEDURE_NAME = dbo.usp_ProcessAck,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF)

SQL Server Service Broker may be complex to set up and there isn't much documentation about it on the Internet. However, once Service Broker is configured and working correctly, it can provide a rock solid interdatabase or intradatabase communications platform for sending data quickly and en masse from one system to another.

[TABLE]


Rate this Tip
To rate tips, you must be a member of SearchSQLServer.com.
Register now to start rating these tips. Log in if you are already a member.




DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



SQL Server Development - .NET, C#, T-SQL, Visual Basic
HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts