Configuring Service Broker Architecture

  • It looks like route is missing or invalid on the Source db.

    Do you have firewall on 4022 port? Have you created SampleServiceTarget service already?

  • Hi,

    sorry for the delay.. I had to do some other tasks before coming back to this...

    As I followed the scripts attached to the article, there is a [SampleServiceSource] on the source server and a [SampleServiceTarget] on the target server.

    Anyway, I'm questioning myself on the query you provided in the article one need to use to test message transmission... Should I update the TO SERVICE clause like this:

    DECLARE @convHandle UNIQUEIDENTIFIER

    BEGIN DIALOG @convHandle

    FROM SERVICE [SampleServiceSource]

    TO SERVICE '\\TargetServer\SampleServiceTarget'

    ON CONTRACT [SampleContract]

    --sql code to create and send a message to remote service

    DECLARE @XMLMessage as XML;

    SET @XMLMessage = (SELECT TOP 1 * FROM [Order] FOR

    XML PATH('SampleMessage'), ELEMENTS XSINIL, TYPE

    );

    SEND ON CONVERSATION @convHandle MESSAGE TYPE [SampleMessage] (@XMLMessage) ;

    GO

  • Jefferson Elias (2/17/2016)


    Hi,

    sorry for the delay.. I had to do some other tasks before coming back to this...

    As I followed the scripts attached to the article, there is a [SampleServiceSource] on the source server and a [SampleServiceTarget] on the target server.

    Anyway, I'm questioning myself on the query you provided in the article one need to use to test message transmission... Should I update the TO SERVICE clause like this:

    DECLARE @convHandle UNIQUEIDENTIFIER

    BEGIN DIALOG @convHandle

    FROM SERVICE [SampleServiceSource]

    TO SERVICE '\\TargetServer\SampleServiceTarget'

    ON CONTRACT [SampleContract]

    --sql code to create and send a message to remote service

    DECLARE @XMLMessage as XML;

    SET @XMLMessage = (SELECT TOP 1 * FROM [Order] FOR

    XML PATH('SampleMessage'), ELEMENTS XSINIL, TYPE

    );

    SEND ON CONVERSATION @convHandle MESSAGE TYPE [SampleMessage] (@XMLMessage) ;

    GO

    Aloha,

    Something is wrong with your route to SampleServiceTarget. you need to check and fix it. Once it is done, run ssbdiagnose few times to make sure that errors have disappeared. Deployment scripts should be correct and working unless sql servers are hosted on untrusted domains or you have a firewall between them.

    Hope it helps,

    Wal.

  • I followed exactly the script you provided.

    As you suggest, there might be a problem in contacting the target service.

    The configuration is as follows :

    192.168.98.xx for the source and 192.168.96.yy for the target. Both are in a LSP so they might see each other without any problem but it seems not to be the case !

    Should both servers participating in a Service Broker conversation be in the same LAN ?

    Regards,

    Jefferson.

  • Jefferson Elias (2/22/2016)


    I followed exactly the script you provided.

    As you suggest, there might be a problem in contacting the target service.

    The configuration is as follows :

    192.168.98.xx for the source and 192.168.96.yy for the target. Both are in a LSP so they might see each other without any problem but it seems not to be the case !

    Should both servers participating in a Service Broker conversation be in the same LAN ?

    Regards,

    Jefferson.

    No, servers can be on different networks and domains. However, configured tcp ports need to be opened between servers.

    Provided scripts should work on sql server instances installed and configured in the same domain.

    Can you send the output of "select * from sys.routes" query on both instances?

    W.

  • Hi WAL,

    here is the output for each servers.

    nameroute_idprincipal_idremote_service_namebroker_instancelifetimeaddressmirror_address

    AutoCreatedLocal655361NULLNULLNULLLOCALNULL

    SampleServiceRoute655401SampleServiceTargetC3A0E781-C11F-4A2D-B777-02538364F23FNULLTCP://192.168.98.93:4022NULL

    nameroute_idprincipal_idremote_service_namebroker_instancelifetimeaddressmirror_address

    AutoCreatedLocal655361NULLNULLNULLLOCALNULL

    SampleServiceRoute655421SampleServiceSourceDBEC22F6-2B9A-4761-8404-5809B6F96893NULLTCP://192.168.96.90:4022NULL

  • These routes look ok unless broker_instance guid do not match. Do they match?

    Can you try to add binding on the source and rerun ssbdiagnostic:

    CREATE REMOTE SERVICE BINDING SourceBinding

    TO SERVICE '<TargetServiceName>'

    WITH USER = <Username>, ANONYMOUS=ON ;

    It may be a quick solution in your case. I don't have full view of your sql estate, accounts used and current configuration. Therefore, it is difficult to help. I hope you can figure it out yourself. Just need more patience and troubleshooting.

    Cheers,

    W.

  • OK, it worked.. problem with a firewall..

    Thanks for the help.

  • Hello all,

     I appreciate that this is an old article, but it is handy this was posted on the headlines today as I was asked the following question this morning about queues on our Clustered instance:

    Good Morning Dave,Over the past couple of months I’ve been trying to ascertain why our service brokerqueues are stopping on <ServerName>  It took a while to nail this down; but I’ve worked out why.  When thedatabase  is failed-over (He means When the Active Node is switched); this has happened over the last couple of weeks; andthe live node becomes ready any service broker queues in the live node are notstarted / enabled. 

    Is there any procedure you can put in place to ensure the high availability ofthese queues which are not affected by a failover?

    The databases concerned are YYY and ZZZ.

    YYY has 1 queue  called “Importer.UploadQueue”.

    ZZZ has 2 queues, “Events.EventQueue” and“Search.SearchQueue”. 

    It seems the YYY one suffers the most from the failover, and goes intoan “off” state.

    I have investigated why this could be so and the only difference between databases YYY (not working) and ZZZ is that YYY's Recovery mode is simple and ZZZ's is Full.

    I have suggested we switch YYY to the Full before we test this again.
    Has anybody seen this on a cluster?  And better yet, fixed it?

    SQL Server 2014, SP2 CU3

    Tia
    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

Viewing 9 posts - 16 through 24 (of 24 total)

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