DBA眼中的CLR (译)

数据库开发, SQL Server
目录

SQL Server 2005引入CLR之后，开发者们热情地接受了它。
CLR作为一个强有力的工具，开发者可在数据库中利用它调用其他面向对象语言编写而成的功能。

从DBA的视角来看，CLR的引入淡化了编译型代码与数据库代码的区别。它引发了在部署、管理CLR代码以及安全性上，开发者和DBA的角色问题。在某种程度上，它也淡化了在多层架构的系统中，业务逻辑层和数据访问层的区间。

对许多组织而言，实现CLR需要决策有关开发过程和它们涉及到的角色。如果你准备执行CLR，你需要足够多的非技术细节信息来印证这些决定。

最后，我将指出如何实现CLR，讨论CLR代码的利弊，以及CLR技术给SQL Server管理和安全方面带来的影响。我也将描述哪些情况适合用CLR，而哪些情况可能不适合。

创建与部署CLR

CLR是一个程序模型，它允许开发者用面向对象语言来写编写代码，然后像T-SQL存储过程、函数或触发器一样去使用它。一般创建过程如下：
1、创建VS工程，代码可以用C#/VB.Net编写；

2、代码被编译后，直接从VS部署到目标数据库。编译后会产生一个DLL文件，DLL是一个包含编译后代码的可运行的文件。部署后，将会在sys.assemblies视图和其他相关视图中插入对应程序集信息，

3、通过对程序集运用EXTERNAL NAME选项，一个或多个T-SQL对象将在数据为中被创建。这些对象对称为原型，一个原型能被定义为存储过程，函数，用户自定义聚集函数或用户自定义类型。原型中不包含代码，它只是一个简单的可被其它数据库代码调用的入口，被调用时，DLL中编译后的代码将会运行。

通常，当你从VS编译和部署时，上述所有步骤都会被执行，但你也可以手动分开执行每一步。

现代的面向对象语言有一些对T-SQL来说是非常困难甚至无法实现的功能。CLR则提供了一种在数据库中运用这些语言丰富特性的方式。对于计算或迭代性的操作，CLR通常提供比T-SQL更优的性能。CLR代码和SQL Server在同一个内存上下文中运行，所以它运行得非常有效率。

CLR代码是被托管的代码，这意味着它在『CLR运行时』中运行时，可以确保不会出现代码运行时使服务器不稳定的情况。尽管这是好事，但任何形式的虚拟化都会有一定程序的开销，CLR如果运用不当还是会影响性能的。尽管编译后的代码在SQL内存空间中会运行得很有效率，但T-SQL仍然是繁重工作的最好选择，如果要访问和返回大批量的数据，CLR并不是一个好的选择。

总之一句话，T-SQL处理集合类操作更加有效率，而CLR对迭代、过程、计算类操作更加在行。当然也会有例外的时候。

管理CLR

有时DBA与CLR打的第一个交道就是有人提议在服务器上启用CLR。虽然启用CLR只是在服务的选项中启用CLR配置项这么简单，但在启动它之前你需要考虑很多问题。

将脚本部署到数据库是DBA的传统职责，DBA同时也要保证脚本正常工作而且不影响性能或引发服务器上的其它问题。然而，CLR代码一般是从VS开发环境中直接部署到数据库中的，而DBA一般不使用VS，这似乎意味著开发人员比DBA更适合去部署CLR代码。

开发人员可以编写编译过后的代码在SQL Server中像SQL代码一样运行，DBA或许会对此感觉不爽，但DBA仍然可以控制谁可以加载CLR程序集，在什么安全级别下运行CLR功能。

CLR安全性

默认的，只有sysadmin、db_ower、ddl_admin角色的用户有权限去运行程序集相关的DDL语句。这个权限可以赋给其他角色和用户。另外，如下三种权限集可以让你设置程序集本身的三种安全级别：

