none
What is Service Broker is used (or supposed to be used) for?

    Question

  • When Service Broker was introduced in 2005 I tried some simple "Hello World" examples which were supplied in order to demonstrate its ability to send messages from one session to another. I didn't need such a tool and wondered who needs it..
    It took me some years to discover its ability to execute stored procedures asynchronously, and I have already implemented it in some systems.
    I have asked some of my colleagues about Service Broker, many of them use his asynchronous ability to execute procedures, none is using it to send messages.

    My question: I suspect the original intention was to supply a messaging system and the asynchronous execution was only a sub product, but somehow - the sub product became a main product. Is it true? Otherwise - do you know many messaging systems rely on Service Broker?
    I ask this question because I don't have any explanation why is it so complex and why I have to create so many objects (message type,  queues, services, contracts etc.) which are essential for messaging and not for asynchronous execution.
    Thanks!


    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    Thursday, March 20, 2014 9:14 AM

Answers

  • My question: I suspect the original intention was to supply a messaging system and the asynchronous execution was only a sub product, but somehow - the sub product became a main product. Is it true? Otherwise - do you know many messaging systems rely on Service Broker?
    I ask this question because I don't have any explanation why is it so complex and why I have to create so many objects (message type,  queues, services, contracts etc.) which are essential for messaging and not for asynchronous execution.

    Service Broker is commonly used to execute stored procedures asynchronously but I wouldn't go so far as to say that is the main goal.  SB provides the infrastructure for a number of SQL Server features, such as Database Mail and Query Notifications, and can be leveraged by applications that benefit from transactional messaging to facilitate guaranteed delivery and asynchronous processing.  Note that the target service that processes the messages can be implemented in any programming language, not just via an activated T-SQL proc.

    You mention that you must create message types, queues, services, contracts, etc. for a SB application.  That is only partially true.  At a minimum, you need only a service and queue, relying on default message types and contracts, and a single service acting in both initiator and target roles.  However, applications often employ message types and contracts to enforce validation of messages.  Otherwise, messages are just a blob of binary data that will need to be validated by the consuming application and it can be confusing for the same service to act as both initiator and target.  Following Service Broker best practices does add complexity but is more robust.

    Below is an example with only a single queue and service.  This application is fragile because there is no message validation or contract; passing anything other that well-formed XML will break it .

    USE master;
    DROP DATABASE Test;
    CREATE DATABASE Test;
    ALTER DATABASE Test SET ENABLE_BROKER;
    GO
    
    USE Test;
    GO
    
    CREATE QUEUE SingleQueue;
    GO
    
    CREATE SERVICE SingleService
    	ON QUEUE dbo.SingleQueue ([DEFAULT]);
    GO
    
    --send a message
    DECLARE @dialog_handle uniqueidentifier;
    BEGIN DIALOG CONVERSATION @dialog_handle
       FROM SERVICE SingleService
       TO SERVICE 'SingleService'
       WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @dialog_handle (N'<Message>Some Payload</Message>');
    GO
    
    --process user and system messages
    DECLARE 
    	 @conversation_handle uniqueidentifier
    	,@message varbinary(MAX)
    	,@message_type_name sysname;
    WHILE 1 = 1
    BEGIN
    	WAITFOR (
    		RECEIVE TOP(1)
    			 @conversation_handle = conversation_handle
    			,@message = message_body
    			,@message_type_name = message_type_name
    		FROM dbo.SingleQueue
    		), TIMEOUT 5000;
    	IF @@ROWCOUNT = 0 BREAK;
    	SELECT @message_type_name AS message_type_name, @message AS message;
    	IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    	BEGIN
    		--end initiator side of conversation normally
    		END CONVERSATION @conversation_handle;
    	END
    	ELSE
    	BEGIN
    		IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    		BEGIN
    			--end initiator side of conversation on unexpected error
    			END CONVERSATION @conversation_handle WITH ERROR = 1 DESCRIPTION = 'Error message receied';
    		END
    		ELSE
    		BEGIN
    			SELECT CAST(CAST(@message AS nvarchar(MAX)) AS xml).value('/Message[1]', 'varchar(100)') AS user_message;
    			--end target side of conversation normally
    			END CONVERSATION @conversation_handle;
    		END
    	END
    END;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, March 20, 2014 11:36 AM

All replies

  • My question: I suspect the original intention was to supply a messaging system and the asynchronous execution was only a sub product, but somehow - the sub product became a main product. Is it true? Otherwise - do you know many messaging systems rely on Service Broker?
    I ask this question because I don't have any explanation why is it so complex and why I have to create so many objects (message type,  queues, services, contracts etc.) which are essential for messaging and not for asynchronous execution.

    Service Broker is commonly used to execute stored procedures asynchronously but I wouldn't go so far as to say that is the main goal.  SB provides the infrastructure for a number of SQL Server features, such as Database Mail and Query Notifications, and can be leveraged by applications that benefit from transactional messaging to facilitate guaranteed delivery and asynchronous processing.  Note that the target service that processes the messages can be implemented in any programming language, not just via an activated T-SQL proc.

    You mention that you must create message types, queues, services, contracts, etc. for a SB application.  That is only partially true.  At a minimum, you need only a service and queue, relying on default message types and contracts, and a single service acting in both initiator and target roles.  However, applications often employ message types and contracts to enforce validation of messages.  Otherwise, messages are just a blob of binary data that will need to be validated by the consuming application and it can be confusing for the same service to act as both initiator and target.  Following Service Broker best practices does add complexity but is more robust.

    Below is an example with only a single queue and service.  This application is fragile because there is no message validation or contract; passing anything other that well-formed XML will break it .

    USE master;
    DROP DATABASE Test;
    CREATE DATABASE Test;
    ALTER DATABASE Test SET ENABLE_BROKER;
    GO
    
    USE Test;
    GO
    
    CREATE QUEUE SingleQueue;
    GO
    
    CREATE SERVICE SingleService
    	ON QUEUE dbo.SingleQueue ([DEFAULT]);
    GO
    
    --send a message
    DECLARE @dialog_handle uniqueidentifier;
    BEGIN DIALOG CONVERSATION @dialog_handle
       FROM SERVICE SingleService
       TO SERVICE 'SingleService'
       WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @dialog_handle (N'<Message>Some Payload</Message>');
    GO
    
    --process user and system messages
    DECLARE 
    	 @conversation_handle uniqueidentifier
    	,@message varbinary(MAX)
    	,@message_type_name sysname;
    WHILE 1 = 1
    BEGIN
    	WAITFOR (
    		RECEIVE TOP(1)
    			 @conversation_handle = conversation_handle
    			,@message = message_body
    			,@message_type_name = message_type_name
    		FROM dbo.SingleQueue
    		), TIMEOUT 5000;
    	IF @@ROWCOUNT = 0 BREAK;
    	SELECT @message_type_name AS message_type_name, @message AS message;
    	IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    	BEGIN
    		--end initiator side of conversation normally
    		END CONVERSATION @conversation_handle;
    	END
    	ELSE
    	BEGIN
    		IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    		BEGIN
    			--end initiator side of conversation on unexpected error
    			END CONVERSATION @conversation_handle WITH ERROR = 1 DESCRIPTION = 'Error message receied';
    		END
    		ELSE
    		BEGIN
    			SELECT CAST(CAST(@message AS nvarchar(MAX)) AS xml).value('/Message[1]', 'varchar(100)') AS user_message;
    			--end target side of conversation normally
    			END CONVERSATION @conversation_handle;
    		END
    	END
    END;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, March 20, 2014 11:36 AM
  • Dan- thank you for you useful help and the detailed example!

    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    Sunday, March 23, 2014 1:00 PM
  • http://technet.microsoft.com/en-us/library/bb522893.aspx

    http://technet.microsoft.com/en-us/library/ms345108(v=sql.90).aspx


    Raju Rasagounder MSSQL DBA

    Sunday, March 23, 2014 1:06 PM