Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 3, 2010

SQL Server Service Broker Components - Message Types, Contracts and Queue

By Arshad Ali

Arshad Ali discusses the Initiator, Target, Message Types, Contract and Queue--all components of SQL Server Service Broker (SSBS).

SQL Server Service Broker - An Introduction introduced SQL Server Service Broker (SSBS), used to write message based asynchronous applications. I briefly discussed SSBS and its capabilities. Let me introduce the different components of SSBS:

Initiator

In SSBS terminology, the sender of the message is called the Initiator.

Target

Again in SSBS terminology, the receiver or intended audience of the message is called the Target.

Message Type

A unit of information, which we transfer from Initiator to Target is called a message. This message can be as large as 2 GB. By using Message Type, we can make SSBS validate messages to conform to a defined standard. For example, you can use a message type to specify that an Initiator can send a valid XML message only; if the message is not a valid XML then SSBS will discard the message and will return the error message to the service. The syntax for MESSAGE TYPE command is as follows:

CREATE MESSAGE TYPE <message_type_name>
    [ AUTHORIZATION <owner_name> ]
    [ VALIDATION = {  NONE
                    | EMPTY
                    | WELL_FORMED_XML
                    | VALID_XML WITH SCHEMA COLLECTION
                                       <schema_collection_name>
                   } ]

<message_type_name> is the name of the message type and can be up to 128 characters. <owner_name> owns the message type and if not specified will be owned by the current user. VALIDATION specifies whether message will be validated or not. NONE means message will not be validated. EMPTY means a message will not contain a message body and is normally used to send an acknowledgement. WELL_FORMED_XML means the message body is XML; SSBS validates whether a message is valid XML or not by loading it into an XML parser. Obviously, it will have the associated overhead with this validation. VALID_XML WITH SCHEMA COLLECTION means the message body is a valid XML and it also conforms to a XML schema of the collection specified.

Example – The statement below will create a message type, which will allow only valid XML as part of its body.

CREATE MESSAGE TYPE
             [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
GO

Are you wondering why the name of the message type is in URI format? This is the recommended method of naming SSBS objects because these objects are often referenced across databases or across servers; to make them unique we use URI format naming convention for SSBS objects. Note, it’s a recommendation not a rule.

Contracts

A contract is nothing but an agreement between Initiator and Target to send specified message types only on the given service. The syntax to create a contract is given below:

CREATE CONTRACT <contract_name>
   [ AUTHORIZATION <owner_name> ]
      (  {   { <message_type_name> | [ DEFAULT ] }
          SENT BY { INITIATOR | TARGET | ANY }
       } [ ,...n] )

<contract_name> is the name of the contract and can be up to 128 characters. <owner_name> owns the contract and if not specified will be owned by the current user. <message_type_name> is the name of the message type with which this contract will be bound. A contract can have one or more message types defined in it and each of them can be further used by either Initiator (SENT BY INITIATOR) only or Target (SENT BY TARGET) only or by both (SENT BY ANY). Because a contract defines message types, which can be used in a communication and a communication can be started by initiator only, there must be at least one SENT BY INITIATOR or SENT BY ANY in a contract definition.

By default, every database has a default message type, which has its validation method set to NONE. If you want to use this default message type, you can simply use DEFAULT in place of <message_type_name>. Likewise there is also a default contract type, which you can use.

Example – The statement below will create a contract, which will allow initiator to send message of type [//SSBSLearning/ProductStockStatusCheckRequest] and target to send message of type [//SSBSLearning/ProductStockStatusCheckResponse].

CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO

Queues

A queue is FIFO (First-In-First-Out) data structure implemented as an internal table and used to store incoming messages. The messages reside in the queue until you process them. You can view the content of the queue by using the SELECT statement but since it's a hidden internal table you cannot directly execute DML statements (INSERT, UPDATE or DELETE) against it. Instead, to add a message to the queue you use a SEND command whereas to pick a message from the queue you use the RECEIVE command, which I will discuss later. As I said, a queue is implemented by SSBS as an internal physical table and hence messages are not lost even after system restart. The general syntax to create a contract is given below:

CREATE QUEUE <Queue_name>
   [ WITH
     [ STATUS = { ON | OFF }  [ , ] ]
     [ RETENTION = { ON | OFF } [ , ] ]
     [ ACTIVATION (
         [ STATUS = { ON | OFF } , ]
           PROCEDURE_NAME = <Activation_stored_procedure> ,
           MAX_QUEUE_READERS = #No_Of_max_readers_Of_Queue ,
           EXECUTE AS { SELF | 'user_name' | OWNER }
            ) ]
   ]
     [ ON { <filegroup> | [ DEFAULT ] } ]

<Queue_name> is the name of the queue, which you want to create. In addition, you can prefix the database name and schema name in which the queue will be created. By default, the STATUS is ON, which means the queue will be created in an enabled state. You can create a queue in disabled status by specifying STATUS = OFF and later enable it with the ALTER QUEUE command. Please note, you cannot add/remove any message to/from the queue if it is in disabled state.

By default RETENTION is OFF, which means messages will be removed from the queue when the message is processed for the first time. If you change it to ON, all of the messages will be retained in the queue as long as the conversation (to which these messages belong) is active. Use message retention only when required as it will reduce the performance since it increases the amount of work SSBS needs to do while sending a message.

ACTIVATION is a very interesting topic and requires great deal of discussion, so I will cover it in detail in my next article on SSBS Activation. Meanwhile, I will briefly talk about it. Activation removes the overhead of the processing module to keep on polling the queue for incoming messages. It allows you to create a stored procedure and make it an activator, which is activated when a message arrives in the queue to process. SSBS creates an instance of a stored procedure automatically to process the message the moment it arrives in the queue. You can even specify the number of simultaneous instances of stored procedures that can be created by SSBS to process messages of different conversation groups and in this way, you achieve parallel processing. Please note, messages of a particular conversation group are processed by only one instance of stored procedure, it means parallelism is available across conversation groups and not messages within a conversation group.

A queue is a physical structure and requires storage space for storing messages. The ON clause allows you to specify the file group on which the queue will be created. If you omit it or specify DEFAULT then the queue will be created in the default file group of the database.

Example – The statement below will create a queue called SSBSLearningInitiatorQueue in the default file group of the database.

CREATE QUEUE SSBSLearningInitiatorQueue;

Conclusion

This article has discussed a few of the components of SSBS, ie. Initiator, Target, Message Types, Contract and Queue. In the next article we will discuss Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other.

Additional Resources

Getting Started with SQL Server Service Broker
MSDN SQL Server Service Broker
MSDN Creating Service Broker Objects
MSDN Creating Service Broker Message Types
MSDN CREATE QUEUE (Transact-SQL)
MSDN Contracts
MSDN: Microsoft Message Queuing

» See All Articles by Columnist Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM