SQL Server Service Broker

APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

Application developers who use Service Broker can distribute data workloads across several databases without programming complex communication and messaging internals. This reduces development and test work because Service Broker 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. Service Broker ensures that all tasks are managed in the context of transactions to assure reliability and technical consistency.

Where is the documentation for Service Broker?

The reference documentation for Service Broker is included in the SQL Server 2017 documentation. This reference documentation includes the following sections:

What's new in Service Broker

No significant changes are introduced in SQL Server 2017. The following changes were introduced in SQL Server 2012 (11.x).

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).