Service Broker

 

By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server

This posting, I would like to unveil basic knowledge of service broker. Service broker has been introduced since SQL Server 2005. And I observe that it is still not widely used by database application. I think it is because no user interface to setup and monitor service broker application. In fact, service broker, a database level messaging system, offers reliable database scalability and high availability because of transactional and asynchronous nature. Service broker application is about communication between services matter, those services could be in one database, across database in one SQL Server instance, across database in across SQL Server instances and across database in across SQL Server instance in different physical database machine. Communication in service broker is based on TCP/IP binary mode. It means that case sensitivity in service broker objects does matter so I recommend you create service broker objects (described further below) with url schema type enclosed by [] sign.

By default service broker is not enabled in your database, to check whether your database is broker enabled you could issue this statement :

SELECT IS_BROKER_ENABLED FROM SYS.DATABASES WHERE NAME = ‘Adventureworks2008’

I use sample database in SQL Server 2008, AdventureWorks2008.

If it is enable state then it returns 1, otherwise 0. If you have 0 , you need to enable it by issuing TSQL command :

ALTER DATABASE Adventureworks2008
SET ENABLE_BROKER;

You need to remember that when a database is restored from backup, by default service broker is not enabled. In this case make sure you check broker ability state by doing steps shown previously.

In this posting, I would describe each components of service broker, and I show you example of creating those components with T-SQL command.

Message type

Smallest unit of service broker is message type. What contains in message type is information that you want to deliver to destination. It is optional if you want to include your data in message type. Here is declaration to create message type :

CREATE MESSAGE TYPE [//test/ItemAdded]
this is most simple one without any data in the message type.

Another variation of message type creation is (remember that each time you try to create variation of message type with same name, don’t forget to drop them first by issuing :

DROP MESSAGE TYPE [//test/ItemAdded];

)

CREATE MESSAGE TYPE [//test/ItemAdded]
VALIDATION = NONE;

VALIDATION = NONE here means no validation applied to the message type when a data is included in it.

Another variation of message type creation is

CREATE MESSAGE TYPE [//test/ItemAdded]
VALIDATION = EMPTY;

VALIDATION = EMPTY here means you are not expected to put any data in it. If you put data in it, service broker will raise error.

Another variation of message creation is

CREATE MESSATE TYPE [//test/ItemAdded]
VALIDATION = WELL_FORMED_XML

VALIDATION = WELL_FORMED_XML here means you are expected to input standard and well formed common XML.

Another variation of message creation is

CREATE MESSATE TYPE [//test/ItemAdded]
VALIDATION = VALID_XML WITH XML SCHEMA COLLECTION <xml schema collection name>

VALIDATION = VALID_XML WITH XML SCHEMA COLLECTION here means that input data should be conformed with XSD (XML schema definition) which is defined in xml schema collection name database object.

You can query list of message type in your database by referencing dynamic management view (DMV) named SYS.SERVICE_MESSAGE_TYPES.

Message contract

This service broker object will bind one or more message types to message sender (called INITIATOR) and message receiver (called TARGET). I show you syntax how to create message contract.

CREATE CONTRACT [//test/ItemAddedContract]
([//test/ItemAdded] SENT BY ANY);

[//test/ItemAddedContract] is contract name and [//test/ItemAdded] is message type that you create earlier. Both message type could be used by INITIATOR and TARGET.

You can drop message contract by issuing :

DROP CONTRACT [//test/ItemAddedContract];

You can query DMV before deletion by checking through this query :

SELECT * FROM SYS.SERVICE_CONTRACTS;

If you want INITIATOR and TARGET issue different message type you could create the following contract here :

CREATE CONTRACT [//test/ItemAddedContract]
([//test/AddItem] SENT BY INITIATOR
,[//test/ItemAdded] SENT BY TARGET);

Before you execute above statement, make sure those message types exist first ([//test/AddItem] and [//test/ItemAdded]).

Queue

Like MSMQ, service broker implements queue. Queue is a place where a message (encapsulated by message type, enforced by message type) is put into it before the message is taken out by destination. Physically queue is database table created by service broker that has already has its own schema predefined. It is recommended you put queue in other filegroup. Here is the queue creation syntax :

CREATE QUEUE dbo.ItemQueue
WITH STATUS = OFF, ACTIVATION
(
PROCEDURE_NAME = dbo.myprocedure,
MAX_QUEUE_READERS = 10,
EXECUTE AS SELF
);

If you don’t specify STATUS = OFF, by default it will be ON immediately after creation. MAX_QUEUE_READERS here is maximum number of activation stored procedure instances when queue starts at the same time.

To set the queue status from OFF to ON issue this TSQL command :

ALTER QUEUE dbo.ItemQueue
WITH STATUS = ON;

To get information about queues in your database, issue statement to SYS.SERVICE_QUEUES dynamic management view.

Service

Service is actually queue. So what is difference between service and queue? Service is queue in logical term, or I can say service is logical queue whereas queue is physical one. With service, it is possible to decouple queue to its physical database instance (activation stored procedure and physical filegroup). Service also binds queue with service contract. More interesting here is that a service can only have one queue but one or more service contract.

Here is syntax to create service :

CREATE SERVICE [//test/AddItemService]
ON QUEUE dbo.ItemQueue
([//test/ItemAddedContract]);

Establish dialog between services

Now, basic service broker infrastructure have been created, it is now ready to be used in service broker conversation. To begin conversation issue this statement :

BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [//test/AddItemService]
TO SERVICE ‘//test/AddItemService’
ON CONTRACT [//test/ItemAddedContract]
WITH ENCRYPTION = OFF, LIFETIME = 3600;

A conversation session has been established through @conversation_handle, an uniqueidentifier variable type. LIFETIME here denotes maximum time an conversation is held (in seconds).

A DMV for knowing status of conversation state is issuing :

SELECT STATE, STATE_DESC FROM SYS.CONVERSATION_ENDPOINTS;

To close conversation, issue the following TSQL statement :

END CONVERSATION @conversation_handle;

Monitor service broker

The most possible service broker application is in trouble when a message has not been sent out successfully to destination. It might be because of destination is unreachable. To monitor message status of running service broker, you can issue this statement :

SELECT * FROM SYS.TRANSMISSION_QUEUE;

You can check TRANSMISSION_STATUS column in SYS.TRANSMISSION_QUEUE;

Once you master basic of service broker application objects, you have foundation to build next stage of reliable service broker application.