.NET INTEGRATION WITH SQL SERVER 2005

 

 

• What are steps to load a .NET code in SQL SERVER 2005?
Write the managed code and compile it to a DLL/Assembly.
After the DLL is compiled using the "CREATE ASSEMBLY" command you can load the assemby into SQL SERVER. Below is the create command which is loading "mycode.dll" into SQL SERVER using the "CREATE ASSEMBLY" command
Syntax
CREATE ASSEMBLY AssemblyName FROM 'C:/MyAssmbly.dll'

• How can we drop an essembly from SQL SERVER?
DROP ASSEMBLY AssemblyName

• Are changes made to assembly updated automatically in database?
No, it will not synchronize the code automatically. For that you have to drop the assembly (Using DROP ASSEMBLY) and create (Using the CREATE ASSEMBLY again)

• Why do we need to drop assembly for updateing changes?
When we load the assembly into SQL SERVER, it stores into the sys.assemblies system table. So any changes after that to the external DLL/ASSEMBLY will not reflect in SQL SERVER. So we have to DROP and CREATE assembly again in SQL SERVER.

• How to see assemblies loaded in SQL SERVER?
SELECT * FROM sys.assemblies_files

• If I want to see which files are linked with which assemblies?
Use sys.Assemblies_files system tables have the track about which files are associated with what assemblies.
SELECT * FROM sys.assemblies_files

• Does .NET CLR and SQL SERVER run in different process?
.NET CLR engine (hence all the .NET applications) and SQL SERVER run in the same process or address space. This "Same address space architecture" is implemeted so that there no speed issues. If the architecture was implemented the other way (i.e. SQL SERVER and .NET CLR engine running in different memory process area) there would have been reasonable speed issue.

• Does .NET controls SQL SERVER or is it vice-versa?
SQL SERVER controls the way .NET application will run. Normally .NET framework controls the way application should run. But in order that we have high stability and good security SQL SERVER will control the way .NET frame work works with SQL SERVER environment. So lot of things will be controlled through SQL SERVER example: threads, memory allocations, security etc.

SQL SERVER can control .NET framework by "Host Control" mechanism provided by .NET Framework 2.0. Using the "Host Control" framework external application's can control the way memory management is done, thread allocation's are done and lot more. SQL SERVER uses "host Control" mechanism exposed by .NET 2.0 and controls the framework.

• Is SQLCLR configured by default?
SQLCLR is not configured by default. If Developers want to use the CLR integration feature of SQL SERVER it has to be enabled by DBA (From the Surface Area Configuration).

• How to configigure CLR for SQL SERVER?
It's a advanced option you will need to run the following query Analyzer

EXEC Sp_configure 'show advaced option', '1''; go reconfigure go
EXEC Sp_configure 'clr enabled', '1' go reconfigure; go
EXEC Sp_configure 'show advanced options', '1'; reconfigure
EXEC sp_configure' clr enabled' ' '1' reconfigure

• Is .NET feature loaded by default in SQL SERVER?
NO it will not be loaded, CLR is lazy loaded that means its only loaded when needed. It goes one step ahead. where the database Administrator has to turn the feature and using the "SP_Configure".

NOTE: Loading .NET programming consumes some memory resources around 20 to 30 MB(it may vary depending on lot of situations). So if you really need .NET Integration then only go for this option.

• How does SQL Server control .NET at run-time?
.NET CLR exposes interfaces by which an external host can control the way .NET rum time runs. In previous versions of .NET it was done via COM interface "ICorRuntimeHost".
In previous version you can only do the following with COM interface.

* Specify that whether its server or work station DLL
* Specify version of the CLR (e.g.version 1.1 or 2.0)
* Specify garbage collection behavior
* Specify whether or not jitted cod emay be shared across AppDomains.

In .NET 2.0 it's done by "ICLRRuntimeHOST" But in .NET 2.0 you can do much above that was provided by the previous COM interface

* Exceptional conditions
* Code loading
* Class loading
* Security particulars
* Resource allocation

SQL Server uses the "ICLRRuntimeHOST" to control .NET run time as the flexibility provided by the interface is far beyond what is given by the previous .NET version, and that 's what exactly SQL Server needs, a full control of the .NET run time.

• What's a "SAND BOX" in SQL SERVER 2005?
"Sand box is a safe place for running semi-trusted programs or scripts, often originationg from the a third party"

Now for SQL SERVER it's .NET the external third party which is running and SQL SERVER has to be sure that .NET runtime crashes does not affect his working. So in order that SQL Server runs properly there are three sandboxes that user code can run:

Safe Access sandbox: This will be the favorite setting of DBA's if they are compelled to run CLR- Safe access Safe means you have only access to in-proc data access functionalities. So you can create stored procedures, triggers, functions, data types, triggers etc. But you can not access memory, disk, create files etc. In short you ca not hang the SQL Server.

External access sandbox: In External access you can use some real cool features of .NET like accessing file systems outside box, you can leverage your classes etc. But here you are not allowed to play around with threading, memory allocation etc.

Unsafe access sandbox:In Unsafe access you have access to memory management, threading,etc. So here developers can write unreliable and unsafe code which destabilizes SQL Server. In the first two access levels of sand box its difficult to write unreliable and unsafe code.