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 Amazon.com. This time, I’d like to use an analogy that’s 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 doesn’t 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.
CREATE MESSAGE TYPE [//SBDemo/Taxes/TaxFormMessage] VALIDATION = WELL_FORMED_XML; CREATE MESSAGE TYPE [//SBDemo/Taxes/TreasuryCheckMessage] VALIDATION = WELL_FORMED_XML; CREATE MESSAGE TYPE [//SBDemo/Taxes/AuditNotificationMessage] VALIDATION = WELL_FORMED_XML; GO
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, we’re 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. Let’s start with queues.
A Service Broker queue is, at its essence, a hidden table in SQL Server that stores messages until they’re processed. Each message in the queue is a row in that hidden table. But the cool thing about this hidden table is that it’s not totally hidden. You can SELECT from it, but you can’t 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.
CREATE QUEUE TaxpayerQueue; CREATE QUEUE IRSQueue;
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 don’t send it to a specific inbox on a desk, right? We send it to the Internal Revenue Service. And sending our return information to the Internal Revenue Service implies 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 don’t 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 we’ve created our message types, contract, queues, and services, we’re ready to start sending and receiving messages. More on that next time!
Thanks for the excellent article! i’m waiting for the next part!
Pingback: SQL Server Service Broker - Error Handling | ColleenMorrow.com
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