Integrating CLR in SQL Server 2005

 

T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult. Invariably, your project includes a stored procedure or two, requiring some text parsing or complex math operations. Doing this in SQL Server has been difficult. Either you wrote T-SQL code, or you wrote an extended stored procedure/function and used COM to interoperate them. Neither was a good solution, but that is all we had.

 

In comes SQL Server 2005 with its CLR integration to alleviate these problems. By integrating the CLR, SQL Server 2005 allows you to deploy C# or VB.NET code that is used within the SQL Server process. This means that if you need complex procedural code, you can write it as managed code.

 

Integrating the CLR into SQL Server is not a step to eliminating T-SQL. As .NET developers, it may seem like a good idea to do all your database code in managed code, but this is not the case. Think of the CLR integration as just another tool in your toolbox. This is the hammer that, I suspect, will be used to hammer in nails, screws, and 2x4s in projects the next year. It will be overused. Don’t let your project be the ones caught guilty of this.

 

Architecture

 __________

| SQL Engine |

|    ——-     |

|      CLR       |

|    ——-     |

|__________|

|                    |

|     SQL-OS   |

|__________|

|                    |

|  Windows   |

|__________|

·          Provides co-ordination between CLR and SQL

·          CLR uses SQLOS for Memory, Thread and Syncronization

·          SQLCLR host co-ordinate Assembly Loading, Security Management, Application Domain, Deadlock detection, Escalation policy for exceptions

 

Assembly Management:

1.       Live in Database

2.       Cataloged using CREATE ASSEMBLY

3.       Assembly Verification (Deployment Time and runtime)

4.       System assemblies are loaded from GAC

5.       Other assemblies come to SQL Server and User assemblies should be present in the database.

 

To list all assemblies: SELECT * FROM SYS.ASSEMBLIES

To list all assembly Files: SELECT * FROM SYS.ASSEMBLY_FILES

 

Memory Management:

1.       Memory Request handled by SQL Server

2.       SQL Server and CLR co-ordinate in GC (Garbage Collection)

3.       System CMV for Memory Monitoring

 

App Domain:

1.       During Assembly verification

2.       Two types of app domain

3.       One app domain per assembly owner

4.       Inter app-domain cannot communicate

 

Thread Management:

1.       Managed threads are mapped by "tasks" by the SQLOS scheduler

2.       Cooperative Scheduling model

 

Security:

1.       Three types of security level (SAFE, EXTERNAL_ACCESS, UNSAFE)

 

Exception Management:

1.       SQL Server cannot leak resources

2.       Incase of any exception, malicious code SQL Server shuts down the app domain alone

 

How to deploy assemblies into SQL Server 2005:

 

STEP 1:

CREATE ASSEMBLY ‘ASSEMBLY-NAME’

FROM ‘DLL-PATH’

WITH PERMISSION_SET = SAFE

GO

 

STEP 2:

CREATE PROCEDURE ‘PROCEDURE-NAME’

AS

EXTERNAL NAME ‘ASSEMBLY-NAME’.’CLASS-NAME’.’FUNCTION-NAME’

GO

 

STEP 3:

EXEC ‘PROCEDURE-NAME’

 

To check whether the CLR is RUNNING

SELECT * FROMSYS.DM_OS_MEMORY_CLERKS

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s