SQL Server 2008: How crash-safe is a CLR Stored Procedure that loads unmanaged libraries

Question

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).

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.

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?

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

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: 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).

Answer 1

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 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.)

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

1. Avoid using the functionality that crashes.
2. Fix the buggy code.
3. Run the code in a separate process (launch an executable, call a Windows service, call a web service, etc.). You can write a managed .NET DLL to perform this interaction. Most likely, you will still need to load it UNSAFE, but--if it is written properly--in reality it can be quite safe.