We have an active project in SQL 2014 Standard where SQL CLR was used and there is a need to add an assembly that has a COM dll reference.
Can I please get some feedback on this regarding experiences and what concerns there are for performance and other issues?
Another thought is to use WCF to loosely couple but again I would ask for feedback regarding this.
Thanks in advance for any thoughts and ideas surrounding this.
Answer by srutzky ·
Calling out to a COM-visible assembly does technically work (I have tested it), but it's hard to say anything regarding performance. You would have to test to see if the performance is acceptable. As far as any other issues, you will need to have the assembly that calls the COM assembly marked as
PERMISSION_SET = UNSAFE. That's not exactly a performance consideration, more of a potential security issue, but if you coded it yourself, or trust the source, then should be ok. Just keep in mind that
UNSAFE assemblies allow for updateable static class variables. Given that the app domain the assembly will be loaded into is shared across all sessions executing code within it, the static class variables are shared and can cause race conditions if you aren't super careful. Not saying that any non-
readonly static class variables are even being used, but just raising the visibility of a potential source of odd, unpredictable (and erroneous) behavior.
I have no idea about WCF.