程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server數據庫引擎.NET CLR環境數據庫管理員向導（2）

SQL Server數據庫引擎.NET CLR環境數據庫管理員向導（2）

編輯：關於SqlServer

目錄對象

一個好的數據庫管理員的主要任務就是跟蹤服務器上的對象，例如那些表在哪些數據庫中，哪些存儲過程和函數是被要求的，哪些腳本每日，每周，每月一定要運行等等。為了幫助針對所有這些對象的跟蹤，我們有很多的工具，他們有來自Microsoft的也有其他的軟件生產廠商的，這其中就包括針對數據模型的Microsoft® Visio®和針對數據庫的Microsoft SQL Server Management Studio 等等。

面對外部世界SQL Server 2005的整合功能引入了很多新的對象來對相關系統目錄的早期版本進行補充和擴展。這部分文字介紹了如何利用改變的編錄去獲得這些新數據庫對象的正確列表，以及數據庫中每個新數據庫對象類型附加的新的源數據。

程序集

-- SYS.ASSEMBLIES
-- Name, Assembly ID, security and “is_visible” flag
SELECT * FROM sys.assemblIEs
-- SYS.ASSEMBLY_FILES
-- Assembly ID, name of each file & assembly contents
SELECT * FROM sys.assembly_files
-- SYS.ASSEMBLY_MODULES
-- Sql ObjectID, Assembly ID, name & assembly method
SELECT * FROM sys.assembly_modules
-- SYS.ASSEMBLY_REFERENCES
-- Links between assemblIEs on Assembly ID
SELECT * FROM sys.assembly_references
-- SYS.MODULE_ASSEMBLY_USAGES
-- Partial duplicate of SYS.ASSEMBLY_MODULES
-- Links SQL Object ID to an Assembly ID
SELECT * FROM sys.module_assembly_usages

這些視圖被定義為系統目錄視圖為這些新的程序集的容器提供信息。這些程序集（或者.dll文件）是數據庫中.Net Framework 程序編輯和注冊的結果。sys.assemblIEs 的“is visible”標記可以在使用公共方法注冊時隱藏程序集。它可以隱藏例如利用網絡服務訪問數據時.XMLSerializers產生的幫助程序集。

在sys.assembly_modules視圖中，一個空程序集方法顯示了一個被用於用戶定義聚合的程序集。需要注意的是有兩個系統存儲過程對這些表提供訪問。如下圖。

EXEC sys.sp_assemblIEs_rowset N'<AssemblyName>'
EXEC sys.sp_assembly_dependencIEs_rowset <AssemblyID>

存儲過程

-- Read CLR Stored Procedure Metadata
SELECT schema_name(sp.schema_id) + '.' + sp.[name] AS [Name]
, sp.create_date
, sp.modify_date
, sa.permission_set_desc AS [Access]
, sp.is_auto_executed
FROM sys.procedures AS sp
INNER JOIN sys.module_assembly_usages AS sau
ON sp.object_id = sau.object_id
INNER JOIN sys.assemblIEs AS sa
ON sau.assembly_id = sa.assembly_id
WHERE sp.type_desc = N'CLR_STORED_PROCEDURE'

觸發器

-- CLR Trigger Metadata
-- Note that Trigger parent object is always in the same schema
SELECT schema_name(so.schema_id) + '.' + tr.[name] AS [Name]
, schema_name(so.schema_id) + '.' + object_name(tr.parent_id) AS 　[Parent]
, te.type_desc AS [Fired On]
, te.is_first
, te.is_last
, tr.create_date
, tr.modify_date
, sa.permission_set_desc AS [Access]
, tr.is_disabled
, tr.is_not_for_replication
, tr.is_instead_of_trigger
FROM sys.triggers AS tr
INNER JOIN sys.objects AS so
ON tr.[object_id] = so.[object_id]
INNER JOIN sys.trigger_events AS te
ON tr.[object_id] = te.[object_id]
INNER JOIN sys.module_assembly_usages AS mau
ON tr.object_id = mau.object_id
INNER JOIN sys.assemblIEs AS sa
ON mau.assembly_id = sa.assembly_id
WHERE tr.type_desc = N'CLR_TRIGGER'

用戶定義函數標量函數

