none
Learning Service Broker & Basic Qs

    Question

  • I am in an environment where we have many untrusted domains running SQL Server and want to minimize/stop using SQL Server accounts.  I want to use Service Broker to use certificate based authentication so I can send monitoring information from all the different servers to a central monitoring database server, such as backup history, DMV information, and a few others. Something that's a very 'simple use case' scenario for service broker and have watched the PASS2011 video by @SQLRunr, Bob Beauchmann's video on SQLSkills, and read numerous blogs but am still confused by getting a simple SB app up and running.

    I created a table tbl_1 with the tables and stored procs needed to populate them with the relevant information and now need to send it over via SB to our central server.  There are currently just 2 tables.  1 for a list of all the DBs on that server, and 1 that holds some MSDB backup information.  The goal is to transfer these to the central server nightly  or at some other set interval.

    Right now I can:

    Send a request from one DB to another if there's only 1 row

    See the results of the request on the other

    End the conversation at the target

    I cannot:
    -Populate the information into a different table

    -End conversation on the initiators end

    -Select a * from a row

    This is my query to initiate the conversation. T he problem is in the SELECT TOP 1 CustomerName FROM.. I want it to be SELECT * FROM

    /* Actual Messaging */
    DECLARE @InitDlgHandle UNIQUEIDENTIFIER ;
    DECLARE @ChangeMsg NVARCHAR(MAX);
    
    BEGIN DIALOG CONVERSATION @InitDlgHandle 
       FROM SERVICE [//CentMon/LC0/InitMsgTypeService]
       TO SERVICE '//CentMon/LC0/ReplyMsgTypeService'
       ON CONTRACT [//CentMon/LC0/InitAndReplyMsgTypeContract] 
       ;
    
    	SET @ChangeMsg = (SELECT TOP 1 CustomerName FROM [dbo].[CBU] WHERE [BUFinishDate] > GETDATE() -1 )
    	;
    	
    	BEGIN TRANSACTION;
    	SEND ON CONVERSATION @InitDlgHandle 
    		MESSAGE TYPE [//CentMon/LC0/InitMsgType]
    		(@ChangeMsg);
    	COMMIT TRANSACTION
    	END CONVERSATION @InitDlgHandle 
    	



    This is how I am currently receiving the message, I'd like to receive it and place it into a table on a different DB on a diff server (currently though everything is on the same server)


    DECLARE @ch UNIQUEIDENTIFIER
    DECLARE @messagetypename NVARCHAR(256),
    @service_name NVARCHAR(512),
    @service_contract_name NVARCHAR(256)
    DECLARE @messagebody NVARCHAR(MAX)



    ;


    RECEIVE TOP(1)
    @ch = conversation_handle,
    --@service_name,
    @service_contract_name = service_contract_name,
    @messagetypename = message_type_name,
    @messagebody = message_body
    FROM ReplyMsgTypeQueue



    --TIMEOUT 600000


    SELECT @messagebody

    This is how I end the conversation at the target, I don't know how to automatically tell it to just end the conversation and also not sure how to end it on the initiators end:

    --I get the conversation ID from sys.conversation_endpoints

    DECLARE @h UNIQUEIDENTIFIER
    SET @h = '846430FB-FC78-E111-B180-0800272EE368'
    END CONVERSATION @h
    GO

    Any help is greatly appreciated!  Next I have to get this working between different servers and I don't have to worry about the plumbing for a monitoring solution across all these untrusted domains!  Thanks.


    Support my SQL MCM & Internal Training blog on AliRazeghi.Com.  Have a tough I.T. job and you need to talk to someone with no obligation?  Contact my certified associates and I at Rosonco.com!

    If you find a question answered please click 'mark as answer' or 'vote as helpful'.  This will help other users find answers quickly.  Thanks for visiting!







    • Edited by Ali Razeghi Wednesday, March 28, 2012 6:27 PM
    Wednesday, March 28, 2012 6:20 PM

Answers

  • Hi Ali Razeghi,

    To receive the message and put them into a table, you can directly query the information directly from the receive queue, and end the conversation according to the conversation handle from the receive queue:

    --Change the target table and filter columns you needed. You can use Linked Server for a remote SQL Server instance.
    INSERT INTO TABLE_NAME
    SELECT * FROM ReplyMsgTypeQueue 
    --End the conversation
    DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
     RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle
     FROM ReplyMsgTypeQueue
    END CONVERSATION @RecvReqDlgHandle;
    For more information, please pay attention to: Completing a Conversation Between Instances.


    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Ali Razeghi Thursday, March 29, 2012 4:52 PM
    Thursday, March 29, 2012 9:13 AM