Problems with CLR Code on Sql Server 2005

Question

I am considering accessing some CLR code from a UDF in Sql Server 2005.

I've heard stories that unhandled exceptions thrown in CLR code can bring down the server.

Obviously my function would contain a try-catch block. However, certain exceptions (stackoverflow) can skip the catch.

Does anyone have a set of guidelines that can completely eliminate (or minimize) the risk of my CLR code bringing down the server.

Answer 1

What does the code do?

I suggest you consider avoiding the risk entirely by achieving your goal without using CLR first, rather than using a technology that you know will quite possibly add coupling and complexity, and be completely non-portable. But I can't recommend anything if I don't know that the problem is, or whether you really need CLR.

Answer 2

I would highly recommend supressing the ability of queries which use the CLR functions to go parallel. Your mileage may vary, but I have found this gets rid of some of my SQL engine errors.

http://msdn.microsoft.com/en-us/library/ms181714.aspx

WITH( MAXDOP 1)

Answer 3

Unhandled exceptions have an adverse effect on SQL Server, but just how adverse depends on the severity of the exception that is thrown.

Generally speaking, you use Constrained Execution Regions to indicate the scope (process, app domain, thread, etc, etc) that a piece of your code can impact when it fails. SQL Server uses this to determine whether or not to just abort the current query/request, or to go further in the case of a more severe error.

There is a good article in MSDN magazine that talks about CER, as well as how SQL Server utilizes them:

http://msdn.microsoft.com/en-us/magazine/cc163716.aspx#

Additionally, here is a list of best practices that is geared specifically towards developing CLR code for SQL Server:

http://msdn.microsoft.com/en-us/library/ms228970.aspx#

Answer 4

Uncaught exceptions in CLR code would not bring down the server. CLR code runs in a separate app domain, and is isolated. The most that could possibly happen (and this is unlikely) is that the exception would bring down the app domain, and cause the app domain to be unloaded. This would make it either a) reload on the next request, or b) be disabled till the problem is fixed or a new version of the assembly is deployed.

So at most that particular CLR assembly would get disabled, and calling sessions would get error messages. It's extremely unlikely that faulty CLR code would bring down an entire sql server instance; if it does, I would say that was a microsoft bug, not a bug in the CLR function or stored proc.

The highly likely case is that the uncaught .net exception would be translated into a tsql error message. At most, the tsql connection would be terminated; mostly likely it would result in an error message.