-- List CLR Scalar Functions
SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name]
, so.create_date, so.modify_date
, sa.permission_set_desc AS [Access]
FROM sys.objects AS so
INNER JOIN sys.module_assembly_usages AS sau
ON so.object_id = sau.object_id
INNER JOIN sys.assemblIEs AS sa
ON sau.assembly_id = sa.assembly_id
WHERE so.type_desc = N'CLR_SCALAR_FUNCTION'

表值函數

-- List CLR Table Functions
SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name]
, so.create_date, so.modify_date
, sa.permission_set_desc AS [Access]
FROM sys.objects AS so
INNER JOIN sys.module_assembly_usages AS sau
ON so.object_id = sau.object_id
INNER JOIN sys.assemblIEs AS sa
ON sau.assembly_id = sa.assembly_id
WHERE so.type_desc = N'CLR_TABLE_VALUED_FUNCTION'

用戶定義聚合

-- List CLR Aggregate Functions
SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name]
, so.create_date
, so.modify_date
, sa.permission_set_desc AS [Access]
FROM sys.objects AS so
INNER JOIN sys.module_assembly_usages AS mau
ON so.object_id = mau.object_id
INNER JOIN sys.assemblIEs AS sa
ON mau.assembly_id = sa.assembly_id
WHERE so.type_desc = N'AGGREGATE_FUNCTION'

用戶定義類型-- User Defined Type Metadata
-- Includes base assembly information
SELECT st.[name] AS [Type Name]
, st.max_length
, st.[precision]
, st.scale
, st.collation_name
, st.is_nullable
, sa.create_date
, sa.[name] AS [Assembly Name]
, sa.permission_set_desc AS [Access]
FROM sys.types AS st
INNER JOIN sys.type_assembly_usages AS tau
ON st.user_type_id = tau.user_type_id
INNER JOIN sys.assemblIEs AS sa
ON tau.assembly_id = sa.assembly_id

安全維護

在整合環境的獨特安全模式下存在一個潛在的缺點，因為設計上的缺陷使得一方可能危及另一方的安全。例如，一個擴展存儲過程必須回連到SQL Server 去訪問數據並且必須支持活動的事務去保護修改數據的完整性。任何的數據失敗都會導致安全漏洞。

數據庫引擎.NET Framework 設計API的架構在安全模式方面是非常注意的。SQL Server 在.Net Framework 代碼訪問安全方面使用GRANT命令來提供針對特殊對象訪問，並且控制在高/低權限模式之間的交互。

一個非管理用戶在他們能被使用之前必須獲得對日常事務處理的執行權限。

單一化處理代碼訪問安全模塊（定義程序集允許做的事情）。組的權限分為下面三個設置：

SAFE

程序集方法只能和Transact-SQL的存儲過程或函數同級別。代碼要運行在信任調用下。

EXTERNAL Access

程序集方法可以執行文件和網絡的輸入/輸出。外部代碼使用SQL Server 服務帳號權限運行，這樣的設置可以在控制用戶輸入的特定外部資源時確定調用者的權限。

UNSAFE

這個權限延展了外部權限的設置，使得程序集允許不可管理的調用，代碼不可控。注意在UNSAFE模式下，整合代碼將阻止一部分對.Net Framework 的庫使用。例如，編譯器，Windows 管理器，企業服務器，和其他一些由於SQL Server 無法發現而被阻止掉的功能。

我們推薦數據庫管理員限制External/Unsafe模式的使用，如果開發者要求這些高級別的權限時管理員應該去選擇更加嚴格的環境要求。

源代碼管理

CREATE ASSEMBLY在SQL Server 數據庫中加載二進制程序集，但沒有辦法同時加載程序源。因為他是為了數據庫管理員創建程序集來管理源代碼用的。就好像第三方軟件只支持二進制代碼，也可以為他們的軟件提供專業的技術支持。無論如何，在你的代碼發生問題之前讓你的數據庫管理員可以管理源代碼是一件非常重要的事情。

在這篇文章的設計范例部分包括推薦的為應用程序開發者提供的工具。根據這些推薦的內容，數據庫管理員對於改變控制策略，程序，以及對開發/測試系統和那些正在運行程序的系統的正確描述這些方面會變的非常的容易，使用SQL Server 2005新的安全特性和DDL觸發器可以在生產環境中限制訪問和支配（至少是審核）來更好的執行控制策略。如果希望，DDL觸發器可以阻止一個數據庫中所有的DDL（數據定義語言）聲明，或者特殊的某個或者某組聲明。

注意沒有經過完全的檢查的代碼是不會從開發階段送到生產階段的，我們一定要確保代碼萬無一失。

盡管如此，我們依然推薦開發者執行Visual Studio的自動部署特性利用最大的生產壓力在代碼開發周期中來進行測試，在完成後開發者應該將源代碼和編譯文件傳給負責部署的數據庫管理員。

數據庫管理員應該將源文件做好備份，然後將檢查後的代碼和編譯後的文件保存在另外的一個位置，這樣可以對代碼作一個很好的保護。

我們同樣建議副本應該在每台它加載程序集的數據庫上進行關聯。這意味著在開發代碼目錄上和多個數據庫管理員目錄上執行的是單一副本。如下圖，在數據庫利用CREATE ASSEMBLY命令加載了程序集後所有的支持文件例如源代碼，文檔都通過ALTER ASSEMBLY命令加載。

ALTER ASSEMBLY LeastSquaresSqlFunction
ADD FILE FROM 'D:Production SystemSource CodeLeast SquaresLS1.cs';

◆Productivity Tip

注意 ALTER ASSEMBLY命令支持在單一語句中加載多個文件。

◆數據庫管理員和開發者都應該在檢查完代碼後作出一個審核日值改變的注釋。

◆WinDiff 工具(或者 Visual SourceSafe Compare) 是一個很好的代碼比較的工具，詳細信息請查閱：WinDiffhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tools/tools/windiff.ASP.

◆Productivity Tip

當Visual Studio 使用SQL Server Database Engine .Net Framework 編程 API 編譯應用程序時，編譯輸出面板包括所需的編譯器命令行工具。他可以被減切並且復制到一個發布命令文件，數據庫管理員可以用它重新編譯應用程序。

Release 管理

在源代碼在專業的控制保護下，開發者提交了源文件和編譯文件。這時release管理器在代碼和架構配置不當的情況下就有了很大的作用。如果希望了解它為什麼如此重要，可以關注這篇文章下面有關SQL Server 2005 的部分。

SQL Server 2005 的工具設置使得數據庫管理員和開發者可以在一個公用源代碼庫上協同工作。Microsoft SQL Server Management Studio 可以從Microsoft Visual SourceSafe或者其他地方加載和保存源控制應用的腳本。◆Productivity Tip

.一個服務器運行多個SQL Server 實例處理的時候，每個CLR代碼在列表中都不顯著，這時，應該將某一個實例連接到CLR，然後通過CPU的活動型來判別。

計數器有很多的設置幫助我們了解程序在SQL上運行時的健康性和活動型。

◆.Net CLR Exceptions –Exceptions/Sec 提供了一個對應用程序代碼的早期警告，如果這個值比平時高（這個設置也可以用在正常的功能上），應用程序可能會失敗。更深入的報告要去看錯誤日志。

◆.Net CLR Loading – SQL Server 使用AppDomain在數據庫之間隔離代碼。一個CLR 為程序提供了一個獨立運行時。這個設置可以檢測AppDomains和系統上加載的程序集的數量。這個值太大意味著“procedure cache”的壓力過大。

◆.Net CLR Memory –提供了三種CLR內存的詳細信息。這個計數器還可以檢測CLR內存的使用情況，可以設置警告值。提示代碼的問題，從而使代碼使用較低的內存重新設計。

.Net Data Provider for SQL Server –提供了每秒的連接數和非連接數，在獲取數據庫級別的活動性方面非常有用。如下圖這個計數器是數據庫級別的。

除了服務監控計數器，我們在SQL Server Dynamic Management VIEws (DMV)中也可以得到大量SQL Server操作系統的數據的信息。下面是一些有用的查詢。

-- Loaded AssemblIEs (run in each database)
SELECT sa.[name]
, ad.[appdomain_name]
, clr.[load_time]
FROM sys.dm_clr_loaded_assemblIEs AS clr
INNER JOIN sys.assemblIEs AS sa
ON clr.assembly_id = sa.assembly_id
INNER JOIN sys.dm_clr_appdomains AS ad
ON clr.appdomain_address = ad.appdomain_address
-- SQL CLR Memory Usage
SELECT mo.[type]
, sum(mo.pages_allocated_count * mo.page_size_in_bytes/1024)
AS N'Current KB'
, sum(mo.max_pages_allocated_count * mo.page_size_in_bytes/1024)
AS N'Max KB'
FROM sys.dm_os_memory_objects AS mo
WHERE mo.[type] LIKE '%clr%'
GROUP BY mo.[type]
ORDER BY mo.[type]
-- SQL CLR Wait Statistics
SELECT ws.*
FROM sys.dm_os_wait_stats AS ws
WHERE ws.wait_type LIKE '%clr%'
-- Requests that are currently in SQL CLR
SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, last_wait_type, wait_resource, cpu_time,
total_elapsed_time, nest_level, executing_managed_code
FROM sys.dm_exec_requests
WHERE executing_managed_code = 1
-- Query performance and time spent in SQL CLR.
SELECT
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*
FROM sys.dm_exec_query_stats AS qs
WHERE qs.total_clr_time > 0
ORDER BY qs.total_clr_time desc
-- Obtaining CLR Execution performance counter values.
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%CLR%'

調試代碼SQL Server 2005 和Visual Studio 2005 是緊密整合的。 The SQL Server 的工具以及商業智能部分是為數據庫管理員設計的，而Visual Studio 的整合開發環境是為專業開發人員設計的。

調試處理針對數據庫管理員和開發人員的功能分割是不同的：

◆數據庫管理員使用SQL Server Profiler測試跟蹤數據庫的活動性。

圖 8

◆開發人員使用Visual Studio debugger去跟蹤代碼執行，在他們自己的SQL Server （開發版）上使用SQL Server Profiler檢測數據庫的活動性。Debugger可以執行客戶端到服務器的端對端的調試。這種模式使得開發過程中的調試變得非常具有生產時效性。

圖 9

這個工具的聯合允許了數據庫管理員能夠集中精力到SQL Server的運行上，了解，優化工作的查詢，也早期SQL Server 版本將比也提供給開發人員一個工具實現快速的應用程序開發。

◆Productivity Tip

數據庫引擎.Net Framework 規劃 API的調試環境並不支持並發調試，推薦開發者准備自己的SQL Server 開發實例來使用Visual Studio debuggers。

排錯

這部分內容給出了數據庫管理員對.Net Framework設計API數據庫引擎的排錯和代碼檢查的一些指導方針。當然這不是一個完整的列表。我們還是推薦所有的開發者和數據庫管理員們應該聚在一起然後制定出符合自己的一個標准。我們以當前流行的Transact-SQL標准作為出發點，來確定一些相對壞的項目的，創建一個好的項目的公認的一些注意點。◆Lack of Set-based Operations (Loops within Loops)

那些對於SQL Server 語法並不熟悉的開發者可能不得不用一些非常復雜的語句來替代像JOIN 或者SELECT等語句。例如一個表的處理需要其他外部表的每行數據時，那麼下面這種處理方式是正確的，具體代碼如下：

getdata()
foreach (datarow in datatable)
getdata()
foreach (datarow in datatable2)
etc

它通常是被一個簡單的INNER JOIN語句所替代。

這個行為在SQL Server Profiler中很容易被發現，因為其內部的循環會在服務器上產生大量的查詢。

◆在CLR中的CPU-Intensive處理

一些復雜的邏輯結構在.Net Framework 語言中要比在Transact-SQL結構中更容易去處理。所以如果SQL Server 不能像中間服務器一樣做一些負載平衡，那麼可能會成為瓶頸。在sys.dm_clr_appdomains表中的一列可以列出有關CLR CPU消耗的檢測數據。

如果按照文章前面的建議進行使用，那麼這點將不是什麼大問題。因此，middle-tIEr的遷移功能將是一個很小的代碼任務。

◆例舉內存中的大的對象

在SQL Server 2000中的Transact-SQL並不提供創建大對象的方法。在小的存儲過程中加入大的IMAGE 或者 N/TEXT參數這一方法的能力可能是最差的。很多的內容都將作為@TABLE 變量或#TEMP 表貯存在TempDB中。然而在SQL Server 2005 的.NET Framework 運行時中就不是這個樣子了，在ADO.Net數據集和用戶自產收集中可以放置大的和潛在的大內存要求。你應該使用系統檢測計數器來檢測代內存的使用情況。因為在從開發到生產的整個過程中我們應該都可以保證代碼流的穩定。

這些解決方案與在middle-tIEr上獲得的是相似的。◆使用分頁來得到內存中的數據子集。

◆確認數據已經提交。

