数据库恢复后 Service Broker 不工作
在服务器上设置一个可用的 Service Broker，我们正在迁移到新服务器，但我似乎无法在新服务器上设置 Service Broker。
已经完成了（对我来说）显而易见的事情，例如在数据库上启用代理，删除路由、服务、合同、队列甚至消息类型并重新添加它们，使用 STATUS ON
SELECT * FROM sys.service_queues
设置 ALTER QUEUE给我一个列表队列，包括我自己的两个，显示为activation_enabled、receive_enabled 等。
不用说队列不起作用。当我将消息放入其中时，什么也没有进来，也没有任何东西出来。
有什么想法吗？我确信我错过了一些非常明显的东西......
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...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只是猜测：
恢复的数据库的 dbo 是在原始服务器上创建数据库的 Windows SID。这可能是本地 SID（例如 SERVERNAME\user），在新服务器上没有任何意义。此问题通常会影响激活的过程，并且可能影响消息传递，这两个问题的发生都是由于 SQL 无法模拟“dbo”。将 dbo 更改为有效的登录 SID（如 sa）可以解决此问题。
如果这不能解决问题，那么您需要追踪消息的去向。如果它们留在 sys.transmission_queue 中，那么您必须检查 Transmission_status。如果它们到达目标队列但没有发生激活，请检查 ERRORLOG。如果它们消失，则意味着您即发即忘（发送后立即结束），因此您将删除指示原因的错误消息。本文疑难解答对话框包含更多可查看的提示。
最后但并非最不重要的一点是，尝试使用 ssbdiagnose.exe。
Just a shot in the dark:
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.
除了 Remus 的回答之外，您可能还想检查 returnedDB 的 BrokerEnabled 属性。每当您还原数据库时，还原的数据库的 BrokerEnabled 属性都会设置为 False。因此，没有任何内容会进入您的队列。要解决此问题：
向下滚动到“Service Broker”组并验证“Broker”的值
Enabled”属性。如果它设置为 False，请将其更改为 True，然后
应该可以解决你的问题。
In addition to Remus's answer, You might also want to check the BrokerEnabled property of the restoredDB. Whenever you restore a DB, the BrokerEnabled property of the restored DB is set to False. For this reason nothing will go into your queue. To address this:
Scroll down to the "Service Broker" group and verify the value of the "Broker
Enabled" property. If it is set to False, change it to True and this
should solve your problem.