SQL Server 2005 引入 CLR 之後,開發者們熱情地接受了它。
CLR 作爲一個強有力的工具,開發者可在數據庫中利用它調用其他面嚮對象語言編寫而成的功能。
從 DBA 的視角來看,CLR 的引入淡化了編譯型代碼與數據庫代碼的區別。它引發了在部署、管理 CLR 代碼以及安全性上,開發者和 DBA 的角色問題。在某種程度上,它也淡化了在多層架構的系統中,業務邏輯層和數據訪問層的區間。
對許多組織而言,實現 CLR 需要決策有關開發過程和它們涉及到的角色。如果你準備執行 CLR,你需要足夠多的非技術細節信息來印證這些決定。
最後,我將指出如何實現 CLR,討論 CLR 代碼的利弊,以及 CLR 技術給 SQL Server 管理和安全方面帶來的影響。我也將描述哪些情況適合用 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 對迭代、過程、計算類操作更加在行。當然也會有例外的時候。
有時 DBA 與 CLR 打的第一個交道就是有人提議在服務器上啟用 CLR。雖然啟用 CLR 只是在服務的選項中啟用 CLR 配置項這麼簡單,但在啟動它之前你需要考慮很多問題。
將腳本部署到數據庫是 DBA 的傳統職責,DBA 同時也要保證腳本正常工作而且不影響性能或引發服務器上的其它問題。然而,CLR 代碼一般是從 VS 開發環境中直接部署到數據庫中的,而 DBA 一般不使用 VS,這似乎意味著開發人員比 DBA 更適合去部署 CLR 代碼。
開發人員可以編寫編譯過後的代碼在 SQL Server 中像 SQL 代碼一樣運行,DBA 或許會對此感覺不爽,但 DBA 仍然可以控制誰可以加載 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 選項的環境中,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
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;
来源: http://blog.csdn.net/helloword4217/article/details/60330115