SQL Server 2008:加载非托管库的 CLR 存储过程有多安全 [英] SQL Server 2008: How crash-safe is a CLR Stored Procedure that loads unmanaged libraries

问题描述

我们在 SQL Server 2000 中有一个调用外部 exe 的常规(即未扩展)存储过程.该 exe 依次加载来自 SDK 的 .dll 并从中调用一些过程(即 Init、DoStuff、Shutdown).

We've got a regular (i.e. not extended) stored procedure in SQL Server 2000 that calls an external exe. That exe, in turn, loads a .dll that came from an SDK and calls some procedures from it (i.e. Init, DoStuff, Shutdown).

我们有这个外部 exe 东西的唯一原因是因为我们不想创建一个会调用 .dll 的扩展存储过程.我们相信，如果 dll 崩溃(不太可能发生但仍然如此)，那么 SQL Server 进程也会崩溃，这不是我们想要的.使用外部 exe，只有那个 exe 会崩溃.

The only reason we have this external exe thing is because we didn't want to create an extended stored procedure that would call the .dll. We believed that if the dll crashes (an unlikely event but still) then the SQL Server process will crash as well which is not what we wanted. With an external exe, only that exe would crash.

现在，我们正在升级到 SQL Server 2008 并考虑创建一个 CLR 存储过程来调用事物并因此摆脱 exe.当然，这个 SP 将被标记为 UNSAFE.因此，问题是，与扩展的 SP 方法相比，这样做是否安全(更安全、足够安全等)?

Now, we're upgrading to SQL Server 2008 and considering creating a CLR stored procedure that calls the thing and therefore getting rid of the exe. This SP would be marked as UNSAFE, of course. The question therefor is, is it safe (safer, safe enough etc.) to do it that way as compared to the extended SP approach?

我在 BOL 上找到的唯一相关内容是:

The only relevant thing I've hunted down on BOL is:

指定 UNSAFE 允许代码在集会执行非法针对 SQL Server 的操作进程空间，因此可以可能会损害稳健性SQL Server 的可扩展性和可扩展性

Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space, and hence can potentially compromise the robustness and scalability of SQL Server

，但我不确定它是否能回答我的问题，因为我不追求稳健性和可扩展性"，而是追求稳定性和保持设备正常运行.

, but I'm not sure whether it answers my question as I'm not after 'robustness and scalability', rather after stability and keeping the thing up and running.

PS:我们想去掉 exe，因为它会在管理 SP 权限时造成不便(你知道，如果你调用一个包含 xp_cmdshell 的 SP，那些东西突然适用于你).

PS: We want to get rid of the exe because it causes inconviniences when managing SP permissions (you know, that stuff that suddenly applies to you if you call a SP that contains xp_cmdshell).

推荐答案

由于此代码最初用于扩展存储过程，因此听起来像是非托管代码.非托管代码中的错误很容易使您的进程崩溃.

Since this code was originally used with extended stored procedures, it sounds like it is unmanaged code. Bugs in unmanaged code can easily crash your process.

CLR 集成比扩展存储过程强得多，但代码仍在进程内运行，因此错误可能会导致 SQL Server 中断或损坏.(相比之下，理论上，SAFE CLR 例程将无法破坏 SQL Server，尽管它可能会导致降低服务器可用性的问题，但不会完全关闭 SQL Server.)

CLR integration is much more robust than extended stored procedures, but the code still runs in-process, so errors can take down or corrupt SQL Server. (For comparison, in theory, a SAFE CLR routine won't be able to corrupt SQL Server although even it could cause problems that reduce your server's availability without totally taking down the SQL Server.)

基本上，在这种情况下不使 SQL Server 崩溃的唯一方法是:

Basically, the only ways to not crash SQL Server in this scenario are:

1. 避免使用会崩溃的功能.
2. 修复有问题的代码.
3. 在单独的进程中运行代码(启动可执行文件、调用 Windows 服务、调用 Web 服务等).您可以编写托管 .NET DLL 来执行此交互.很可能，您仍然需要将其加载为 UNSAFE，但是——如果它编写得当——实际上它是非常安全的.

这篇关于SQL Server 2008:加载非托管库的 CLR 存储过程有多安全的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！