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 have been doing clr procedures which calls webservices both on Exchange and AD and I agree to the posts above. It works, but we quickly ran into out-of-memory issues because of the special way memory is handled in CLR inside sql server. As you can imagine performance is ok for small queries but does not scale at all.

Generally your database performance determines the performance of your application and I think putting such logic in your database is a no-no if you don't have completely control over what you are doing.

Use CLR for simple text manipulations and other calculations that does not depend on external resources.

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.

Answer 3

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.