Service Broker not working after database restore

Question

Have a working Service Broker set up on a server, we're in the process of moving to a new server but I can't seem to get Service Broker set up on the new box.

Have done the obvious (to me) things like Enabling Broker on the DB, dropping the route, services, contract, queues and even message type and re adding them, setting ALTER QUEUE with STATUS ON

SELECT * FROM sys.service_queues

gives me a list of the queues, including my own two, which show as activation_enabled, receive_enabled etc.

Needless to say the queues aren't working. When I drop messages into them nothing goes in and nothing comes out.

Any ideas? I'm sure there's something really obvious I've missed...

Answer 1

Just a shot in the dark:

ALTER AUTHORIZATION ON DATABASE::[restored db name] TO [sa];

The dbo of the restored database is the Windows SID that created the db on the original server. This may be a local SID (eg. SERVERNAME\user) that has no meaning on the new server. This problem usually affects activated procedures and may affect message delivery, both issues happening due to inability of SQL to impersonate 'dbo'. Changing dbo to a valid login SID (like sa) would fix it.

If this doesn't fix it, then you need to track down where do the messages go. If they stay in sys.transmission_queue, then you must check the transmission_status. If they reach the target queueu but no activation occurs, check ERRORLOG. If they vanish, it means you do fire-and-forget (SEND followed immediately by END) and you are therefore deleting the error message that indicates the cause. This article Troubleshooting Dialogs contains more tips where to look.

And last, but not least, try using ssbdiagnose.exe.