Service Broker Concepts, Queues, Services, Dialogs, Activation, and Reliable Messaging

Learning outcomes

Some ServiceHub work should happen reliably after the transaction that accepts a request, but it should not force the foreground session to perform every downstream step synchronously. SQL Server Service Broker provides durable, transactional messaging primitives inside SQL Server: messages, contracts, queues, services and dialogs. Its value comes from coupling SEND and RECEIVE to database transactions. Its boundary is equally important: it is not a drop-in Kafka/RabbitMQ ecosystem, it does not promise global ordering across unrelated dialogs, and external fan-out/consumer-group semantics must be designed explicitly.

1. Mental model: typed conversations terminate at services backed by queues

A message type names and optionally validates a message body. A contract states which side may send which message types. A queue stores incoming messages. A service is the logical address attached to a queue and contracts. A dialog conversation is a long-lived ordered exchange between two Service Broker services. For remote delivery, routes and a Service Broker endpoint become part of the topology; the mandatory lab keeps both services in one database so no network endpoint is required.

Service Broker is enabled by default for newly created SQL Server databases in current versions, but restored/copied databases and operational changes can produce different states. Always verify sys.databases.is_broker_enabled instead of assuming. Disabling broker delivery leaves messages pending in the transmission path; it does not silently discard them.

SELECT name, is_broker_enabled, service_broker_guidFROM sys.databasesWHERE name = N'ServiceHubLab';GOUSE ServiceHubLab;GOIF SCHEMA_ID(N'lab18') IS NULL  EXEC(N'CREATE SCHEMA lab18 AUTHORIZATION dbo;');GO

2. Build a local dialog and observe transactional delivery

