Skip to content

sql-server-service-broker.md

Latest commit

 

History

History
119 lines (83 loc) · 8.31 KB

sql-server-service-broker.md

File metadata and controls

119 lines (83 loc) · 8.31 KB
title SQL Server Service Broker | Microsoft Docs
ms.custom
ms.date 09/07/2018
ms.prod sql
ms.prod_service high-availability
ms.reviewer
ms.technology configuration
ms.topic conceptual
f1_keywords
SQL13.SWB.SSBMSGTYPEPROPERTIES.GENERAL.F1
SQL13.SWB.SSBCONTRACTPROPERTIES.GENERAL.F1
SQL13.SWB.SSBQUEUEPROPERTIES.GENERAL.F1
SQL13.SWB.SSBREMSVCBINDPROPERTIES.GENERAL.F1
SQL13.SWB.SSBROUTEPROPERTIES.GENERAL.F1
SQL13.SWB.SSBPRIORITYPROPERTIES.GENERAL.F1
SQL13.SWB.SSBSERVICEPROPERTIES.GENERAL.F1
helpviewer_keywords
Broker See Service Broker
SQL Server Service Broker
Service Broker
ms.assetid 8b8b3b57-fd46-44de-9a4e-e3a8e3999c1e
author MikeRayMSFT
ms.author mikeray
monikerRange =azuresqldb-mi-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017

Service Broker

[!INCLUDEappliesto-ss-asdbmi-xxxx-xxx-md]

[!INCLUDEssNoVersion] [!INCLUDEssSB] provide native support for messaging and queuing in the [!INCLUDEssDEnoversion] and Azure SQL Database Managed Instance. Developers can easily create sophisticated applications that use the [!INCLUDEssDE] components to communicate between disparate databases, and build distributed and reliable applications.

When to use Service Broker

Use Service Broker components to implement native in-database asynchronous message processing functionalities. Application developers who use [!INCLUDEssSB] can distribute data workloads across several databases without programming complex communication and messaging internals. Service Broker reduces development and test work because [!INCLUDEssSB] handles the communication paths in the context of a conversation. It also improves performance. For example, front-end databases supporting Web sites can record information and send process intensive tasks to queue in back-end databases. [!INCLUDEssSB] ensures that all tasks are managed in the context of transactions to assure reliability and technical consistency.

Overview

Service Broker is a message delivery framework that enables you to create native in-database service-oriented applications. Unlike classic query processing functionalities that constantly read data from the tables and process them during the query lifecycle, in service-oriented application you have database services that are exchanging the messages. Every service has a queue where the messages are placed until they are processed.

Service broker

The messages in the queues can be fetched using the Transact-SQL RECEIVE command or by the activation procedure that will be called whenever the message arrives in the queue.

Creating services

Database services are created by using the CREATE SERVICE Transact SQL statement. Service can be associated with the message queue create by using the CREATE QUEUE statement:

CREATE QUEUE dbo.ExpenseQueue;
GO
CREATE SERVICE ExpensesService
    ON QUEUE dbo.ExpenseQueue;

Sending messages

Messages are sent on the conversation between the services using the SEND Transact-SQL statement. A conversation is a communication channel that is established between the services using the BEGIN DIALOG Transact-SQL statement.

DECLARE @dialog_handle UNIQUEIDENTIFIER;

BEGIN DIALOG @dialog_handle  
FROM SERVICE ExpensesClient  
TO SERVICE 'ExpensesService';  
  
SEND ON CONVERSATION @dialog_handle (@Message) ;

The message will be sent to the ExpenssesService and placed in dbo.ExpenseQueue. Because there is no activation procedure associated to this queue, the message will remain in the queue until someone reads it.

Processing messages

The messages that are placed in the queue can be selected by using a standard SELECT query. The SELECT statement will not modify the queue and remove the messages. To read and pull the messages from the queue, you can use the RECEIVE Transact-SQL statement.

RECEIVE conversation_handle, message_type_name, message_body  
FROM ExpenseQueue;

Once you process all messages from the queue, you should close the conversation using the END CONVERSATION Transact-SQL statement.

Where is the documentation for Service Broker?

The reference documentation for [!INCLUDEssSB] is included in the [!INCLUDEssCurrent] documentation. This reference documentation includes the following sections:

See the previously published documentation for [!INCLUDEssSB] concepts and for development and management tasks. This documentation is not reproduced in the [!INCLUDEssCurrent] documentation due to the small number of changes in [!INCLUDEssSB] in [!INCLUDEssCurrent].

What's new in Service Broker

No significant changes are introduced in [!INCLUDEssCurrent]. The following changes were introduced in [!INCLUDEssSQL11].

Service broker and Azure SQL Database Managed Instance

  • Cross-instance service broker is not supported
  • sys.routes - Prerequisite: select address from sys.routes. Address must be LOCAL on every route. See sys.routes.
  • CREATE ROUTE - you cannot use CREATE ROUTE with ADDRESS other than LOCAL. See CREATE ROUTE.
  • ALTER ROUTE cannot use ALTER ROUTE with ADDRESS other than LOCAL. See ALTER ROUTE.

Messages can be sent to multiple target services (multicast)

The syntax of the SEND (Transact-SQL) statement has been extended to enable multicast by supporting multiple conversation handles.

Queues expose the message enqueued time

Queues have a new column, message_enqueue_time, that shows how long a message has been in the queue.

Poison message handling can be disabled

The CREATE QUEUE (Transact-SQL) and ALTER QUEUE (Transact-SQL) statements now have the ability to enable or disable poison message handling by adding the clause, POISON_MESSAGE_HANDLING (STATUS = ON | OFF). The catalog view sys.service_queues now has the column is_poison_message_handling_enabled to indicate whether poison message is enabled or disabled.

Always On support in Service Broker

For more information, see Service Broker with Always On Availability Groups (SQL Server).