Executing a .NET Managed Assembly from SQL Server 2008 - Pro's, Con's & Recommendations

looking for opinions/recommendations/links for the following scenario im currently facing.

The Platform:

• .NET 4.0 Web Application
• SQL Server 2008

The Task:

• Overhaul a component of the system that performs (fairly) complex mathematical operations based on a specific user activity, and updates numerous tables in the database. A common user activity might be "Bob" decides to post a forum topic. This results in (the end-solution) needing to look at various factors (about the post he did), then after doing some math based on lookup values/ratios as well as other data in the database, inserting some other data as a result of these operations.

The Options:

Ok - so here's what im thinking. Although it would be much easier to do this in C# (LINQ-SQL) it doesnt make much sense as the majority开发者_如何学JAVA of the computations are based on values in the db, and it will get difficult to control/optimize/debug the LINQ over time. Hence, im leaning towards created a managed assembly (C# Class Library) that contains the lookup values (constants) as well as leveraging the math classes in the existing .NET BCL. Basically i'd expose a few methods that can be called by the T-SQL Stored Procedures.

This to me has the following advantages:

1. Simplicity of math. Do complex math in .NET vs complex math in T-SQL. No brainer. =)
2. Abstraction of computatations, configurable "lookup" values and business logic from raw T-SQL.
3. T-SQL only needs to care about the data, simplifying the stored procedures and making it easier to maintain. When it needs to do math it delegates off to the managed assembly.

So, having said that - ive never done this before (call .NET assmembly from T-SQL), and after some googling the best site i could come up with is here, which is useful but outdated.

So - what am i asking? Well, firstly - i need some better references on how to actually do this. "This" being how to call a C# .NET 4 Assembly from within T-SQL Stored Procedures in SQL Server 2008. Secondly, who out there has done this, what problems (if any) did you face?

Realize this may be difficult to provide a "correct answer", so ill try to give it to whoever gives me the answer with a combination of good links and a list of pro's/con's/problems with this implementation.

Cheers!

A good introduction on SQL CLR is Using CLR Integration in SQL Server 2005. SQL Server CLR will only load CLR 2.0, and only a subset of approved libraries, see Supported .NET Framework Libraries. The .Net 3.5 libraries (ie .Linq) are not supported, but can be loaded on your own risk. But you will not succeed in loading your .Net 4 assemblies into SQL Server 2005, 2008 or 2008 R2.

Running managed assembly withing SQL Server isn't too complex, just follow Microsoft samples

But in general this is a bad idea, you are mixing responsibilities and probably trying to optimize prematurely

Just make good clean code, that does the job using no convoluted systems, and the "next guy" will thank you for it