SAFE。此安全级别只允许访问本地数据和内部计算。对系统资源，比如文件、网络、环境变量或者注册表的访问是禁止的。任何有创建程序集权限的用户都可以创建一个SAFE级别的程序集。如果CREATE ASSEMBLY语句没有显示指定安全级别，SAFE将是默认的级别。

EXTERNAL ACCESS。此安全级别允许程序集访问外部资源。拥有EXTERNAL ACCESS权限的用户才可以创建此安全级别的信息集。

UNSAFE。在将程序集设为UNSAFE时请三思，此级别的程序集在SQL Server内外都拥有不受约束的访问权限。在UNSAFE程序集里的代码也可能是不受管理的代码，意味著它有让服务器不稳定的可能。仅sysadmins用户组的用户可以登陆UNSAFE的程序集。

默认的，CLR代码都是在SQL SERVER服务帐号的安全上下文运行，如果你要访问SQL Server之外的资源，这就会有问题，最好的做法是不超过服务帐号的权限。但是CLR代码可以通过模拟其他Windows帐号来运行。UNSAFE和EXTERNAL ACCESS安全级别的程序集有这种能力。可以创建这种程序集的人必须是深受公司信任的。

理解CLR元数据

在一个启用了CLR选项的环境中，DBA应该对如下5个包含了元数据信息的系统视图非常熟悉：

  • sys.assembiles：此视图每行记录对应一个程序集。它包含了程序集的一些属性，比如名称、安全级别和创建日期。
  • sys.assembly_files：对数据库中每一个程序集，这个视图都包含了一行信息说明其源文件、DLL文件。
  • sys.assembly_modules： 一个程序集可能包含多个类，比如存储过程和函数。程序集里的类可能包含多个实现不同功能的代码模块。这个视图将各自独立的代码模块ID与引用它们的数据库对象ID关联在一起了。如下查询可以查出每个CLR数据库对象引用的程序集的类和方法：
SELECT OBJECT_NAME(m.object_id) AS db_object
      ,a.name AS assembly
      ,m.assembly_class
      ,m.assembly_method
FROM sys.assembly_modules m
INNER JOIN sys.assemblies a ON a.assembly_id = m.assembly_id
  • sys.assembly_references：程序集之间的相互引用依赖关系。
  • sys.module_assembly_usages：将程序集的ID与引用它们的数据库对象ID，关系起来了。
SELECT OBJECT_NAME(object_id) AS db_object
      ,a.name AS assembly
FROM sys.module_assembly_usages u 
INNER JOIN sys.assemblies a ON a.assembly_id = u.assembly_id

移除CLR程序集

VS可以很方便创建和部署CLR程序集到SQL Server中，但并没有同样方便的工具去卸载它们。你必须有顺序地手动将它们移动。
在移除某程序集之前可以通过查看依赖项先移动那些引用此程序集的T-Sql对象，可以Sp_Depends或在对象资源管理器中右键查看。

更好的改变

纯T-SQL的数据库编程时代正在发生变化，CLR正是这一迹象的例子。尽管有人会有点排斥，但正是变化让DBA生涯变得有趣。CLR真正突破了T-SQL在某些方面的限制。两种模式的混合编程能力是一个强大的工具，DBA和开发者都应该掌握它们。

附常用查询

-- Assembly Query 
SELECT * FROM sys.assembly_modules;
SELECT * FROM sys.assemblies;
SELECT * FROM sys.assembly_files;

-- CLR Object In Database
SELECT * FROM sys.objects WHERE type_desc LIKE 'CLR%';

-- 哪些系统对象引用了CLR程序集
SELECT OBJECT_NAME(object_id) AS [Object_Name],b.Name AS Assembly_Name
FROM sys.module_assembly_usages a
INNER JOIN sys.assemblies b ON a.assembly_id = b.assembly_id;

原文链接：https://www.itprotoday.com/sql-server/common-language-runtime-dba-point-view