SQLCLR 触发器与 Windows 服务。什么时候适合使用SQLCLR？
我们有一个带有 SQL Server 2005 后端的 .NET 电子商务应用程序。新订单需要一定的“后处理”。这些任务包括发送电子邮件、创建文件、将文件上传到 FTP 服务器以及针对 WCF 数据服务执行 CRUD 操作。执行所有这些任务的代码已作为多个 .NET 类库就位。
我的团队正在进行的争论是将该代码放置在哪里。我编写了一个简单的 Windows 服务，它定期轮询数据库，并在检测到数据库中的新事务（基于标志）时，它会执行必要的操作并记录任何错误。已提出的替代方案是 SQLCLR INSERT 触发器，它将启动处理。
我知道在 SQLCLR 中完成上述大部分（全部？）任务在技术上是可行的 — 我什至发现了许多文章解释如何从 SQLCLR 中使用 Web 服务，所以显然人们正在这样做。但我还在犹豫。 SQLCLR 曾经用于这种事情吗？如果不是，实际的缺点是什么？至于 SQLCLR 触发器相对于 Windows 服务的潜在好处，我只能看到一个：更少的数据库流量。我们预计最初的交易量非常小，因此 Windows 服务将产生一些“浪费”的流量。但该服务与数据库位于同一台机器上，因此它甚至不会影响网络，只会影响内部服务器资源。
最后，第三种可能性是使用 SQLCLR 触发器在文件系统上保存一个简单的令牌，并使用 Windows 服务中的 FileSystemWatcher（而不是 Timer）来根据需要执行任务。
请分享您对这些不同方法的权衡的想法或提出更好的替代方案。
We have a .NET e-commerce application with a SQL Server 2005 back-end. A new order requires certain "post-processing". These tasks include sending emails, creating files, uploading files to an FTP server, and performing CRUD operations against a WCF data service. The code to perform all these tasks is already in place as several .NET class libraries.
The debate that my team is having is where to place this code. I've written a simple Windows service that polls the db at regular intervals and upon detecting new transactions (based on flags) in the db, it does the necessary thing and logs any errors. An alternative that has been proposed is a SQLCLR INSERT trigger that will set off the processing.
I know it's technically possible to do much (all?) of the above tasks in SQLCLR -- I've even found a number of articles explaining how to consume web services from within SQLCLR, so apparently people are doing this. But I am still hesitant. Was SQLCLR ever intended for this kind of thing? And if not, what could be the practical downside? As for the potential benefits of a SQLCLR trigger over a windows service, I can only see one: less db traffic. We expect very light transaction volume initially, so the windows service will generate some "wasteful" traffic. But the service is on the same box as the database, so it doesn't even affect the network, only internal server resources.
Finally, a third possibility would be to use a SQLCLR trigger to save a simple token on the file system, and use FileSystemWatcher in the Windows service (instead of Timer) to perform the tasks as needed.
Please share your thoughts on the trade-offs of these different approaches or suggest a better alternative.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在查看类似的流程时，我们考虑了一些事情。
我们选择使用 Windows 服务，因为它为那些支持系统的人（那些愿意花更多时间在服务器故障而不是 stackoverflow 上的人）提供了最佳体验。因为它有一个既定的方法来停止和启动服务、监控、集群支持、轻松地将处理拆分到多台机器、与事件日志集成等。
When looking at a similar process here were some things we considered.
We choose to use a Windows Service because it offered the best experience to those that would be supporting the system (people who would spend more time on serverfault instead of stackoverflow). Since it had an established way to stop and start the service, monitoring, cluster support, easy to split processing to multiple machines, integration with event logging, etc.
SQL CLR 是为某些事情而设计的 - 但这不是其中之一，将此类内容放入 INSERT 触发器中确实会让小耶稣哭泣。
一个潜在的解决方案是使用查询通知来减轻服务和数据库之间的负载 - 可以采取另一种方法，允许通过 UDP 或类似的轻量级方式“ping”服务 - 这意味着您“不会导致 INSERTS 上发生大量事件（因此您不会长时间锁定基础表）。
There are some things that the SQL CLR was designed for - and this isn't one of them, putting that sort of stuff in an INSERT trigger really would make baby Jesus cry.
One potential solution would be to use Query Notification in order to lighten the load between the service and the database - another route could be taken by allowing the service to be 'pinged' via UDP or something similarly light-weight - meaning that you aren't causing masses to happen on INSERTS (and you are therefore not locking the underlying table for excessive amounts of time).