

Introduction to Service Broker in SQL Server

Discussion in 'SQL Server' started by MinalS, Dec 18, 2014.

  1. MinalS

    MinalS New Member

    Jul 8, 2014
    Likes Received:
    Trophy Points:
    The Service Broker helps users for creating services that can be used for sending and receiving messages. Service is used as a database object. It helps user by creating an endpoint for conversation.

    The following diagram shows the asynchronous messages to be communicated in Service Broker.


    The service keeps the message in a queue. The transaction of the client application is committed. If there are multiple requests, the messages are kept in a queue. The orders are processed as they are received.

    The unprocessed messages by the database server, the messages are returned to the initial position in the queue. Once the server is available, the messages are processed. Once the operation is successful, the client receives the response.

    Large database distributed applications can process the data in multiple database servers situated in remote locations. These applications can be used for message queuing and reliable delivery.

    The list mentioned below represents the usage of the service broker in business processing.
    1. Reliable data collection: The data is collected from several servers. The service broker can be used for collecting the data.
    2. Asynchronous triggers: The triggers are executed synchronously for a transaction. User can create a trigger invoking an asynchronous process through the service broker. The messages are queued before execution. A new transaction is associated with it. The original transaction is committed as per its execution.
    3. Reliable query processing: Applications must process queries without disturbances from the system failures, power changes, and other problems. The messages can be sent to the service broker services.
    4. Large – scale batch processing: The applications that contain large volume of data and perform the large scale batch processing. It can also use parallel processing and queuing offered by the service broker.
    5. Data consolidation in client application: The client application displays data from several servers. The application sends request to the services simultaneously through service broker. When services respond to the requests, the application collects and displays value. Hence, parallel processing offered service broker are useful in reducing the response time.
    6. Distributed server – side processing for client application: It can help applications that access the multiple databases. It can provide message queuing. The application accepts values even if one of the databases is unavailable.

    Service Broker Architecture

    The Service Broker is dependent on the Service Broker architecture. It contains of following database objects.
    1. 1) Message: The data is interchanged between services.
    2. 2) Message Type: It defines the text of the messages between the participants in the conversation.
    3. 3) Service: It defines an addressable endpoint for conversation. The messages are sent from one service to another. The initiator and processing services are two types of services. The initiator services are used for initiating the conversation. The processing service receives the messages.
    4. 4) Contract: It is a deal in two services for message exchange.
    5. 5) Queue: It can be used as a medium for storing messages. Every service contains a queue. The message is kept in a queue. A queue is represented as a table and the messages are placed in a row. Every row contains a message and the information as type of the message, initiator and target service.
    6. 6) Service Program: It is a program that provides logic for the service. The service broker initiates the service program and forwards the message to the program.

    Activation of Service Broker

    Once a service broker is initiated, the mechanism to implement the message in the queue is created. The messages in the queue are polled continuously to check is any messages have been inserted. But polling results into wastage of many resources.

    To overcome this issue, the service broker activation process is used. It ensures that the resources are available to process messages as they appear in the queue. A queue monitor is created for each queue for which the QUEUE_ACTIVATION event is registered.

    Every queue monitor on an SQL Server instance has the following conditions:
    1. The number of activated stored procedures running in the queue
    2. A RECEIVE statement returns an empty result set
    3. The queue contains messages that are ready for receiving
    The queue monitor checks for the activation when one of the following events occurs:
    1. A RECEIVE statement for the queue is executed
    2. A new message is present in the queue
    3. The stored procedure executed by the queue monitor exit
    4. ALTER statement is executed
    5. A transaction with the RECEIVE statement is rolled back
    The following two activation types are supported by Service Broker.
    1. Internal Activation - In the internal activation, user needs to create the service program for processing the messages in the queue. The monitor automatically executes the stored procedure for processing the messages. The procedures can be either T-SQL or CLR Code.
    2. External Activation - The event based activation is also known as external activation. The server provides a activation event for notifying the external applications when the queue receives the messages.

    Implementing the Service Broker

    Whenever the user wants to implement the service broker, the objects of the service broker are created. The conversation is initiated. Once it is initiated, the objects can communicate with each other through messages.

    Creating Messages

    The messages are used for communication between the services. A name is necessary for the message for entering the conversation. It contains a unique identifier and a sequence number for message queuing.

    The CREATE MESSAGE TYPE statement is used for creating a new message. The syntax for creating message is as shown below:

    	CREATE MESSAGE TYPE message_type_name
    	[ VALIDATION = { NONE | EMPTY | WELL_FORMED_XML | VALID_XML WITH SCHEMA  COLLECTION schema_collection_name } ] [ ; ]

    message_type is the name of the message

    VALIDATION is used to specify how the message must be validated.

    NONE: It is used to specify that no validation is performed.

    EMPTY: It is used to specify that message body is NULL

    WELL_FORMED_XML: It specifies that the message must be well formed XML.

    VALID_XML WITH SCHEMA COLLECTION: It specifies that the message present in the snippet must match with the schema collection.

    The following code shows the creation of a message type.

    Creating Queues

    A queue is the unit that stored the messages. It can be a primary storage for the messages. It can be considered as the pipeline for the messages.

    The CREATE QUEUE statement is used for the queue creation. The syntax for CREATE QUEUE is as shown below:

    CREATE QUEUE [ database_name . [ schema_name. ] ] queue_name 
    [ WITH [ STATUS = { ON | OFF } [ , ] ] [ RETENTION = { ON | OFF } [ , ] ] 
    { ACTIVATION ( [ STATUS = { ON | OFF } , ] PROCEDURE_NAME = <procedure>, 
    MAX_QUEUE_READERS = max_readers, EXECUTE AS { SELF | ‘user_name’ | OWNER } ) ] ]
    [ ON { filegroup | [ DEFAULT ] } ]

    queue_name is the name of the queue

    STATUS ( Queue) is the state of the queue. It contains a value as ON or OFF. ON state states that the queue is ready for receiving messages. OFF specifies that queue is unavailable.

    RETENTION is used to state that the messages sent or received in a specific conversation will be retained or not.

    ACTIVATION is the information about the stored procedure for processing messages

    STATUS ( Activation ) is used to specify that the queue executes the stored procedure

    PROCEDURE_NAME = <procedure> is the name of the procedure for processing the messages

    EXECUTE AS is used to state the user account under which the procedure executes

    The following example demonstrates the queue.

    	PROCEDURE_NAME= sendProc,
    In the above code, the queue named as sndQueue is used for sending and receiving messages.

    The following code creates a queue that is not available for receiving messages.

    Creating Contracts

    A contract can be described as commitment between the two services for communicating with the services. The message type is specified in the contract. The CREATE CONTRACT is used for creating the contract.

    	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

    AUTHORIZATION owner_name is the name of the owner for the contract

    message_type_name is the name of the message type for the contract

    SENT BY specifies the type of the message sent to the endpoint

    Consider an example to demonstrate the create contract.

    CREATE CONTRACT sndContract
    	sendMessage SENT BY TARGET 
    ) ;
    In the above code, the sndContract is the contract created.

    Creating Services

    A service is used by the service broker for delivering messages in the proper queue. The CREATE SERVICE statement is used for creating the services.

    The syntax for CREATE SERVICE is as shown below:

    CREATE SERVICE service_name
    [ AUTHORIZATION owner_name ] ON
    [ schema_name. ] queue_name [ ( contract_name | [ DEFAULT ] [ ,…n ] )
    ] [ ; ]

    service_name is the name of the service to be created.

    AUTHORIZATION owner_name is the name of the owner of the service

    ON QUEUE [ schema_name. ] queue_name is the queue in which the messages are stored

    contract_name is the contract for which the service is targeted.

    Consider an example to demonstrate the services.

    CREATE SERVICE sndService
    [ dbo]. [ sndQueue] 
    In the above code, the service is created but no contract is declared.

    Initiating a Conversation

    The communication between the two services is established through the dialog. A dialog can be used for bi – directional communication between the services. The sequence of sent and received messages is maintained. It is necessary for the user to start the dialog conversation before communication.

    The BEGIN DIALOG statement is used for creating the DIALOG communication. The syntax for creating the DIALOG is as mentioned below:

    @dialog_handle FROM SERVICE
    ‘target_service_name’ ] [ ON CONTRACT contract_name ]

    @dialog_handle is the variable used for storing the system created handle

    FROM SERVICE initiator_service_name is the service that initiates the dialog

    TO SERVICE ‘target_service_name’ is the target service used for initiating the dialog

    ON CONTRACT contract_name is the name of the contract for the communication between the services.

    Consider the following example to demonstrate the dialog conversation.

    SERVICE [ sndService ] TO SERVICE ‘receiveService’ ON CONTRACT [ sndContract ];
    In the above code, the conversation can be started using the variable named as @dialog_handle. The sndService is the initiator and receiveService is the target of the dialog.

    Send and Receive Messages

    Once the objects are created and dialog is initiated, user can sent and receive messages.

    The SEND statement is used for sending messages. The syntax for SEND statement is as shown below:

    SEND ON CONVERSATION conversation_handle [ MESSAGE TYPE message_type_name ] [ ( message_body_expression ) ] [ ; ]

    ON CONVERSATION conversation_handle is the conversation to which the message belongs.

    MESSAGE_TYPE message_type_name is the message type of the sent message

    message_body_expression is the message that is to be sent.

    The following code snippet demonstrates the send message.

    @dialog_handle MESSAGE TYPE [ sndMessage ]
    ( ‘<StudID> 1 </StudID>’ )
    For receiving the message, the RECEIVE statement is used. The following steps are performed.
    1. A variable for storing the message details is declared
    2. The RECEIVE statement is called
    3. The message is processed
    4. The END CONVERSATION statement is called when the conversation ends.
    The syntax fort the RECEIVE statement is as shown below:

    [ WAITFOR ( ]
    	RECEIVE [ TOP ( n ) ]
    	<column_specifier> [ …n ]
    	FROM queue_name
    [ ) ] [ , TIMEOUT timeout ]

    WAITFOR is the RECEIVE statement waiting for the message to appear on the queue.

    TOP ( n ) is used to state the number of messages received from the queue

    column_specifier [ …n ] is the list of columns used for retrieving the data

    queue_name is the name of the queue

    TIMEOUT timeout is the amount of time to wait for the message

    The following example demonstrates the receive message in a queue.

    DECLARE @new_body nvarchar(200)
    		RECEIVE TOP(1)
    		@new_body = new_body
    		FROM RateQueue
    	      ) , TIMEOUT 2000
    In the above statement, the messages are received from the RateQueue. The variables are stored in the @new_body variable. The processing waits for two seconds if the message is not available in the queue.

    The END CONVERSATION statement is used for ending the conversation.

    The syntax for the END CONVERSATION statement is as shown below:

    END CONVERSATION conversation_handle

    conversation_handle is the handle for the end of conversation.

    The following statement demonstrates the end of conversation.

    END CONVERSATION @dialog_handle.
    The above statement ends the execution of the dialog conversation specified in the @dialog_handle

Share This Page