Sqlserver中CLR集成可能出现哪些问题
我阅读了一篇有关在 sqlserver 中使用 CLR 集成的文章，并且想知道可能存在哪些潜在问题（如果有）。 我的想法是使用它来验证遗留数据库中潜在的不良数据。 例如，电话号码栏中的人名。
编辑： 我不认为有任何问题，但这不是我看到很多讨论的问题，并且想确保我没有打开罐头蠕虫，这会在以后引起问题。 我问这个问题的原因是当我问这个问题时，我的 DBA 看着我，就像我疯了一样。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
SQL Server 中的 CLR 集成本身并不稳定。 作为证据，我将向您指出这样一个事实：在 SQL Server 2008 中，一堆系统数据类型被实现为 CLR 数据类型，例如新的地理和几何类型。 因此，CLR 被认为足够安全，允许新的核心功能基于它。
话虽这么说，CLR 为 SQL 编程带来了一个全新的武器库，这可能会搬起石头砸自己的脚。 您可以启动线程、阻止 IPC 通信（事件、互斥体、信号量）、外部连接并等待 I/O、在内存中读/写、调用各种 Win32 APIS，并且通常会鲁莽行事并造成严重破坏。 旧的 T-SQL 编程需要更多的黑客才能才能实现同样的目标。
您是否正在考虑实现一种新的数据类型，该数据类型可以公开良好的、受约束的行为，例如字段的正则表达式验证？ 前进。 您是否正在考虑从 SQL 托管的 CLR 内部发出 Web 服务请求？ 你已经得到了它，你将得到你应得的一切！
经验法则是，如果您的程序集将在没有可靠要求的情况下加载和验证（没有 EXTERNAL_ACCESS，没有 UNSAFE），那么您应该没问题。 当然，您仍然可以在 SAFE 程序集中编写 while(1) {;} 循环，但 T-SQL 存储过程也可以...
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...
我看到的主要内容：
有一个关于 MS 试图将“日期”和“时间”实现为的故事SQL Server 2005 的 CLR 数据类型但失败了...（Itzak Ben-Gan 在 2004 年的研讨会上）
The main ones that I see:
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)
一个问题是如何将 CLR（生产）assys 加载到服务器上。 例如，我们公司的一些客户不允许我们访问他们的 SQL 服务器，而是通过 SSMS 进行远程连接。 因此没有可以部署 assy dll 的本地路径。 解决方案是将二进制文件作为二进制 blob 或 0x -hex 字符串上传到临时表。
那么当你更新函数/存储过程时会发生什么？ 您无法更新某个程序集依赖于另一个程序集（我不记得是否仅在函数签名更改时才发生......）。 我认为在上传任何程序集的新版本之前，我们总是删除所有 sp/func/assys。
构建/参考系统集成是愚蠢的。 添加对 sqlclr 的引用时，必须将该 dll 部署到 SQLS。 VS 会将该 dll 从 SQLS 复制到该项目的“obj/sqlclr”目录，然后从那里使用它。 然后，当您使用 TFS 构建系统构建该项目时，您必须拥有该 dll 才能成功构建。 （有涉及共享 SQLS 服务器的解决方法，但是..）。
当 SQLS 部署了生产 dll 时，VS 将无法部署新的 dll。 解决方案是删除生产 dll。
让调试器与 SQLCLR 一起工作是一件很麻烦的事情。 我们最常遇到的问题是我们的域用户名（DOMAIN\username）不在“sqladmin”（或类似的）组中。 VS/SQLS 几乎没有提供什么建议来告诉您出了什么问题以及为什么无法到达断点。
那么，即使您编写 C#，您最终也会在硬编码字符串中编写 SQL。 我不知道是否存在一些帮助程序来避免这种废话，sqlclr 没有 nhibernate/sqlalchemy。 这主要是在需要处理许多行等的 SP 中。 功能受此影响较小。
这也意味着，如果您在 BL 层中使用 nhibernate 或类似的东西，您可能无法在 sqlclr 层中重用它，并且您将重复类。 在 2009 年，从 SqlReader 实例化对象是非常、非常、非常令人讨厌的。MS 的想法超出了我的想象，令人难以置信的废话。
总而言之，我认为 SQLCLR 比 T-SQL 好得多，但在某些情况下最终只是编写 T-SQL SP，因为它不那么麻烦。
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.
我的问题之一遇到的问题是上下文连接的行为方式不合理（正如 SO 上的链接问题所示）
尽管如此，我认为 CLR 很棒。
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.