SQL Server database administration in sunny Cleveland, OH

SQL Server database administration in sunny Cleveland, OH

SQL Server Service Broker Service Architecture

SQL Server Service Broker Service Architecture

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queuing technology built into the SQL Server Database Engine.

In this installment, we introduce the basic Service Broker service architecture components.

Before I jump into the technical details of the Service Broker architecture, I think it helps to have a real-world analogy of what Service Broker is and does.  In the last installment, I used the example of ordering something from .  This time, Id like to use an analogy thats somewhat timely: taxes.

Each year, we fill out that 1040 or 1040EZ form and we send it to the Internal Revenue Service.  Maybe we eFile, maybe we mail it in, it doesnt matter.  That form is received by the IRS and goes into a queue, awaiting review.  At some point, days, maybe weeks later, our tax return is processed.  If all goes well, our return is approved and the IRS cuts us a check.  That is a Service Broker application.

The first Service Broker components we define in a new application are the message types.  The message type defines name and format of messages that will be exchanged between services.  When we create a message type, we have the option of also applying validation, basically saying the message must adhere to a specific format.  That validation format may be well-formed XML, XML of a specific schema, an empty message, or we can say no validation at all, in which case the message content could be anything.  In our tax example we had 2 message types: the tax return form we submit and the check we get back.  Each of these has a well-defined format with specific fields it must contain.


Once the message types have been defined, the next component we need to create is the contract.  A Service Broker contract specifies which message types allowed in a conversation and which participant can send which message type.  In our tax example, the taxpayer sends the 1040 message type and the IRS sends the treasury check.  The IRS, however, would never send the taxpayer a completed 1040 form and a taxpayer would never send a treasury check.  Why is this important?  By defining what message types can be sent by each participant in a Service Broker app, were helping the receiving participant identify when an unauthorized message is received, thereby making our Service Broker app more secure.

Note that a contract can specify more than one message type for any participant.  For example, the IRS can also send an audit notice.  And the taxpayer can also send other forms or, unfortunately, a personal check.  The same holds true for a Service Broker contract.

CREATE CONTRACT [//SBDemo/Taxes/TaxContract] ( [//SBDemo/Taxes/TaxFormMessage] SENT BY INITIATOR, [//SBDemo/Taxes/TreasuryCheckMessage] SENT BY TARGET, [//SBDemo/Taxes/AuditNotificationMessage] SENT BY TARGET ); GO

So, message types and contracts are pretty straightforward, right?  The next 2 components sometimes cause a little confusion.  Lets start with queues.

A Service Broker queue is, at its essence, a hidden table in SQL Server that stores messages until theyre processed.  Each message in the queue is a row in that hidden table.  But the cool thing about this hidden table is that its not totally hidden.  You can SELECT from it, but you cant perform any DML operations on it.  Like any other real table in the database, the queue is included in transactions, logging, database backups, database mirroring, etc., just like a real table.  Each participant in a Service Broker application needs to have a queue to store received messages.  In our tax analogy, I like to picture the queue as a cheap plastic inbox sitting on some sad desk in the IRS offices (complete with dreary florescent lighting, ala Joe Versus the Volcano).  Our 1040 form will stay in that inbox until Tom Hanks processes it.

A Service Broker service is an addressable endpoint for conversations that bundles up a specific contract and queue.  Huh?  When we send our tax return form in, we dont send it to a specific inbox on a desk, right?  We send it to the Internal RevenueService.  And sending our return information to the Internal RevenueServiceimplies the adherence to the contract that was laid out earlier (we send in a specific form, they send us a check, etc.).  And when we send that form to the IRS, the form automatically gets placed in that inbox (queue).  Similarly, in a Service Broker application, we dont send a message to a queue, we send it to a service.  By sending a message to a specific service, we agree to use the message types defined in the contract on that service and SQL Server automatically places our message into the queue associated with that service.

CREATE SERVICE [//SBDemo/Taxes/TaxpayerService] ON QUEUE TaxpayerQueue ([//SBDemo/Taxes/TaxContract]); GO CREATE SERVICE [//SBDemo/Taxes/IRSService] ON QUEUE IRSQueue ([//SBDemo/Taxes/TaxContract]); GO

That is a very basic introduction into the Service Broker architecture.  Once weve created our message types, contract, queues, and services, were ready to start sending and receiving messages.  More on that next time!

Your email address will not be published. Required fields are marked*

You may use theseHTMLtags and attributes:a href= title= abbr title= acronym title= b blockquote cite= cite code del datetime= em i q cite= strike strong

Thanks for the excellent article! im waiting for the next part!

Pingback:SQL Server Service Broker – Error Handling

Pingback:SQL Server Service Broker Internal Activation – SQL Server – SQL Server – Toad World

Pingback:SQL Server Service Broker Networking – SQL Server Blog – SQL Server – Toad World

Pingback:SQL Server Service Broker – Security m

I dont know whether its just me or if perhaps everybody else experiencing issues with your blog.

It appears as if some of the written text on your

posts are running off the screen. Can someone else please provide feedback and let me know if this is happening

to them too? This may be a issue with my browser because Ive had this happen before.

SQL Server Service Broker Introduction

SQL Server Service Broker Conversation Architecture

SQL Server Service Broker Internal Activation

SQL Server Service Broker Error Handling

© Designed byThemes & Co