Service Broker

Back To Basics: Service Broker Message Types

The message type is the first of the service broker specific objects.  The message type defines the name of the message, and performs any validation that the service broker needs to do on the contents of the message prior to sending the message.  If the message is being sent to another database (on the same server or a different server) then the message type must exist on both databases.

Validation can be one of four options.

1. None (My personal favorate especially for sample code)
2. Empty
3. Well_Formed_XML
4. Valid_XML With Schema Collection

1 and 2 are pretty straight forward.  None = No validation is done.  Empty = The message must be empty.  Well_Formed_XML requires that the XML be a valid XML document.  The fourth option requires that not only the XML be valid, but that it fits within a pre-existing XML schema collection (an XSD which has been loaded into the SQL Server).

The syntax to create a Message Type is pretty basic.

CREATE MESSAGE TYPE [MessageTypeName]
VALIDATION = NONE

That’s it.  If you need to change the schema use the AUTHORIZATION clause to set the owner of the object.

When using the validation of NONE it is up to the receiving code or application to verify that the data within the message exists and is valid.

Denny

Back To Basics: The SQL Server Service Broker

The SQL Server Service Broker is a fantastic new addition to SQL Server.  For those who have used Microsoft Message Queue (MSMQ) the service broker will be easy to understand as it’s the same basic concept.  Messages are sent from one place to another, within a database, from database to database, or from server to server.

 While the configuration can be a daunting task, once the service broker is setup it is a very solid system which can handle a large message load.

The Service Broker give you guaranteed delivery order, with single processing of messages always in the order received.  Messages can be processed on demand (you write software which queries the service broker on a schedule) or automatically via the queue activation.

Check back for information about all the various service broker object an how to configure each of them.

Denny

New Article: Configure SQL Server Service Broker for sending stored procedure data

I’ve just published a new tip on SearchSQLServer.com called Configure SQL Server Service Broker for sending stored procedure data.  In it I’m going over SQL Service Broker and how to configure all the objects.

As a followup to this I’ll be going over all these objects as part of the Back To Basics series over the next several weeks.

 Denny

I’ll be speaking at the NJ SQL Server User Group

The NJ SQL Server User Group has been kind enough to invite me to come speak to them about SQL Server Service Broker.

 I’ll be speaking up there on August, 19, 2008.

I’ll be giving a similar presentation to the one that I gave at the SoCal Code Camp in February 2008.  I’ll be revising the slide deck a little bit based on how that session went.  If you are in the area I’d be thrilled to have you come by, listen to me blather on about Service Broker for a bit, and chat.  The Address, directions and times are posted on the NJ SQL Server User Group web site.

See you in New Jersey.

Denny

I had a great time speaking at the San Diego SQL User Group

I’d like to thank the San Diego SQL Server User Group for having me come and speak to them tonight.  I had a great time presenting both my SQL Server Query Tuning and SQL Server Service Broker presentations.  You can grab the slide deck and sample code from those two links.

 I was happy to fill in on short notice for them when there scheduled speak cancelled on them.  Hopefully the members liked the presentations as much as I liked giving them.  Hopefully the San Diego SQL Server User Group will invite me back in the future.

Denny

Slide Decks and Sample Code for San Diego SQL Users Group Meeting

Below you will find links to download file files with the MS PowerPoint slide decks and sample files which I’ll be using at this weeks presentation at the San Diego SQL Users Group Meeting.

There is a lot of sample code in the Query Tuning file.  We’ll look through as much of it as we can depending on the amount of time that we have.

SQL Server Query Tuning

SQL Server Service Broker

I’ve included the Service Broker slide deck even though I’m not sure that we will get to it.  If there isn’t time to get to the slide deck during the meeting I’ll be happy to answer questions on it after the meeting (or posted here of course).

See you Thursday night.

Denny

What is Service Broker?

Service Broker is a transaction message queueing system build into Microsoft SQL Server.  It provides you with in order, guaranteed single read, message processing that is handled and managed with T/SQL code.  This makes it extremely easy to send and process messages either within a single database, or send those messages to a remote database on another server for processing.  Messages are sent as XML documents so a message payload can contain a single field of data or a multiple row record set as a single message.

For those familiar with Microsoft Message Queue you will find that Service Broker is very similar to MSMQ but is native to the Microsoft SQL Server.

Server Broker can route messages from database to database, or server to server.  Messages can be processed or routed to another server for processing there.  Queues can be setup to hold messages for processing by an application or job, or have the messages processed as soon as they arrive by an activation procedure which is simply a procedure which is fired as messages arrive.  Activation procedures can be run as a single thread or several threads pulling from the same queue at once.

 Look for a future blog posting on configuring and using the service broker to send and process messages.

Better Error Reporting for Service Broker

I’ve submitted a request to Microsoft (via the Connect site) to have them improve the error reporting which the Service Broker does to the error log.  If everyone could vote for that submission (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=304785) it would go a long way towards getting it included in a future build of SQL Server.