In this article, we are going to discuss the Service Broker, another new feature in SQL Server 2005. In simple terms, Service Broker is a distributed messaging framework, or MSMQ (Microsoft Message Queue), in SQL Server 2005. Using this new feature, you will be able to add reliable, scalable, asynchronous functionality to your database applications.
This article will focus on the benefits of this new feature and basic configuration for applications.
Benefits of Message-Based Application
Before detailing the features and functionalities of Service Broker, let us examine the benefits of message-based applications.
Message based applications are very much needed when several services are involved. For example, in an online trading application, before confirming the order of a client, you need to perform several processes like checking stock availability, shipping availability, delivery date, etc. These processes will need time. If you are validating all the processes before confirming the order, your client may have to wait for some time at his browser. Instead, you can take in ordering details from the client and process all the necessary processes by putting the information in messaging. After getting all the information from all the services, you can compile them into an e-mail and send it to the client. In this way, your client does not have to wait for a long time at your Web site, which will make it more user-friendly.
Message based applications can process several services at once. For example, shipping service and stock availability service can run parallel. By doing so, it will minimize the time required to process the entire order.
Microsoft Messaging Queue (MSMQ) is a framework that supports this kind of messaging. You can write an application in VB, C#, or C++ to implement MSMQ applications. However, a basic issue with traditional messaging applications is when one process fails you have to rollback all the other processes manually, which has to be handled carefully. Not an easy task.
SQL Server 2005 has come up with this new feature to get rid of traditional messaging issues.
Install
Service Broker needs minimum configuration, and its features are available in all SQL Server editions. In SQL Server Express edition, you get the client features of Service Broker.
There are a few concepts or objects that we have to understand in Service Broker: Queues, Messages, Dialogs, Contracts, Services, and Conversation Groups.
Queues
Well, queues are nothing but queues. Like traditional queues, this is a first-in-first-out (FIFO) storage system. Queues provide a link between the message sender and the message receiver. The message sender will send the message to the queue while the message receiver will read the message. Because of the queue technology, the sender does not have to wait for the receiver to finish processing the message before sending the next one. The sender can send any number of messages and the receiver can process them at a different rate or even a different time thus enabling equal resource distribution.
In Service Broker, queues use hidden tables. Because messages are saved in tables, they will not be removed even after the server restarts. Also, you can backup your messages by simply making a backup of the database, as those queues are part of the database. Another important feature is that you cannot execute INSERT, DELETE, or UPDATE statements against these queues. SELECT and RECEVE (which I will discuss later in this article) are the only operations that you can perform on these queues.
Messages
A message is placed in queues. The data type of a message is varbinary(max), which is a new data type in SQL Server 2005. This new data type gives you the option of saving messages of up to 2 GB capacity. You will not able to view the message by simply invoking a SELECT statement. Messages are always received in the order in which they are sent and are guaranteed to arrive, as they cannot be lost during transmission or to any other cause. This is a major advantage over MSMQ as many developers/users complain about the sequence of message sending and receiving in MSMQ. If the message is not sent for some reason, the service will try to resend it again later, which will reduce your need to monitor the messages.
When defining a message, you can define what type of message it should contain. This provides added security to Service Broker messages. The validation types are EMPTY, WELL_FORMED_XML and VALID_XML_WITH_SCHEMA_COLLECTION. With EMPTY, you are not imposing any validation on the message. WELL_FORMED_XML means that message should have valid XML format. VALID_XML_WITH_SCHEMA_COLLECTION is the same as the class XmlSchemaCollection. XmlSchemaCollection is a cache or library where XML-Data Reduced (XDR) and XML Schema Definition (XSD) language schemas can be stored and validated. XmlSchemaCollection improves performance by caching schemas in memory instead of accessing them from a file or URL.
Dialogs
You are familiar with Queues and Messages from other messaging applications. A Dialog is a fairly new concept in SQL Server 2005 Service Broker. Some experts are calling this a Conversation as well. Whatever the name, it forms the core of Service Broker. A Dialog is the mechanism by which you are able to put your messages in queues. In a Dialog, messages are ordered and delivered in the order that they were sent. Under normal circumstances, a dialog is created for a particular task and deleted when it is completed. A Dialog occurs between two end points. End points are used to communicate with Service Broker on different SQL Server instances. End points allow Service Broker to communicate by using HTTP, TCP, or SOAP protocols. These end points are not configured by default. So you need to configure them if you plan to communicate over different SQL Server instances. Service Broker often uses port 4022 for broker-to-broker communication. You can change this if you want to, when creating end points.
Contracts
Contracts define who can send what types of messages from the initiator or receiver. You can specify multiple message types in a contract and specify who (either initiator or receiver) can send it.
Services
Services read messages from a queue and process them. A Service can be a SQL Server stored procedure or a different program that is linked with a Dialog.