SQL CLR的一些补充(本文基本上是书中的要点摘录)

之前我在空间里发过一些关于SQL CLR的东西，那个里面在SQL SERVER中部署程序集的时候，如果程序集需要EXTERNAL_ACCESS or UNSAFE的权限的时候，那时候写的做法是要开启相应数据库的TRUSTWORTHY，使用ALTER DATABASE CLRDB SET TRUSTWORTHY ON来开启，这样才能赋与前面说的那两种权限。这个只是一种简单的做法，还有一种更为安全的做法，只是要比开启trustworthy更为复杂。
第二种方法为：
1 在生成程序集文件，发布程序集文件的时候，对程序集进行签名，在visual studio里打开项目的属性页--选择签名--勾选为程序集签名--在选择强名称密钥文件新生成一个密钥文件或者选一个已经存在的，设置好后，生成程序集文件。这样就生成了一个签名的程序集文件DLL。当然也可以通过命令行来编译生成一个签名的程序集文件，类似如下的命令：
C:/>C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/Csc.exe
/reference:C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.Data.dll
/reference:C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.dll /reference:C
:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.Xml.dll /keyfile:c:/assembly
/keypair.snk /out:c:/assembly/GetFileDetails.dll /target:library C:/assembly/Get
FileDetails.cs

2 在部署程序集文件时候，要多执行一些如下的sql语句，就是红色部分的sql语句，先从程序集文件创建一个asymmetric key，再从这个asymmetric key创建一个登录，最后赋与这个登录external access：
USE master
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] =
‘ExternalAccess_Login’)
DROP LOGIN ExternalAccess_Login;
GO
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = ‘ExternalAccess_Key’)
DROP ASYMMETRIC KEY ExternalAccess_Key;
GO
CREATE ASYMMETRIC KEY ExternalAccess_Key
FROM EXECUTABLE FILE = ‘C:/Assembly/GetFileDetails.dll’
GO
CREATE LOGIN ExternalAccess_Login FROM ASYMMETRIC KEY ExternalAccess_Key
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalAccess_Login
GO
---------------------
USE CLRDB
GO
CREATE ASSEMBLY GetFileDetails
FROM ‘C:/Assembly/GetFileDetails.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
这样就能正确的赋与程序集需要的权限了。

=================================================================================
以下再列出一些SQL CLR的相关的管理脚本等等以作速查手册之用：

--开启sqlserver clr
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
--You can check serverwide configuration using the catalog view sys.configurations
SELECT * FROM sys.configurations WHERE name = 'clr enabled'



/*在sqlserver中创建程序集，如果不开启trustworthy，那就需要对程序集文件进行签名，然后使用如下sql语句来创建*/
USE master
go
CREATE ASYMMETRIC KEY ExternalAccess_Key
FROM EXECUTABLE FILE = 'C:/Assembly/GetFileDetails.dll'
GO
CREATE LOGIN ExternalAccess_Login FROM ASYMMETRIC KEY ExternalAccess_Key
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalAccess_Login
GO
CREATE ASSEMBLY GetFileDetails
FROM ‘C:/Assembly/GetFileDetails.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

/*当程序集文件改变之后可以使用alter来更新数据库中创建的程序集*/
ALTER ASSEMBLY GetFileDetails
FROM ‘//MyMachine/Assembly/GetFileDetails.dll’

/*If you decide that an assembly should only be called by another assembly and not from outside, you can
change the visibility of the assembly as follows*/
ALTER ASSEMBLY GetFileDetails
SET VISIBILTY = OFF

/*在删除程序集的时候如果使用NO DEPENDENTS，则要被删除的程序集依赖的程序集将不会被删除,否则包含它依赖的程序集
也会被删*/
DROP ASSEMBLY GetFileDetails WITH NO DEPENDENTS

/*The sys.assemblies view gives you all the registered assemblies in the database*/
SELECT * FROM sys.assemblies

/*all the files associated with the assembly*/
SELECT a.Name AS AssemblyName, f.name AS AssemblyFileName
FROM sys.assembly_files f
JOIN sys.assemblies a
ON a.assembly_id = f.assembly_id

/*information about the assembly, its associated class, the methods in the class,
and the SQL object associated with each assembly*/
SELECT
a.Name AS AssemblyName
,m.Assembly_Class
,m.Assembly_Method
,OBJECT_NAME(um.object_id) AS SQL_Object_Associated
,so.type_desc AS SQL_Object_Type
,u.name AS Execute_As_Principal_Name
FROM sys.assembly_modules m
JOIN sys.assemblies a
ON a.assembly_id = m.assembly_id
LEFT JOIN sys.module_assembly_usages um
ON um.assembly_id = a.assembly_id
LEFT JOIN sys.all_objects so
ON so.object_id = um.object_id
LEFT JOIN sys.sysusers u
ON u.uid = m.Execute_As_Principal_id


/*the CLR stored procedure and other details associated with it*/
SELECT
schema_name(sp.schema_id) + ‘.’ + sp.[name] AS [SPName]
,sp.create_date
,sp.modify_date
,sa.permission_set_desc AS [Access]
FROM sys.procedures AS sp
JOIN sys.module_assembly_usages AS sau
ON sp.object_id = sau.object_id
JOIN sys.assemblies AS sa
ON sau.assembly_id = sa.assembly_id
WHERE sp.type = ‘PC’

/*the CLR trigger’s details*/
SELECT
schema_name(so.schema_id) + ‘.’ + tr.[name] AS [TriggerName]
,schema_name(so.schema_id) + ‘.’ + object_name(tr.parent_id) AS [Parent]
,a.name AS AssemblyName
,te.type_desc AS [Trigger Type]
,te.is_first
,te.is_last
,tr.create_date
,tr.modify_date
,a.permission_set_desc AS Aseembly_Permission
,tr.is_disabled
,tr.is_instead_of_trigger
FROM sys.triggers AS tr
JOIN sys.objects AS so
ON tr.object_id = so.object_id
JOIN sys.trigger_events AS te
ON tr.object_id = te.object_id
JOIN sys.module_assembly_usages AS mau
ON tr.object_id = mau.object_id
JOIN sys.assemblies AS a
ON mau.assembly_id = a.assembly_id
WHERE tr.type_desc = N‘CLR_TRIGGER’


/*the CLR scalar function*/
SELECT
schema_name(so.schema_id) + ‘.’ + so.[name] AS [FunctionName]
,a.name AS AssemblyName
,so.create_date
,so.modify_date
,a.permission_set_desc AS Aseembly_Permission
FROM sys.objects AS so
JOIN sys.module_assembly_usages AS sau
ON so.object_id = sau.object_id
JOIN sys.assemblies AS a
ON sau.assembly_id = a.assembly_id
WHERE so.type_desc = N‘CLR_SCALAR_FUNCTION’

/*all CLR table-valued functions*/
SELECT
schema_name(so.schema_id) + N‘.’ + so.[name] AS [FunctionName]
,a.name AS AssemblyName
,so.create_date, so.modify_date
,a.permission_set_desc AS Aseembly_Permission
FROM sys.objects AS so
JOIN sys.module_assembly_usages AS sau
ON so.object_id = sau.object_id
JOIN sys.assemblies AS a
ON sau.assembly_id = a.assembly_id
WHERE so.type_desc = N‘CLR_TABLE_VALUED_FUNCTION’


/*the CLR user-defined aggregates*/
SELECT
schema_name(so.schema_id) + N‘.’ + so.[name] AS [FunctionName]
,a.name AS AssemblyName
,so.create_date
,so.modify_date
,a.permission_set_desc AS Aseembly_Permission
FROM sys.objects AS so
JOIN sys.module_assembly_usages AS mau
ON so.object_id = mau.object_id
JOIN sys.assemblies AS a
ON mau.assembly_id = a.assembly_id
WHERE so.type_desc = N‘AGGREGATE_FUNCTION’

/*a list of the CLR user-defined types*/
SELECT
st.[name] AS [TypeName]
,a.name AS [AssemblyName]
,a.permission_set_desc AS AssemblyName
,a.create_date AssemblyCreateDate
,st.max_length
,st.[precision]
,st.scale
,st.collation_name
,st.is_nullable
FROM sys.types AS st
JOIN sys.type_assembly_usages AS tau
ON st.user_type_id = tau.user_type_id
JOIN sys.assemblies AS a
ON tau.assembly_id = a.assembly_id


/*information about all the AppDomains currently loaded, including their state*/
SELECT * FROM sys.dm_clr_appdomains

/*all the states an AppDomain has gone through*/
SELECT
Timestamp
,rec.value(‘/Record[1]/AppDomain[1]/@address’, ‘nvarchar(10)’) as Address
,rec.value(‘/Record[1]/AppDomain[1]/@dbId’, ‘int’) as DBID
,d.name AS DatabaseName
,rec.value(‘/Record[1]/AppDomain[1]/@ownerId’, ‘int’) as OwnerID
,u.Name AS AppDomainOwner
,rec.value(‘/Record[1]/AppDomain[1]/@type’, ‘nvarchar(32)’) as AppDomainType
,rec.value(‘/Record[1]/AppDomain[1]/State[1]’, ‘nvarchar(32)’)as AppDomainState
FROM (
SELECT timestamp, cast(record as xml) as rec
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_CLRAPPDOMAIN’
) T
JOIN sys.sysdatabases d
ON d.dbid = rec.value(‘/Record[1]/AppDomain[1]/@dbId’, ‘int’)
JOIN sys.sysusers u
on u.uid = rec.value(‘/Record[1]/AppDomain[1]/@ownerId’, ‘int’)
ORDER BY timestamp DESC


/*how much memory SQL CLR is using, in KB*/
/*The first column, single_pages_kb, is the memory allocated in the SQL buffer pool. The second column,
multi_pages_kb, indicates the memory allocated by the SQL CLR host outside of the buffer pool. The
third column, virtual_memory_committed_kb, indicates the amount of memory allocated by the CLR
directly through bulk allocation (instead of heap allocation) through SQL server.*/
/*Note that you will see a second row with 0 if you don’t have a NUMA (non-uniform memory access)
system. In a NUMA system, each node has its own memory clerk, so in that case you would have to add
the node totals to get the total memory usage.*/
SELECT single_pages_kb + multi_pages_kb + virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'


/*all the currently loaded assemblies*/
SELECT
a.name AS Assembly_Name
,ad.Appdomain_Name
,clr.Load_Time
FROM sys.dm_clr_loaded_assemblies AS clr
JOIN sys.assemblies AS a
ON clr.assembly_id = a.assembly_id
JOIN sys.dm_clr_appdomains AS ad
ON clr.appdomain_address = ad.appdomain_address


/*the current request status in the 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


/*execution count (how many times that query was executed), logical reads, physical reads, time elapsed,
and last execution time on a CLR query*/
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

/*Finding the .NET Framework Version in SQL Server*/
/*The .NET load is a lazy load in SQL Server. It is only loaded when any
CLR assembly is called for the first time.*/
SELECT value AS [.NET FrameWork Version]
FROM sys.dm_clr_properties
WHERE name = 'version'

/*SELECT Product_Version AS [.NET FrameWork Version]
FROM sys.dm_os_loaded_modules
WHERE name LIKE N‘%/MSCOREE.DLL’*/
SELECT Product_Version AS [.NET FrameWork Version]
FROM sys.dm_os_loaded_modules
WHERE name LIKE N'%/MSCOREE.DLL'


最后再列举一些可以用来监视SQL CLR一些性能的Windows System Monitor中的计数器：

.NET CLR Memory: Provides detailed information about the three types of CLR heap memory,
as well as garbage collection. These counters can be used to monitor CLR memory usage and to
flag alerts if the memory used gets too large. If code is copying a lot of data into memory, you
may have to check the code and take a different approach to reduce memory consumption, or
add more memory.

.NET CLR Loading: SQL Server isolates code between databases by using an AppDomain. This
set of counters enables monitoring the number of AppDomains and the number of assemblies
loaded in the system.这个也可以使用dmv来监视查询

.NET CLR Exceptions: The Exceptions/Sec counter provides you with a good idea of how many
exceptions the code is generating. The values vary from application to application because sometimes
developers use exceptions for some functionality, so you should monitor overtime to set
the baseline and go from there.