October 19, 2007 at 12:53 pm
This is a fairly general question. I'm just starting to use some of the new STUFF that SQL Server 2005 provides over 2000.
What are the potential dangers in loading commercial 3rd party .Net libraries into an instance of SQL Server?
Specifically, I'm loading DynamicPDF (http://www.cete.com/) and using it to convert tiff images stored in our database into PDF documents. By using this product within SQL Server, I can write a CLR User Defined Function that takes a record key and passed back a PDF document as a byte array. It's fast, simple, and has the additional advantage that is works in ASP as well as ASP.Net (don't have to jump through the hoops to expose the .Net library to com for ASP).
I had to use Unsafe permission to load the library (Create Assembly) into SQL Server, however.
Am I heading into dangerous waters?
Thanks.
October 22, 2007 at 5:47 am
"Unsafe" means a lot of things, but the important thing to remember is that the CLR is going to try to run this in-process. So, if the .dll you have crashes, throws an unhandled exception, or (dumb as this sounds) displays a dialog, you have a serious problem that will likel crash your SQL server.
Now that the big warning is out of the way, you can do this relatively safely. Make sure you have done lots of testing, your error handling around the .dll calls is adequate, and once again, you have done lots of testing.
As an alternative, you wrap this .dll in a web service and call to the web service through your CLR stored procedure. It will add overhead because your web service may need to re-connect to the database, there will be SOAP translation layers, etc. Most of this is transparent to you as a developer, but may be a performance issue when you are done (calling to a web service a million times can be a problem). If you do this, however, you can disallow unsafe assemblies and you have divorced the .dll that may be a problem from your SQL Server process. If the .dll crashes, it is just a failed call to a web service and SQL can handle that quite well.
If the web service piece is over your head (nothing personal, I just don't know your .Net programming experience), find the closest .Net programmer to you and discuss it with them.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply