通过CLR集成在SQL Server中使用MSMQ

在数据库管理和应用程序开发中,消息队列是一个重要的组件,它可以帮助应用程序异步处理任务,提高性能和可靠性。在SQL Server 2005中,虽然可以使用MSMQ进行复制作业,但T-SQL本身并不提供直接访问MSMQ的功能。本文将介绍如何通过CLR集成来填补这一空白。

分析

有以下几种解决方案:

  • 扩展存储过程:这是一种较老的方法,需要编写C++ DLL,其中包含将从T-SQL调用的方法。这种方法通常不容易实现,也不推荐在SQL Server 2005中使用。
  • xp_cmdshell和命令行工具:需要编写命令行工具来发送/接收/窥视队列,并使用xp_cmdshell调用它们。xp_cmdshell默认情况下出于安全原因被禁用(如果恶意用户获得了使用xp_cmdshell的SQL Server登录权限,他们可以轻易地攻击操作系统)。此外,还需要为MSMQ操作编写这些工具。
  • Windows服务和轮询:这是一个奇特但可行的想法。Windows服务(代理)轮询数据库表以发送消息,并监听队列以接收到达的消息,这些消息将被放入另一个表中。这看起来复杂,但也非常强大。
  • CLR程序集:这个解决方案利用了SQL Server 2005的新特性——CLR集成。换句话说,现在存储过程可以用C#、VB.NET等编写。

相信前两种解决方案可以在互联网上找到,所以不会讨论它们。第三种解决方案只是另一个带有ADO.NET的Windows服务。幸运的是,还有最后一个,也是最有趣的解决方案。因此,选择变得容易——CLR程序集。

解决方案

下面将看到的所有代码都在本文的源代码包中,所有的T-SQL脚本都在SqlMSMQRegister.sql文件中。

让看看.NET程序集源代码。这是一个普通的类库项目,包含一个名为SqlMSMQ的类,其中包括三个方法:Send、Peek和Receive,它们分别对应System.Messaging.MessageQueue的Send、Peek和Receive方法。所有方法都接受两个参数:queue(队列路径,例如myhost\private$\queue1)和msg(消息内容)。当然,在Peek和Receive中,它是一个out参数。SqlString是.NET中SQL Server nvarchar类型的等效类型,因此为了简单起见,这个实现假设MSMQ队列中的文本消息。这里最重要的事情是Microsoft.SqlServer.Server.SqlProcedure方法属性——它们标记了哪些方法可以从T-SQL作为存储过程调用。方法非常简单,只是一个try-catch,里面有Send、Peek或Receive。构建程序集后,就有了在SQL Server中所需的一切。

首先,必须在SQL Server实例中启用CLR集成(默认情况下是禁用的)。可以使用SQL Server表面区域配置工具(功能->数据库引擎->CLR集成)或调用sp_configure(必须是'sysadmin'或'serveradmin'):

SQL sp_configure 'clr enable', 1 GO RECONFIGURE GO

SQL Server在内部保存CLR程序集。

CREATE ASSEMBLY语句将程序集上传到SQL Server并创建一个给定名称的数据库对象。

SQL CREATE ASSEMBLY assembly_name AUTHORIZATION role_or_user FROM path_to_dll WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } GO

PERMISSION_SET参数告诉SQL Server在程序集访问资源时应多么严格。SAFE是默认选项,也是最限制性的——具有SAFE权限的程序集执行的代码不能访问外部系统资源,如文件、网络、环境变量或注册表。EXTERNAL_ACCESS选项允许CLR代码访问文件、注册表、网络等资源。UNSAFE选项允许程序集访问SQL Server内外的所有资源,并调用非托管代码。在案例中,必须使用UNSAFE,但在创建SqlMSMQ程序集之前,需要创建SqlMSMQ引用的其他程序集。

SQL Server中注册的系统程序集包括:

  • Microsoft.VisualBasic.dll
  • mscorlib.dll
  • System.Data.dll
  • System.dll
  • System.Xml.dll
  • Microsoft.VisualC.dll
  • CustomMarshallers.dll
  • System.Security.dll
  • System.Web.Services.dll
  • System.Data.SqlXml.dll

在注册PERMISSION_SET = UNSAFE的程序集之前,数据库必须将TRUSTWORTHY选项设置为ON:

SQL ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON GO

缺失的System.Messaging程序集可以使用以下代码创建:

SQL CREATE ASSEMBLY Messaging AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH PERMISSION_SET = UNSAFE GO

这可能需要一些时间,因为它还创建了System.Messaging引用的其他程序集。由于这些DLL与System.Messaging.dll在同一个目录中,它们可以由SQL Server自动注册。最终,可以创建SqlMSMQ程序集:

SQL CREATE ASSEMBLY SqlMSMQ AUTHORIZATION dbo FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll' WITH PERMISSION_SET = UNSAFE GO

最后一件事是创建引用SqlMSMQ.dll中方法的存储过程。

SQL CREATE PROCEDURE uspMSMQSend @queue nvarchar(200), @msg nvarchar(MAX) AS EXTERNAL NAME SqlMSMQ.[WJeziorczak.Sql.SqlMSMQ].Send GO
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485