February 12, 2016 at 5:34 am
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?
February 17, 2016 at 2:46 am
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
February 17, 2016 at 8:13 am
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.
February 22, 2016 at 5:16 am
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.
March 3, 2016 at 6:07 am
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.
March 3, 2016 at 6:14 am
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
March 5, 2016 at 4:31 am
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.
April 20, 2016 at 1:01 am
OK, it worked.. problem with a firewall..
Thanks for the help.
September 7, 2018 at 7:32 am
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply