what are the possible problems with CLR integration in Sqlserver

Question

I read an article about using CLR integration in sqlserver and was wondering what some of the potential problems might be, if any. My thought was using it for validating potentiality bad data in a legacy Database. an example is a persons name in the phone number column.

Edit: I don't think there are any problems, but it's not something I see a lot of discussion about, and want to make sure I'm not opening a can worms that's going to cause problems later on. The reason I ask is my DBA looked at me like I was crazy when I asked about it.

Answer 1

One of the problems that I ran into is that the context connection does not behave in a way that makes sense (as the linked question on SO presents)

Notwithstanding, I think the CLR is great.

Answer 2

The main ones that I see:

• Non-database stuff eg UNSAFE rights to do registry writes, stop/start services etc
• Avoids writing SQL
• Harder to tune, trace and tweak

There's a story about MS trying to implement "date" and "time" as CLR datatypes for SQL Server 2005 but failing... (Itzak Ben-Gan at a seminar in 2004)

Answer 3

CLR integration in SQL Server per se is not unstable. As evidence I'll point you to the fact that in SQL Server 2008 a bunch of system data types were implemented as CLR data types, like the new geography and geometry types. So the CLR was deemed safe enough to allow new core functionality to be based on it.

That being said, the CLR brings into the SQL programming a whole new arsenal to shoot yourself in the foot. You can start threads, block on IPC communication (events, mutexes, semaphores), connect externally and wait for I/O, read/write in memory, call various Win32 APISs and in general behave recklessly and wreak havoc. The old T-SQL programming required a much bigger hacking talent to achieve the same.

Are you looking at implementing a new datatype that exposes nice, constrained, behavior like, say, regex validation of a field? Go ahead. Are you looking at making Web service requests from inside SQL hosted CLR? You have it coming and you'll deserve all you'll get!

The rule of thumb is if your assembly will load and validate without trustworthy requirements (no EXTERNAL_ACCESS, no UNSAFE) then you should be OK. Of course, you can still write while(1) {;} loops in SAFE assemblies, but then so could a T-SQL stored proc...

Answer 4

One problem is how do you get the CLR (production) assys onto the server. For example our company has some clients that don't give us access to their SQL servers but as remote connection via SSMS. So no local path from which you can deploy your assy dll. Solution is to either upload the binary to temp table as binary blob or as 0x -hex string.

Then what happens when you update a function/storedproc? You can't update some assembly that is dependent upon by another assembly (I can't remember if it's only if a function signature changes..). I think we always drop all sp/func/assys before we upload new version of any assembly.

Build/reference system integration is idiotic. When you add reference to a sqlclr, you must have that dll deployed to SQLS. VS will copy that dll from SQLS to "obj/sqlclr" -directory for that project and use it from there then. When you then build that project with TFS build system, you must have that dll there for the build to succeed. (There are workarounds involving shared SQLS server, but..).

When SQLS has production dlls deployed, VS will fail to deploy new dlls. Solution is to drop production dlls.

Getting debugger to work with SQLCLR is a bitch. We most often get tripped by not having our domain username (DOMAIN\username) in 'sqladmin' (or somesuch) group. There is very little in the way of advice that VS/SQLS tells you what is wrong and why it fails to hit a breakpoint.

Then even when you're writing C#, you end up writing SQL in hardcoded strings. I don't know if some helpers exist to avoid this bullshit, there is no nhibernate/sqlalchemy for sqlclr. This is mainly in SP's which need to process many rows/etc. Functions are less affected by this.

This also means that if you use nhibernate or something like that in your BL layer, you will probably be unable to reuse it in sqlclr layer and you will duplicate classes. Instantiating objects from SqlReader is very, very, very off-pissing in year 2009. What MS were thinking is beyond me, unbelievable crap.

All in all I think SQLCLR is way better than T-SQL, but in some situations end up just coding up a T-SQL SP because it's less of a bother.