什么是 SQL Server CLR 集成生命周期? [英] What is the SQL Server CLR Integration Life Cycle?

问题描述

如何在 SQL Server 中管理 CLR (.NET) 对象?

How are CLR (.NET) objects managed in SQL Server?

来自 SQL Server 的任何 CLR 代码的入口点都是静态方法.通常，您只会创建存在于该方法范围内的对象.但是，您可以想象在静态成员中存储对对象的引用，让它们脱离方法调用范围.如果 SQL Server 跨多个存储过程/函数调用将这些对象保留在内存中，那么它们可能对缓存应用程序很有用——尽管它们也会更危险.

The entry point to any CLR code from SQL Server is a static method. Typically you'll only create objects that exist within the scope of that method. However, you could conceivably store references to objects in static members, letting them escape the method call scope. If SQL Server retains these objects in memory across multiple stored procedure/function calls, then they could be useful for caching applications -- although they'd be more dangerous too.

SQL Server 如何处理这个?它甚至允许(非方法)静态成员吗​​?如果是这样，它会将它们保留在内存中多久?每次 CLR 调用后它是否会垃圾收集所有内容?它如何处理并发?

How does SQL Server treat this? Does it even allow (non-method) static members? If so, how long does it retain them in memory? Does it garbage collect everything after every CLR call? How does it handle concurrency?

推荐答案

在 Robin Dewson 和 Julian Skinner 的Pro SQL Server 2005 程序集"中，它说加载到数据库中的程序集，像其他数据库对象一样，由一个数据库用户.同一数据库中同一用户拥有的所有程序集将在同一个 AppDomain 中运行.不同用户拥有的程序集将在单独的 AppDomain 中运行."

In "Pro SQL Server 2005 Assemblies" by Robin Dewson and Julian Skinner, it says that "Assemblies loaded into a database, like other database objects, are owned by a database user. All assemblies owned by the same user in the same database will run within the same AppDomain. Assemblies owned by a different user will run within a separate AppDomain."

这告诉我，如果您使用的是单个数据库，并且您使用 CREATE ASSEMBLY 语句加载的所有程序集都具有相同的所有者，那么您的程序集将全部运行在同一个应用程序域中.但是，在同一个AppDomain中并不意味着使用相同的代码库，因此即使是同一个dll也可以多次加载到同一个应用程序域中，并且即使它们具有相同的名称，它们的类型也不会匹配.当同名类型来自不同的代码库时，它们的静态变量也会是不同的实例.

What this tells me is that if you're working with a single database and all the assemblies you load in with the CREATE ASSEMBLY statement have the same owner, then your assemblies will all run in the same app domain. However, being in the same AppDomain does not mean using the same code-base, so even the same dll can be loaded into the same application domains multiple times, and it's types will not match up, even though they have the same name. When same-named types are from different code-bases, their static variables will be different instances as well.

我认为在具有多个程序集的 SQL Server CLR 环境中安全使用静态变量的唯一方法是实际上只使用一个程序集.您可以使用带有UnionMerge"选项的 ILMerge 实用程序将所有程序集打包成一个并合并具有相同名称的类.这应该保证对于给定的数据库，在您的唯一程序集中，您的静态变量将像在独立应用程序中一样工作.我认为假设应用程序域不会在每次请求时卸载和重新加载是安全的，但你不能依赖它永远不会被卸载，因为每当出现未处理的错误时就会发生这种情况(至少如果它在不安全模式下运行)).

The only way I can see to use static variables safely in SQL Server CLR environment with multiple assemblies is to actually just use a single assembly. You can use the ILMerge utility with the "UnionMerge" option to pack all your assemblies into one and merge classes with the same name. This should guarantee that for a given database, in your sole assembly, your static variables will work just like they would in a stand-alone application. I think it's safe to assume the application domain isn't unloaded and reloaded at every request, but you can't depend on it never being unloaded, since that will happen whenever there is an unhandled error (at least if it's running in unsafe mode).

这篇关于什么是 SQL Server CLR 集成生命周期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！