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

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

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles