Search code examples
sql-serverservice-broker

SQL Service Broker — one central SQL and more satellite SQL… transmission_status "disabled or not configured"

Summary: I do observe the "The Service Broker protocol transport is disabled or not configured." in the transmission_status of the queue. It seems that some more detail should be set. Or what am I missing?

The question is directly related to the answer to my earlier question. I did follow the advice with using special trick GRANT CONNECT ON ENDPOINT::[<brokerendpointname>] TO [public] to simplify the plug-in kind of configuration in the trusted local network:

create message type...
create contract ...
create queue ...
create service [tcp://MACHINE3:4022/Satellite] 
   on ...
   ([...]);
grant send on service::[tcp://MACHINE3:4022/Satellite] to [public];
create route transport with address = 'TRANSPORT';

For the case the detail may play the role, here is the extract of the real code:

USE [MySatelliteDB];

CREATE MESSAGE TYPE [//x/y/RequestMessage]
  VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [//x/y/ReplyMessage]
  VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT [//x/y/SimpleContract]
  ([//x/y/RequestMessage] SENT BY INITIATOR,
   [//x/y/ReplyMessage]   SENT BY TARGET);

CREATE QUEUE GenericQueue;
CREATE SERVICE [tcp://192.168.4.120:4022/GenericService]
  ON QUEUE GenericQueue ([//x/y/SimpleContract]);

GRANT SEND ON SERVICE::[tcp://192.168.4.120:4022/GenericService] TO [public];

CREATE ROUTE transport WITH ADDRESS = 'TRANSPORT';

The central machine uses the same except of the database name, and the IP address (ends with 158 in the case). Then I tried to send the explicit dummy message...

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [tcp://192.168.4.120:4022/GenericService]
     TO SERVICE N'tcp://192.168.4.158:4022/GenericService'
     ON CONTRACT [//x/y/SimpleContract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//x/y/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

It displayed

SentRequestMsg
<RequestMsg>Message for Target service.</RequestMsg>

... apparently as the result of the last select. This way I guess the COMMIT TRANSACTION succeeded. (No error message observed.)

Then I opened the SSMS window for the other SQL server and tried to receive...

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM GenericQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName =
   N'//x/y/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
        N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE
            [//x/y/ReplyMessage] (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

It displayed two results with unexpected values

ReceivedRequestMsg
NULL
---------------------------------------------
SentReplyMsg
NULL

No error messages observed.

When running the script...

USE [MySatelliteDB];

SELECT * FROM GenericQueue WITH (NOLOCK);
SELECT * FROM sys.transmission_queue;

I can see that something does not work correctly (was one line, wrapped manually)

conversation_handle                   to_service_name                        
1A227CA7-6F24-E211-B1EC-004063F5CE90  tcp://192.168.4.158:4022/GenericService  
------------------------------------------------------------------------------...
to_broker_instance                    from_service_name
<empty>                               tcp://192.168.4.120:4022/GenericService
------------------------------------------------------------------------------...
service_contract_name                 enqueue_time
//x/y/SimpleContract                  2012-11-01 22:01:15.440
------------------------------------------------------------------------------...
message_sequence_number  message_type_name  
0                        /x/y/RequestMessage
------------------------------------------------------------------------------...
is_conversation_error    is_end_of_dialog    message_body   
0                        0                   0x3C0052006...snip...3E00
------------------------------------------------------------------------------...
transmission_status                                                    priority
The Service Broker protocol transport is disabled or not configured.   5

What should I focus on to make it working?

Solution

  • The Service Broker protocol transport is disabled or not configured

    means the endpoint is not started. Try ALTER ENDPOINT [<brokerendpointname>] STATE=STARTED;. If the statement succeeds but the problem persists, check the ERRORLOG, it should display an error message shortly after you ran this statement stating why it could not start the endpoint. Most likely there is a listening port conflict.