Prior to SQL Server 2005, the developers use their complex logics in COM Objects and call that COM objects with OLE Automation in SQL Server (using sp_OA*) Extended Stored Procedure. In SQL Server 2005, the error handling introduced in with TRY..CATCH block but it is still susceptible to untrappable errors, where we can handle these with structure error handling methods available in .NET languages. (http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLCLRforDBAs.doc)
More advantages over using SQL CLR integration instead of XPROCs are
- As CLR requests memory from SQL Server not directly from Windows, there is no managed user-code memory leaks making SQL Server slow or hanging up.
- As CLR and SQL Server integrated within, the CLR code runs within SQL Server also gaining safe and secure environment by SQL Security and .NET Framework environment’s security.
- This also makes safe SQL Server from user-code access violation cause crashes.
This CLR integration of Complex logics in Data layer itself reducing high cost of network traffic of high marshaling of data to COM Server (in case of Distributed COMs are in use)
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Once you configured your server to enable this feature, now your .NET code can be integrated with SQL Server.
Assemblies
Managed code is compiled and deployed in units called Assembly. If you create a package in .NET language it will be either .exe or .dll. SQL Server supports .DLL assemblies. First you have to register an assembly using CREATE ASSEMBLY in SQL Server before its functionality is used or processed.
Create Assembly TSQL Statement will register an assembly in SQL Server. Using WITH PERMISSION_SET keyword, you can specify security permission of the assembly. Permission set may be SAFE, EXTERNAL_ACCESS, or UNSAFE. Default permission set is SAFE.
Books Online is saying
“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “
After an assembly is registered in SQL Server, It can be used.
To monitor and manage CLR objects, we can use CLR Catalog views , CLR related DMVs and DMFs.
Catalog Views
SYS.ASSEMBLIES Catalog view returns each row per assembly registered in SQL Server.
SYS.ASSEMBLY_FILES Catalog view return each row per file for all the files that makes up the assembly.
SYS.ASSEMLY_REFERENCES catalog view returns all each row for a pair of assemblies which is directly referencing other assembly.
Dynamic Management Views and Functions
sys.dm_clr_appdomains : Returns a row for each application domain in the server
sys.dm_clr_loaded_assemblies: Returns a row for each managed user assembly loaded into the server address space
sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR
sys.dm_clr_tasks : Returns a row for all common language runtime (CLR) tasks that are currently running
For Further Studies Read
