What are the possible problems with CLR integration in Sqlserver

Question

I read an article about using CLR integration in sqlserver and wondered what some of the potential problems might be, if any. My thought used it to validate the potentiality data in an outdated database. an example is the name of a person in a telephone number column.

Edit: I don’t think there are any problems, but that’s not what I see a lot about, and I want to not open worms that can cause problems later. The reason I ask is my DBA, which looked at me as if I was crazy when I asked about it.

Answer 1

CLR integration into SQL Server is itself unstable. As proof, I will point out that a group of system types of CLR data types, such as new geography and geometry, were created in SQL Server 2008. Thus, the CLR was considered safe enough so that new core functionality could be based on it.

In doing so, the CLR introduces a whole new arsenal into SQL programming to shoot in the foot. You can start streams, block IPC communication (events, mutexes, semaphores), connect from the outside and wait for I / O, read / write to memory, call various WinISA APIS and generally behave recklessly and cause damage. Old T-SQL programming required a lot more hacking talent to achieve the same.

Do you consider implementing a new data type that exhibits pleasant, limited behavior, such as validating a regular expression? To succeed. Are you looking for web service requests from the embedded SQL CLR? You have it, and you deserve everything you get!

Rule of thumb: if your assembly will be downloaded and tested without reliable requirements (no EXTERNAL_ACCESS, UNSAFE), then you should be fine. Of course, you can still write while (1) {;} loops in SAFE assemblies, but then it can also be stored in T-SQL proc ...

Answer 2

The main ones that I see:

• Non-database, for example, UNSAFE permissions for registry entries, stop / start, etc.
• Prevents SQL Writing
• Harder to set up, track and tune

It talks about how MS tries to implement “date” and “time” as CLR data types for SQL Server 2005, but it fails ... (Itsak Ben-Gan at a seminar in 2004)

Answer 3

One of the problems is how you get CLR (production) commands to the server. For example, our company has several clients that do not give us access to their SQL servers, but rather as a remote connection via SSMS. Thus, there is no local path from which you can deploy your asset DLL. The solution is to either load the binary into the temp table as a binary blob, or as a 0x -hex line.

Then what happens when you update the / storedproc function? You cannot update some assembly depending on another assembly (I cannot remember if it is only in case the function signature is changed.). I think we always drop all sp / func / assys before loading a new version of any assembly.

Build / help integration is idiotic. When you add a reference to sqlclr, you must have this DLL deployed to SQLS. VS will copy this dll from SQLS to the "obj / sqlclr" directory for this project and use it there. When you then create this project using the TFS build system, you must have this DLL for the build to succeed. (There are workarounds related to the common SQLS server, but ..).

When SQLS deploys production DLLs, VS will not be able to deploy new DLLs. The solution is to reset the production DLLs.

Getting a debugger to work with SQLCLR is a bitch. We most often face the fact that we do not have a domain username (DOMAIN \ username) in the sqladmin group (or somesuch). There is very little information that VS / SQLS tells you what is wrong and why it did not hit the breakpoint.

Then even when you write C #, you end up writing SQL in hard-coded strings. I don't know if some helpers exist to avoid this bullshit, for sqlclr there is no nhibernate / sqlalchemy. This is mainly in SP, which should handle many lines / etc. These functions are less affected.

It also means that if you use nhibernate or something similar in your BL layer, you probably won't be able to reuse it in the sqlclr layer, and you will duplicate classes. Creating objects from SqlReader is very, very, very dislike in 2009. What MS thinks is outside of me, incredible shit.

In general, I think SQLCLR is better than T-SQL, but in some situations it ends up just coding T-SQL SP, because it’s less of a concern.

Answer 4

One of the problems I ran into is that the contextual connection does not behave the way it makes sense (since the related SO question is presented)

Despite this, I think the CLR is excellent.