集成 SQL Service Broker 和 NServiceBus

发布于 2024-12-04 17:08:28 字数 2619 浏览 5 评论 0原文

我的要求是我有不同状态的“项目”。我需要根据这些项目的状态执行各种不同的操作（发送电子邮件、存储文件、更新内部应用程序等）。问题是更新这些状态的应用程序是第三方的，所以我无法更新它；雪上加霜的是，只有一半的组织正在使用该系统，而另一半正在使用未来的替代系统。我确实可以开放访问所使用的两个数据库。

我的计划是通过 Item 表的每个表示上的触发器将带有 ID 和状态的消息添加到 SQL Service Broker 队列。然后，从 SSB 激活过程调用 CLR 过程，通过 NServiceBus 将消息添加到发布队列。

这一切似乎都很顺利，直到我尝试使用 CREATE ASSEMBLY 在 SQL 中注册我的程序集：

Msg 10327, Level 14, State 1, Line 1
Warning: The Microsoft .NET Framework assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

etc. etc. etc.

CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Msg 10301, Level 16, State 1, Line 2
Assembly 'log4net' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Msg 6218, Level 16, State 3, Line 3
CREATE ASSEMBLY for assembly 'NServiceBus.Core' failed because assembly 'NServiceBus.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
Msg 6218, Level 16, State 3, Line 4
CREATE ASSEMBLY for assembly 'NServiceBus' failed because assembly 'NServiceBus' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

首先，我想知道这是否是一个完全疯狂的想法。我希望消息按顺序处理并异步添加到队列中。 NServiceBus 提供了一个主机应用程序，可以在需要时轻松启动新的发布者。替代方案似乎是编写一个监视 SSB 队列然后发布的 Windows 服务。我只是想要一些意见或建议。谢谢。

原文

My requirement is that I have "items" with different states. I need to do all kinds of different things based on the states of these items (send emails, store files, update internal applications, etc). The problem is that the application that updates these statuses is third party, so I can't update it; furthering the difficulty is the fact that only half of the organization is using this system and the other are using it's future replacement. I do have open access to both of the databases used.

My plan is to add a message to a SQL Service Broker Queue with and ID and the status via a trigger on each representation of the Item table. Then, call a CLR procedure from the SSB activation procedure that adds a message to a publish queue via NServiceBus.

This all seemed to be going well until I tried to register my assemblies in SQL with CREATE ASSEMBLY:

Msg 10327, Level 14, State 1, Line 1
Warning: The Microsoft .NET Framework assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

etc. etc. etc.

CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Msg 10301, Level 16, State 1, Line 2
Assembly 'log4net' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Msg 6218, Level 16, State 3, Line 3
CREATE ASSEMBLY for assembly 'NServiceBus.Core' failed because assembly 'NServiceBus.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
Msg 6218, Level 16, State 3, Line 4
CREATE ASSEMBLY for assembly 'NServiceBus' failed because assembly 'NServiceBus' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

First, I want to know if this is a completely crazy idea. I want the messages processed in order and added to the queues asynchronously. NServiceBus provides a host application that makes it very easy to spin up a new publishers when needed. The alternative seems to be to write a windows service that monitors the SSB queue and then publish. I'd just like some opinions or advice. Thanks.

分享到QQ

分享到微博