This is my second part of the SQL Server 2005 posts; you can read the <strong>first part</strong> on Hosting the .NET runtime in SQL Server.

In the CLR certain conditions such as out of memory, stack overflow, etc can bring down an app domain (or process), this cannot be allowed in SQL Server 2005 when latter is acting has a host (for the CLR) as it will affect reliability and performance - couple of the key goals for SQL Server. Similarly unconditionally stopping a thread (e.g. via Thread.Abort) can potentially leave some resources in a “hung” state.

So how do we handle this? Well other hosts such as ASP.NET recycle the app domain in such situations which is OK because a web application is disconnected and not “long running”. On the other hand SQL Server can have long running batch jobs and rolling all that information back in mot cases is not an option. Our of memory conditions are pretty hard to handle even if you leave a safety buffer for memory. Since SQL Server manages its own memory and tends to use all available memory to maximise throughput this puts it in an even more difficult position. As a result of you increase the “safety net” to handle out of memory conditions you also increase the chances or getting in those situations.

The good news is that .NET 2.0 (which ships with Whidbey) handles these situations better than ver. 1.x and it notifies SQL Server about repercussions of failing each memory request. It would also facilitate running the GC more aggressively, waiting for existing procedural code to finish before starting to execute newer code and also aborting running threads if needed. In addition, at the CLR level there is also a failure escalation policy that would allow SQL Server to deal with those situations. Using this SQL Server among other things can either abort the thread causing the exception of unload the whole app domain.

If any resources fail then the CLR will unwind the whole stack and if there are any locks on them then unload the whole app domain as locks indicate there is some shared state to synchronise and most likely would not be consistent in that state. If the CLR encounters a stack overflow that is not caught by the application then it will unwind the stack and get rid of that thread.

Going back to the main goals, SQL Server will maintain transactional semantics. If an app domain is recycled then the main reason is probably due to reliability (even if at the expense of performance). As a note, all the class libraries part from the BCL that SQL Server will load have been ensured to clean up memory and other resources after a thread is aborted or an app domain is unloaded.

I will post on security and app domains at a later time.