.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

Database Snapshots

In SQL Server 2005, an interesting new feature is available is Database snapshots.(Of course it is available on Enterprise Edition only )

It is a point-in-time, read-only , Virtual copy of the source database that is a snapshot of a particular source database can be created in a particular time.It is most useful if your database contains historical data like Quarterly sales, year-wise employees performance. This snapshots can be used for further reporting purposes. Multiple snapshots can be created from single source database for different point of times.


When you create a snapshot for a Source database, SQL Server create an empty Sparse file(is NTFS File),If there are uncommitted transactions are there, it will not copied to Snapshot, the pages before the transactions are copied to Sparse file. NTFS Sparse file will not hold the user data at the time of creation.


Whenever the source database is modified after the snapshot is created, the Copy-On-Write operation is began for every snapshots! At the time of creation of a NTFS Sparse file, SQL Server creates a bitmap file for every sparse file, with bit for every page of the source database to check the page is copied to snapshot or not. When a page updating is in progress, SQL Server checks this bit and if the page is not copied then it will copy the page to snapshot(s). This is called Copy-On-Write operation. The Read from snapshot operation first checks this bit for whether read it from snapshot or from source database. This bitmap is stored in cache so it is always available until the SQL Server shuts down or the database is closed. If any one this two happen, then the bitmaps are need to be reconstructed when the database startups.


The snapshots can not be backed up an restored.More over if any snapshots are available for a particular database, that DB can not be dropped. Snapshots can not be attached or detached,
For security aspects, the all the security constraints are inherited to snapshots. If you drop a user from source database it will not dropped from snapshot!



Need more about Database snapshots

• Chapter 4. Databases and Database Files from the book Inside Microsoft SQL
  Server 2005 : The Storage Engine by Kalen Delaney.

  Read More
  about Read / Write operations in snapshots.

  For
  further pros and cons of database snapshots read this.

SET NOCOUNT ON – A simple Performance Booster

How many of us using the SET NOCOUNT ON statement in our stored procedures? This simple one line code do a lot of differences in performance (in terms of Network Traffic which is one the performance related issue) of SQL Server.

What will this do is simply cut off the number of rows affected by the stored procedures’ Select / Insert / Delete/ Update statements. According to BOOKS ONLINE, When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

By default value of SET NOCOUNT is OFF.

If your application really wants the number of rows affected, then you can use @@RowCount variable which is very handy.

On reading this BOL, a question is raised on my mind, that what happened if I set this on, to SQLDATAREADER’s RecordsAffected Property ? Is there any link between this SET NCOUNT? But it is not having any link. Because the remark section of the SqlDataReader.::.RecordsAffected Property in MSDN (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.recordsaffected.aspx) is saying that The RecordsAffected property is not set until all rows are read and you close the SqlDataReader.

So to reduce network traffic, you can add this simple line at the starting of every stored procedure. For further digging read the followings.

http://www.sqlmag.com/Article/ArticleID/22093/sql_server_22093.html

http://weblogs.asp.net/jgalloway/archive/2006/08/30/How-NOCOUNT-affects-ADO.NET.aspx

http://www.mssqltips.com/tip.asp?tip=1226

A Starting Point to Endpoints

SQL Server 2005 supports various type of Endpoints in its various services. These endpoints are nothing but a listener that can receive requests  from the clients which are connecting to SQL Server's particular service.

Tabular Data Stream Endpoints.

These endpoints are introduced by SQL Server 2005 for defining the SQL Server connections.

When an application is communicating SQL Server it should use the either one of the four available network protocols supported by SQL Server with the format of communication in a Microsoft Communication Format called Tabular Data Stream. During the setup of SQL Server, it creates endpoints for all four type of Protocols (TCP/IP, Named Pipes, Shared Memory, Virtual Interface Adapter) and if the particular Protocol is enabled, then that particular endpoint can be used.

The following endpoints are automatically created

TSQL LocalMachine Endpoint  for Shared Memory

TSQL Named Pipes Endpoint  for Named Pipes

TSQL Default TCP for TCP/IP

TSQL Default VIA for VIA

DAC and HTTP Endpoints are also created for Dedicated Admin Connection and Native XML Web Services ( I will post a separate topic on this Native XML Web Services ) respectively.

For Shared memory and  Named Pipes there can be only one endpoint per instance. But all other endpoints, besides the default endpoints, user end points can be created. The user created endpoints  are manageable with TSQL statements(Create Endpoint, Alter Endpoint, Drop Endpoint). To connect SQL Server through a user created endpoint, that endpoint must be given a GRANT CONNECT permission to PUBLIC by admin.

For further studies on Endpoints search Network Protocols, and TDS Endpoints in SQL Books Online.

Happy New Year 2008...

As Millions of people already said 'Goodbye 2007!', A New Year 2008 has born gloriously. I wish you all a happy and prosperous New year.

Like most of us, I too resolve in this new year to compel my self to write atleast one post in every month(minimum) and i will stand on this resolution.

More over I decided to give you a hint about my next post in my every post.

As you know I missed a lot to post last year as my profession makes my passion out.

This year as we are going to welcome the new flavour of SQL Server 2008, I will discuss more about that too.

So, let us welcome the new year 2008 with a great smiles and we will pass more miles in this year.

HAPPY NEW YEAR.