June 7, 2012 at 8:27 am
Have implemented SQL Service Broker at one customer's site and it is up and running fine. Took the same scripts from that customer and ran it on a different customer's databases, changing the name of the target and initiator servers. Running into "The message has been dropped because the service broker in the target database is unavailable: 'The service broker is administratively disabled'." <== what does that mean?
Here's the scenario:
Initiator sends out message to Target. Target receives message properly, runs activation proc properly, sends back a confirmation message, ending the dialog. The return message doesn't make it back to the Initiator, conversation is in Disconnected_Outbound status.
In profiler, nothing is showing up on the Initiator. It just keeps on sending message for acknowledgement, nothing else. In Target profiler, the message above is shown constantly once the activated proc sends the message back, ending the conversation.
I have confirmed that firewall is not on. What is puzzling is that the dialog is initiated and connected, checked via sys.dm_broker_connections.
The databases are able to communicate with one another. If the target machine sends out a message to the initiator, everything works fine. The dialog ends correctly, the error is only thrown when the initiator sends out the message.
Any insight into why this occuring is truly appreciated or what the heck the message is alluding to!
Jay
June 7, 2012 at 11:24 am
It's amazing once you ask for help, you see things from a different perspective. Ok, here's the gist of it
If "a" GUID of the service_broker_guid column in sys.databases in the target database matches the initiator's database service_broker_guid, then the service broker tries to route the message to that database, even though you specify a return address which is not local and not guid specific. In my scenario, I actually specify 'Transport' as the return address. How and why this occurs, no idea. That was why the message stating the broker was disabled did not make sense because the broker in the Initiator was up and running. In hindsight, it makes sense because the broker in the local database was turned off.
Thanks!
November 22, 2012 at 12:19 pm
Tkx for following up on your own post. I had this exact same problem... I had restored a backup copy of my target DB onto the source DB server... bad news.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply