I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example.
A is SQL 2005, B is SQL 2012. It doesn’t work, and I have not been able to track down a good reason why. What I can see is:
The Profiler on the receiving end doesn’t even show any Service Broker events, indicating nothing is getting to it.
But using ssbdiagnose as below, it says congratulations, you have no errors:
C:\Users\me>ssbdiagnose -level info -E configuration from service ServerAServiceTest -S ServerA -d MyDB to Service ServerBServiceTest -S ServerB -d MyDB on Contract myContract Microsoft SQL Server 11.0.2100.60 Service Broker Diagnostic Utility 0 Errors, 0 Warnings
Also in the transmission queue, the only thing I see that seems particularly off is ‘to_broker_instance’ is null, though I explicitly specified that info when setting up the route.
Further, no errors are showing up in transmission_status.
Also, SQL Server error logs are shedding no light.
As for firewall issues, well, these are test servers not accessible from the outside so I tried turning the firewalls off altogether.
One thing that is bothersome, I will get these:
An exception occurred while enqueueing a message in the target queue. Error: 15581 State: 7. Please create a master key in the database or open the master key in the session before performing this operation.
I do open the key and these go away. But I shouldn't have to repeatedly open the key everytime I want to do something, should I? I suspect this is part of the problem even though as mentioned the errors go away.
Sorry for the somewhat open question - even some help identifying where to get more informative errors or debugging info would be great. This is new territory for me.