A. Extended Stored Procedure: Older way of extend SP functionality in programming language like c,c++. This wouldn't be supported in future version of sql server. this is call extended as you call a c dll function in a stored proc just like you are calling UDF/SPs.
What's typically done is create a dll which exposes a funciton, register this dll with sql server master DB and create a UDF in DB where you need to call this function, this udf internally call the c dll and execute the code written there.
Parameters can be passed to extended stored procedures, and they can return results and return status.
Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
permission should be granted to user to execute the stored proc. Extended stored procedures can be added only to the master database.
Adding extended stored proc to Database - sp_addextendedproc 'xp_hello', 'c:\hello.dll' this db proc will register the ext sp to database. xp_hello is the name of the funciton which extended the functionality of sp. so any call to Master.dbo.xp_hello will load and execute the dll routine.
Issues with ext sp:
Address space:
As a DLL, it is loaded in the address space of the calling process. In the case of the extended stored procedure, the process is SQL Server. If a DLL is improperly accessing memory or if it is not thread safe, you can adversely affect the process. You must perform thorough testing to make sure that the DLL maintains the integrity of the process. If you are at all concerned that an extended stored procedure may be adversely affecting SQL Server, you must address this problem immediately.
Memory leaks
Any project may have a problem where allocated memory, a handle, or a similar resource is not being released properly. It is paramount to any DLL test suite that the suite makes sure that the DLL is releasing all resources correctly.
Thread safety
Applications such as Microsoft Internet Information Server (IIS) and Microsoft SQL Server are thread pooling, multi-threaded applications. Make sure that all code paths are thread-safe
-------------------------------------------------------------------------------------------------
B. CLR SQL Integration:
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS) to prevent assemblies from performing certain operations. SQL Server uses CAS to help secure the managed code and prevent compromise of the operating system or database server.
The CLR provides various functions and services, to these DB objects also ,required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
You can build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR). Managed code that runs inside of Microsoft SQL Server is referred to as a "CLR routine." These routines include: Scalar UDFs / Tabular UDFs / SP / Triggers.
Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.
Managed code can run on a client computer, while Transact-SQL cannot.
while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of the CLR and SQL Server. With CLR integration, you have a more secure way than extended stored procedures to write the stored procedures you need to perform tasks not possible in Transact-SQL.
In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework.
CLR routines are mapped to public, static methods of a class. UDTs and user-defined aggregates are mapped to entire .NET Framework classes.
Design Goal of CLR Integration:
1. Reliability (Safety)
User code should not be allowed to perform operations that compromise the integrity of the Database Engine process, such as popping a message box requesting a user response or exiting the process. User code should not be able to overwrite Database Engine memory buffers or internal data structures.
2. Scalability
SQL Server and the CLR have different internal models for scheduling and memory management. SQL Server supports a cooperative, non-preemptive threading model in which the threads voluntarily yield execution periodically, or when they are waiting on locks or I/O. The CLR supports a preemptive threading model.
The CLR does not distinguish between virtual and physical memory, but SQL Server directly manages physical memory and is required to use physical memory within a configurable limit.
3. Security
User code running in the database must follow SQL Server authentication and authorization rules when accessing database objects such as tables and columns. In addition, database administrators should be able to control access to operating system resources, such as files and network access, from user code running in the database.
How it's done
CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. In order to synchronize between multiple threads, the CLR calls SQL Server synchronization objects. This allows the SQL Server scheduler to schedule other tasks when a thread is waiting on a synchronization object. The ability to hook CLR threads to SQL Server threads implies that the SQL Server scheduler can identify "runaway" threads in the CLR and manage their priority. Such runaway threads are suspended and put back in the queue.
The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.
SQL Server allows users to specify the reliability and security requirements for code deployed into the database. When assemblies are uploaded into the database, the author of the assembly can specify one of three permission sets for that assembly: SAFE, EXTERNAL_ACCESS and UNSAFE.
Managed code in sql server shouldn't it's maintain state across invocations. For SAFE and EXTERNAL_ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time and fails the creation of such assemblies if it finds the use of static data members and variables.
Coding
The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:
System.Data / System.Data.Sql / Microsoft.SqlServer.Server / System.Data.SqlTypes
1. Enable CLR integration in sql server (by default it is disabled).
2. Create a public class and a static method in it wihch is decorated with
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld(out string text)
{
SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
text = "Hello world!"; }}
[Microsoft.SqlServer.Server.SqlProcedure] attribute which signify this function as an DB sp.
3. Create reference in sql server -
CREATE ASSEMBLY helloworld from 'D:\hellowWorld.dll' WITH PERMISSION_SET = SAFE
4. Create stored proc in sql server which will wrap the call to this assembly and handles input and out put parameter
CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld (sqlAssembly.classname.mthname)
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld (sqlAssembly.classname.mthname)
5. Call this procedure from anywhere in sql server
DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J
If we user visual studio to create SQL SERVER project than most of the work done by VS behind the scene you just need to create a class with static method and project properties has a deploy menu which register this assemmbly in DB and create a wrapper sp for u.
Supported Libraries
Not all .net library is supported by sql server. only libraries which are tested to ensure that they meet reliability and security standards for interaction with SQL Server. Supported libraries do not need to be explicitly registered on the server before they can be used in your code; SQL Server loads them directly from the Global Assembly Cache (GAC).
Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.
pending:
SQL Server Data Types in the .NET Framework
Since SQL data type and CLR data types are different like Null has proper datatype in SQL not is clr so some operand retrun true/false/unknown if both compared values are null to handle this sqldata type has SQLboolean type which supports these three values. same way decimal data type in clr and sql has different precision so sqldata type has SqlDecimal to map with sql data type.
All these sqldata types are part of System.data.SqlTypes namespace which is in System.Data library which is the part of .net base class libraries. in short .Net Framework provides a sql data types in framework via system.data.sqltypes namespace.
Types supported in CLR integration are:
CLR User defined functions - scalar-valued/ Table-values / User defined aggregates.
CLR user defined types (classes)
CLR proc / CLR Triggers.
Examples of all db object types.
For more topics - http://msdn.microsoft.com/en-us/library/ms131046(v=SQL.105).aspx
-------------------------------------------------------------------------------------------------
No comments:
Post a Comment