.NET in SQL Server 2005 – Making .net code more reliable

A while back I got interested in the new .net/CLR features in SQL Server 2005 such as .net stored procedures, functions, triggers etc.

First off let me just say that the extent of my knowledge about databases is pretty much limited to “select * from authors” so I have to admit that my interest was triggered mostly by the fact that I wanted to know how we were going to debug itJ

I started off as I usually do by taking a simple sample and then modifying it to the point where it would generate a hang, crash or some insane performance problem. Imagine my disappointment (and of course delight at the same timeJ) when I found out that causing these types of problems was hard or close to impossible…

So why was this? Well, in .net 2.0 a lot of work was put into “hardening” the CLR since it was going to be introduced in a service (SQL Server) that is very sensitive to these kind of problems. If you crash SQL Server you can loose a lot of important data since it is a very state full application.

Crashing an IIS server is bad, but in most cases if you restart it you won’t have lost by far as much information as if you crashed SQL server.

A lot of the reasons why it is so hard to crash or hang SQL Server with .net functions is because of the restrictions that are imposed. 2.0 introduces something called Host Protection Attributes. Specifically there is host protection attributes for SharedState (writing to statics, storing items in cache etc.), Synchronization (locks etc.), MayLeakOnAbort (code that may leak if a thread abort exception occurs), and ExternalProcessManagement (calls to COM components etc.).

All functions in the framework that perform tasks that involve any of the above must be marked with a Host Protection Attribute (HPA), and then the host can allow or disallow code with said HPAs. In the case of SQL Server, the safe and external-access modes don’t allow the above HPAs, so essentially, you can try to shoot yourself in the foot, but you won’t have any bullets.

As you can probably imagine, imposing these limits is not feasible in other applications such as ASP.NET applications or WinForms, so in those you can still shoot yourself in the foot, but it might be interesting to know that it is very hard in SQL Server.

The experiments got me so excited that I decided to create a training class for our customers on SQL CLR and that is when I found the awesome Hands-On-Labs for SQL 2005

If you are interested in new features in SQL 2005 I warmly recommend you to go through those.

Until next time…