Service Broker - Messaging

  • All,

    Been trying to setup service broker as an async way of doing data level auditing. This is from one server to another. Both are SQL2005 sp2.

    This is the error:

    This message could not be delivered because the conversation ID could not be associated with an active conversation. The message origin is: 'Transport'.

    I am using a very simple message (to get this to work, I had to change <> to []):

    '[AuditMsg]

    [SourceTable]' + 'jct_am_ActivityActivity' + '[/SourceTable]

    [LinkID]' + '20' + '[/LinkID]

    [ChangeType]' + 'UPDATE' + '[/ChangeType]

    [ChangeSource]' + 'IRIS' + '[/ChangeSource]

    [ChangeDatetime]' + '20 July 2008' + '[/ChangeDatetime]

    [ChangeUsername]' + 'LIVE01\' + '[/ChangeUsername]

    [OLDValue]' + 'TESTZ|TEST' + '[/OLDValue]

    [HostName]' + 'SERVER077' + '[/HostName]

    [/AuditMsg]'

    If I run this message against the procedure that the QUEUE is supposed to use, it successfully inserts the record.

    However when I fire from SOURCE server it does not seem to reach the procedure.

    On the SOURCE I have the following in PROFILER (EventSubClass):

    1 - Create

    11 - BEGIN DIALOG

    1 - SEND Messaeg

    2 - Remote

    2 - Remote

    2 - Connected

    1 - Message with Acknowledgement Sent

    1 - Message with Acknowledgement Sent

    Looks like it sends the message again

    On the TARGET I have the following in PROFILER (EventSubClass):

    6 - Accept

    1 - Login Success

    1 - Local

    1 - Create

    12 - Dialog created

    6 - Received Sequenced Message

    3 - Message with Acknowledgement Received

    1 - Start

    2 - End

    1 - Local

    2 - Acknowledgement Sent

    Then it seems to receive duplicate message(s):

    1 - Local

    2 - Unsequenced Message

    I cannot see the Acknowledgements being received on the SOURCE server. Would this stop the message from hitting the QUEUE on the TARGET?

    So what do I need to do to fix this?

    Please ask if more info is needed, no errors in SQL logs or in the Windows event logs.

    Thanks in advance,

    Graham

  • Check the routes exists;

    In Source to Destination

    And

    In Destination to Source

    The route should be defined at both ends, Source and Destination. Otherwise, the conversation will not take place.

    Also, go for IP:port_number based route instead of TRANSPORT type.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Here are my routes:

    Source

    CREATE ROUTE RouteDataSender

    AUTHORIZATION dbo

    WITH

    SERVICE_NAME = '//Audit/AcMan_DataWriter',

    BROKER_INSTANCE = '51E3A8D4-BE8B-4162-8B90-245E321F674A',

    ADDRESS = 'TCP://10.0.x.x:x'

    GO

    Target:

    CREATE ROUTE [RouteDataReceiver]

    AUTHORIZATION dbo

    WITH

    SERVICE_NAME = '[tcp://10.0.x.x:x/db_ActivityManagement/Audit/DataSender]',

    BROKER_INSTANCE = 'AE6697F2-83BA-48FF-93CF-DC8C7BA40FFA',

    ADDRESS = 'TCP://10.0.y.y:y'

    GO

    Have I done something with these?

  • Well i dont see any problem in the Routes. Just check the following;

    1. Grant Connect to Endpoints to [public] at both sender and Receiver.

    2. Grant SEND to Service to [public] at both sender and Receiver.

    3. Grant Control on Service to Public on Receiver

    4. Alter database of sender and receiver to Set Trustworthy ON on sender and receiver.

    Also, Empty the sys.Conversation_endpoints DM and transmission_queue by using END CONVERSATION ... WITH CLEANUP. and try again with the empty transmission queue.

    Good Luck.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Tried all that and still the same thing!

    How does the messaging work?

    When the intial message is sent, is it sent to the initiator queue or the target queue?

    If it is the target, must the the acknowledgement (that the target sends to the initiator) be received/accepted by the initiator, before the message will hit the actual queue?

    That message is not making it back to the initiator and then the initiator re-sends, so I am thinking that is where my issue is...

  • Using a packet sniffer, it seems that I may have the ports setup incorrectly.

    Packet going from source to target:

    SrcPort - 4509

    DstPort - 4022

    Packet going from target to source:

    SrcPort - 4022

    DstPort - 4509

    This kind of makes sense, as the service broker on the source server is listening on port 4022. But where is this set? Seems to change on ever conversation I setup...

  • Ports are mentioned in Endpoints and then in routes.

    Try using same port numbers at sender and receiver, say, 9998.

    My Service Broker is working perfectly on this port.

    I have never tried even my self on different port numbers at sender and receiver.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Please show us your Sending code.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Both routes specify the port as 4022. So not sure why the one appears as it does.

    I have setup Service Broker on another instance of SQL going between 2 databases and it works perfectly. So I am now retro fitting the works bits back into the cross server version.

    here is my procedure that I use to send the code:

    ALTER PROCEDURE [dbo].[usp_AuditSendData_f]

    (

    @AuditedData XML

    )

    AS

    BEGIN

    BEGIN TRY

    DECLARE @dlgId UNIQUEIDENTIFIER

    -- Check if our database already has a dialog id that was previously used

    -- if it does reuse the conversation

    SELECT@dlgId = tlg_dialogID

    FROMdbo.tbl_AuditDialog

    WHEREtlg_dbID = DB_ID()

    -- if we're reusing a dialog conversation then

    -- check if it is in a good state for conversation ...

    IF @dlgId IS NOT NULL

    AND NOT EXISTS(SELECT*

    FROMsys.conversation_endpoints

    WHEREconversation_handle = @dlgId AND state IN ('SO', 'CO'))

    BEGIN

    -- ... if it isn't then delete it from our saved dialogs table ...

    DELETE

    FROMdbo.tbl_AuditDialog

    WHEREtlg_dbID = DB_ID()

    AND tlg_dialogID = @dlgId

    -- ... optionally you can end the conversation here,

    -- but it is better to end it from target server

    -- END CONVERSATION @dlgId WITH CLEANUP

    -- ... and set it to null to create a new dialog

    SELECT@dlgId = NULL

    END

    IF @dlgId IS NULL

    BEGIN

    -- Begin the dialog, with the new Id

    BEGIN DIALOG CONVERSATION @dlgId

    FROM SERVICE [tcp://10.0.0.89:4022/db_ActivityManagement/Audit/DataSender]

    TO SERVICE '//Audit/AcMan_DataWriter',

    -- this is a MasterAuditDatabase Service Broker Id

    -- (change it to yours and remove )

    '51E3A8D4-BE8B-4162-8B90-245E321F674A'

    ON CONTRACT [//Audit/AcMan_Contract]

    WITH ENCRYPTION = OFF;

    -- add our db's dialog to AuditDialogs table if it doesn't exist yet

    INSERT INTO dbo.tbl_AuditDialog(tlg_dbID, tlg_dialogID)

    SELECTDB_ID(), @dlgId

    END

    -- Send our data to be audited

    ;SEND ON CONVERSATION @dlgId

    MESSAGE TYPE [//Audit/AcMan_rMessage] (@AuditedData)

    END TRY

    BEGIN CATCH

    INSERT INTO dbo.tbl_AuditError

    (

    aer_ErrorProcedure

    ,aer_ErrorLine

    ,aer_ErrorNumber

    ,aer_ErrorMessage

    ,aer_ErrorSeverity

    ,aer_ErrorState

    ,aer_AuditedData

    )

    SELECT ERROR_PROCEDURE()

    ,ERROR_LINE()

    ,ERROR_NUMBER()

    ,ERROR_MESSAGE()

    ,ERROR_SEVERITY()

    ,ERROR_STATE()

    ,@AuditedData

    END CATCH

    END

  • You have this check for your re-used conversation handle being either NULL, non-existent or bad state:

    IF @dlgId IS NOT NULL

    AND NOT EXISTS( SELECT * FROM sys.conversation_endpoints

    WHERE conversation_handle = @dlgId AND state IN ('SO', 'CO')

    )

    But it appears that you only handle the NULL case:

    IF @dlgId IS NULL

    BEGIN -- Begin the dialog, with the new Id

    BEGIN DIALOG CONVERSATION @dlgId

    FROM SERVICE [tcp://10.0.0.89:4022/db_ActivityManagement/Audit/DataSender]

    TO SERVICE '//Audit/AcMan_DataWriter',

    -- this is a MasterAuditDatabase Service Broker Id

    -- (change it to yours and remove

    '51E3A8D4-BE8B-4162-8B90-245E321F674A'

    ON CONTRACT [//Audit/AcMan_Contract]

    WITH ENCRYPTION = OFF;

    -- add our db's dialog to AuditDialogs table if it doesn't exist yet

    INSERT INTO dbo.tbl_AuditDialog(tlg_dbID, tlg_dialogID)

    SELECT DB_ID(), @dlgId

    END

    -- Send our data to be audited

    ;SEND ON CONVERSATION @dlgId

    MESSAGE TYPE [//Audit/AcMan_rMessage] (@AuditedData)

    Note that if it is non-existent or in a bad state, nothing is done, but the SEND still runs and tries to use it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.

    This all works in the single instances across multiple databases environment.

    So I am now re-starting from scratch. Restored the source database, dropped and re-created the target database.

    Using exactly what I had for the same instance version, I am now trying to change the services and the routes, server bindings, endpoints, etc.

    For future reference, I found an interesting way of checking the target queues, rename the procedure that is supposed run on the target queue. This then leaves the data in the queue, and you can manual run the procedure once the data is in the queue... Helped me a lot on the single instance setup 🙂

  • Ok, I have gotten the messaging to work (across servers), but I have one final issue.

    The acknowledgement message is not working going from the TARGET to the SOURCE correctly. In profile on the TARGET it processes a task for Acknowledgement Sent, but nothing is received on the SOURCE side. This then cause the SOURCE to re-send the message, which the TARGET does not accept, as it has already accepted and processes that message.

    I am going break out the packet sniffer again.........

    Will post the simplified code once I have gotten it all to work 🙂

  • graham cleverly (7/14/2008)


    If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.

    No, look at it again. It only does that if it is NULL. If it is non-existant or in a bad state, it falls through to the SEND.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (7/14/2008)


    graham cleverly (7/14/2008)


    If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.

    No, look at it again. It only does that if it is NULL. If it is non-existant or in a bad state, it falls through to the SEND.

    I accept that if the conversation is in a bad state I will have issues. However if its non-existant? When compared to the dialog table?

    Still struggling with this return message, admittedly not spent much time on it. Where is the first place to start looking? The route from TARGET to SOURCE?

  • This is the only clear reference that I can find to this specific error message and how to debug it:http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=356938&SiteID=1

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply