.NET CLR Integration with SQL Server 2005.

Even though there are a lot of different thoughts in using CLR based codes inside SQL Server 2005, this feature of SQL SERVER provides a rich programming environment for both developers and DBAs. As the coding technology is in traditional way (with all of the language features) and integrating that code in a most secured area like SQL Server, this environment gives us to create safe, secure, scalable and feature - rich Stored Procedure, UDFs, UDTs, Triggers and User Defined Aggregates.

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)
  

By default, after the installation of the SQL Server, this feature is disabled. We have to enable this feature (if we want to use that). Use Surface Area Configuration For Features Wizard to enable or use the following code.

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

1. Books Online : http://msdn2.microsoft.com/en-us/library/ms131102.aspx
2. Blogs : http://blogs.msdn.com/sqlclr/default.aspx