Configure SQL Server Service Broker for sending stored procedure data

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     Del.icio.us

RELATED CONTENT SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development 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 Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 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 Microsoft SQL Server 2005 Research Database Development Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Working with IntelliSense in SQL Server 2008 Management Studio Top tips and tricks for SQL Server 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

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/.

1. The first object you want to set up in Service Broker is Message Type, which tells SQL Server about basic information within the message. Service Broker messages can be data of any type: text, binary, xml, numeric, etc. It's recommended that XML is used, because it allows for the sending of all the other data types within it. We use the CREATE MESSAGE TYPE command to create a message type. There are four validation options available. If you want to enforce valid XML formatting, select the WELL_FORMED_XML or the VALID_XML WITH SCHEMA COLLECTION (to use an XSD to enforce your XML schema).
2. The next object we'll need to set up is the contract. The contract tells SQL Server which message types relate to each other. Personally, I like to keep things simple and use a single message type for all communication in a specific process. However, the Microsoft SQL Server Books OnLine examples show using one message type for the sending system and a second for the receiving system.
3. Now we're on to the actual queue in Service Broker. The queue is kind of like a table. This is where the messages are stored while they wait to be processed. Unlike a table, you cannot define the schema of the queue. For this reason, it's recommended that XML be used to send the data because then you can define the schema within the XML.

   The next Service Broker object to configure is the service. The service is used by Service Broker to deliver messages to the correct queue within the database and to bind the contract to the queue message.

4. The last object is the route and it's optional. The route is only required when you are sending messages from one database server to another. The route is tied to a local copy of the server that is receiving the message. If the server receiving the messages is mirrored to another server using database mirroring, then you'll want to include the MIRROR_ADDRESS parameter. If you want the route to delete itself automatically, you can set the LIFETIME flag.

   The BROKER_INSTANCE parameter is the value of the service_broker_guid column from the sys.databases view of the database located on the remote server..

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.

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.

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.

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.

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.

ABOUT THE AUTHOR:   

[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog: SQL Server with Mr. Denny

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.