◆移動聚合數據替代具體的細節數據。

◆出錯處理

數據庫引擎.Net Framework 設計API支持不同語言的出錯處理。所以出錯處理是非常容易的，任一個外部操作，例如數據庫，文件系統或者web服務訪問，以及操作的上溢出，下溢出的處理方法都在出錯處理中有預先的安裝。

結合SQLContext.Pipe.ExecuteAndSend()使用RAISERROR命令可以返回SQL Server 的錯誤。注意在SQL Server 2005 中Transact-SQL的出錯處理有了一個很大的改變。開發者應該對他們的代碼使用RAISERRORS這一處理。

提供一個例外比返回錯誤代碼對開發者來說更加有意義。因為開發者可能會忘記標記@@ERROR。

◆不安全的代碼問題

數據庫管理員對於UNSAFE注冊代碼非常難以控制。尤其是在共享實例中。因為在開發者的被允許調用非管理代碼。這是一個未經授權的任務。在其他的代碼安全模式下，SQL Server 只關注那些注冊的程序集和僅授權級別的調用。

Unsafe代碼和不可管代碼在高權限下可以被完全訪問。因此，在部署前應該進行一次全面的代碼檢查。尤其應該注意所有的參數以及SQL Server 上的連接。在部分升級時確認事務的邊界。最後我們應該確任代碼在服務器上不會有錯誤信息提示顯示。

◆用戶自定義類型問題

用戶自定義類型使用時應特別小心。注意他的8-KB的限制，以及他們的其他限制：

◆他們將作為一個自動對象進行讀寫。

◆執行代碼在架構級別使用，所以對於現有類型的改變的潛在花費是很高的。

Beyond SQL Server 2005

SQL Server 2005 的.NET Framework 運行時環境同時裝載了Visual Studio 2005。預期的服務包和熱修補並沒有改變版本，然而，這對於SQL Server 未來的發展並不是一個好的設定。作為.Net Framework 的發展，未來的SQL Server 版本將會轉化到可以使用這些新設備。下面的表可以用於應用程序開發者使用SQL Server 的.Net Framework 測試應用測試。注意如果CLR不可用，查詢將返回一個空的字符串。通常來說，主要的版本都需要做檢查，除非只是為了測試某些特性而發布的版本。

-- Will return the version if the .Net Framework has been used
SELECT p.[value]
FROM sys.dm_clr_propertIEs AS p
WHERE p.[name] = N'version'
-- Will return the version even if the .Net Framework is unused
-- Test the version of the Microsoft .Net Runtime Execution Engine
SELECT lm.product_version
FROM sys.dm_os_loaded_modules AS lm
WHERE lm.[name] LIKE N'%MSCOREE.DLL'

在這個白皮書出版的時候，Windows只能處理一個單一的.Net Framework 運行時環境。雖然這一點在今後的版本中還沒有被確定。但是未來的SQL Server 版本可能有一下特性：

◆要求代碼重編譯 – 確定數據庫管理員和加載到SQL Server 上的源代碼版本是同步的。同時使得重編譯更加簡單。

◆要求代碼修改– 利用新的API的優勢同時為現有的選項設計不當的API增加選項。管理源文件以及API使用的情況。

◆要求多個 .Net Framework運行時在SQL Server “down level” 代碼檢查時加載。它可以在性能開銷方面提供更好的保護。

數據庫管理員要管理他們公司的源代碼，檢測開發API的使用，還要花時間保護代碼的修改。及在需要代碼重寫的情況下保護系統。

概要

這個白皮書從傳統的數據庫管理員的角度探究了SQL Server 2005 .Net 公共語言時的整合。

下面的部分提供了：◆使用這個新的能力提供的對於處理和程序的安全以及成功部署的確定。

◆為數據庫管理員提供了更好的目錄數量管理和動態管理視圖查詢的能力。更簡單的訪問對象列表，了解系統狀態的信息。

◆在調試和跟蹤方面為數據庫管理員提供可以發揮起最大生產力的工具集。

我們關心的問題是能否提出一個新特性並不適應的例舉環境。豐富的SQL Server 2005 CLR整合使得再各種情況都可以很好的滿足人們的要求。

在這篇文章中我們要學習的最重要的就是CLR整合提供了一種新的，強有力的應用程序開發工具包，以使得在許多的場景下生產力被大大提高，並且為許多企業環境提供了新的可選項。