USE ServiceHubLab;GOIF EXISTS (SELECT 1 FROM sys.services WHERE name=N'//ServiceHub/Lab18/Initiator')  DROP SERVICE [//ServiceHub/Lab18/Initiator];IF EXISTS (SELECT 1 FROM sys.services WHERE name=N'//ServiceHub/Lab18/Target')  DROP SERVICE [//ServiceHub/Lab18/Target];IF OBJECT_ID(N'lab18.InitiatorQueue', N'SQ') IS NOT NULL DROP QUEUE lab18.InitiatorQueue;IF OBJECT_ID(N'lab18.TargetQueue', N'SQ') IS NOT NULL DROP QUEUE lab18.TargetQueue;IF EXISTS (SELECT 1 FROM sys.service_contracts WHERE name=N'//ServiceHub/Lab18/WorkContract')  DROP CONTRACT [//ServiceHub/Lab18/WorkContract];IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name=N'//ServiceHub/Lab18/WorkRequested')  DROP MESSAGE TYPE [//ServiceHub/Lab18/WorkRequested];GOCREATE MESSAGE TYPE [//ServiceHub/Lab18/WorkRequested]  VALIDATION = WELL_FORMED_XML;CREATE CONTRACT [//ServiceHub/Lab18/WorkContract]  ([//ServiceHub/Lab18/WorkRequested] SENT BY INITIATOR);CREATE QUEUE lab18.InitiatorQueue;CREATE QUEUE lab18.TargetQueue;CREATE SERVICE [//ServiceHub/Lab18/Initiator]  ON QUEUE lab18.InitiatorQueue ([//ServiceHub/Lab18/WorkContract]);CREATE SERVICE [//ServiceHub/Lab18/Target]  ON QUEUE lab18.TargetQueue ([//ServiceHub/Lab18/WorkContract]);GO

DECLARE @dialog uniqueidentifier;DECLARE @body nvarchar(max) = N'<workOrder id="1001" action="dispatch" />';BEGIN TRAN;BEGIN DIALOG CONVERSATION @dialog  FROM SERVICE [//ServiceHub/Lab18/Initiator]  TO SERVICE N'//ServiceHub/Lab18/Target'  ON CONTRACT [//ServiceHub/Lab18/WorkContract]  WITH ENCRYPTION = OFF;SEND ON CONVERSATION @dialog  MESSAGE TYPE [//ServiceHub/Lab18/WorkRequested] (@body);COMMIT;SELECT conversation_handle,       message_type_name,       CAST(message_body AS nvarchar(max)) AS message_bodyFROM lab18.TargetQueue;GO

The message becomes durable as part of the sender’s commit. If the transaction rolls back, the send rolls back with it. On the target, RECEIVE is likewise transactional: a rollback returns received messages to the queue. This creates a strong in-database atomicity boundary that is valuable for reliable work dispatch.

DECLARE @target_handle uniqueidentifier;DECLARE @message_type sysname;DECLARE @message_body varbinary(max);BEGIN TRAN;WAITFOR(  RECEIVE TOP (1)         @target_handle = conversation_handle,         @message_type = message_type_name,         @message_body = message_body  FROM lab18.TargetQueue), TIMEOUT 5000;SELECT @message_type AS received_type,       CAST(@message_body AS nvarchar(max)) AS received_body;IF @target_handle IS NOT NULL  END CONVERSATION @target_handle;COMMIT;GO

Ending the target endpoint causes an EndDialog system message to flow back to the initiator, which the initiator should receive and use to close its endpoint. Conversation cleanup is part of application correctness; abandoned endpoints can accumulate state.

3. Activation is queue-driven concurrency with a security context

Internal activation associates a stored procedure with a queue. When work arrives and activation is required, SQL Server starts procedure instances up to MAX_QUEUE_READERS. The procedure executes under the configured principal. This is not a general cron scheduler; it is a queue-reader lifecycle optimized for Service Broker work. Set the reader count from measured processing cost, contention, ordering requirements and downstream capacity—not from CPU count folklore.

SELECT  SCHEMA_NAME(q.schema_id) AS schema_name,  q.name,  q.is_receive_enabled,  q.is_activation_enabled,  q.max_readers,  q.activation_procedure,  q.execute_as_principal_id,  q.is_poison_message_handling_enabledFROM sys.service_queues AS qWHERE q.name IN (N'InitiatorQueue', N'TargetQueue');SELECT * FROM sys.dm_broker_activated_tasksWHERE database_id = DB_ID(N'ServiceHubLab');GO

The activation principal needs only the permissions required by the queue-processing module and referenced objects. Using EXECUTE AS OWNER without reviewing ownership chains and module permissions can unintentionally widen the security boundary.

4. Wrong approach: keep retrying a poison message until the whole queue stops

A poison message is not necessarily corrupt. It is a message that repeatedly causes the receive transaction to roll back—for example, a once-valid request whose referenced business object was removed. Automatic poison-message detection can disable a queue after five rollbacks of a transaction that receives from it. This is a circuit breaker, not the normal error-handling strategy.

SELECT name,       is_receive_enabled,       is_enqueue_enabled,       is_poison_message_handling_enabledFROM sys.service_queuesWHERE name IN (N'InitiatorQueue', N'TargetQueue');SELECT conversation_handle,       to_service_name,       transmission_status,       enqueue_timeFROM sys.transmission_queueORDER BY enqueue_time;SELECT state_desc,       far_service,       lifetime,       dialog_timerFROM sys.conversation_endpointsWHERE far_service LIKE N'//ServiceHub/Lab18/%';GO

If a queue is OFF, identify the failing message and processing rule, repair or quarantine the cause according to the application contract, then use ALTER QUEUE ... WITH STATUS = ON. Blindly clearing a queue destroys the very evidence needed to explain business divergence.

5. Production judgment and bridge

Use Service Broker when transactional, SQL Server-native asynchronous work and dialog semantics are valuable, especially when message handling belongs close to database transactions. Operate queue depth, disabled queues, transmission errors, activation tasks, conversation lifetime and poison handling. For cross-instance designs, add endpoints, routing, transport security and availability-group behavior deliberately. Lesson 4 moves to a very different integration boundary: synchronous distributed queries through linked servers, where remote latency, provider behavior, authentication and distributed transactions can couple two systems at request time.

Authoritative references

• SQL Server Service Broker
• Create Service Broker queues
• Handle poison messages
• Start and stop Service Broker queues
• BEGIN DIALOG CONVERSATION