SQL Server - Using CLR integration to consume a Web Service

Question

There are a few tutorials on the web that describe consuming a Web Service using SQL Server 2005's CLR integration. For the most, the process seems pretty convoluted. I've run into several issues including the need to change my database's trust level, and using the sgen tool to create a static XmlSerializer assembly; and I still haven't gotten it working right... (I'm sure I just need to put a little more time and energy into it)

What are the security, performance, and maintenance implications when going to this type of architecture? This would likely be a fairly heavily-used process, and ease of maintenance is relatively important.

I do have freedom to choose whether to integrate this into SQL Server as a UDF, or have it be a stand alone .NET library for console/Web applications. Is the SQL CLR integration with external assemblies worth the trouble?

Answer 1

I think you have answered your own question, I personally find that anything calling a WebService is more than likley better suited to exist OUTSIDE of SQL Server. The complications, elevated trust levels, and as you mentioned overall convoluted process makes it a hard to document and hard to maintain solution.

Answer 2

The short answer is, no, SQL CLR Integration is probably not worth the trouble.

The longer answer has several points, beginning with programming CLR in the database. It's a fine tool, when used correctly, but it does increase memory consumption and can lead to performance issues if not done correctly. I use it in my database for very specialized functionality, such as adding RegEx ability, but it's used sparingly, with well-tested code to prevent as many issues as possible from cropping up.

A second is, as you pointed out, you've got to modify security, opening up potential risks.

Use a stand alone application to load the data into your server. You'll have more control, less risk and a much easier time of it.