With SQL Server 2000, Transact-SQL (T-SQL) was the primary language that database administrators and developers used to interact with SQL Server. Stored procedure, triggers, user-defined functions, batches, and so on are written using the T-SQL language. If something was not possible using T-SQL, developers had an option of writing an extended stored procedure by using C++ and the Open Data Services (ODS) API. However, writing extended stored procedure is not a trivial task. On top of that, because an extended stored procedure ran directly in SQL Server 2000 process space, it could possibly compromise the reliability and stability of the server. Memory leaks were other common problems with extended stored procedures, including those from Microsoft (for example, see Microsoft Knowledge Base articles 300414 and 164523).
T-SQL is still the primary language that database administrators and developers use to interact with SQL Server 2005. However, this release now provides a simpler, secure, reliable, and efficient way of extending the T-SQL language. The .NET integration with SQL Server 2005 allows developers to write stored procedures, functions, triggers, aggregates, and types, using any of the .NET languages, such as C# or Visual Basic .NET. As explained earlier, the .NET code is executed under the CLR execution environment. Therefore, in order to allow stored procedures, functions, and triggers written using .NET to run from within the database engine, SQL Server 2005 hosts the CLR, which in turn runs the managed code.
Extending the T-SQL Language
Developers can use any .NET programming language, such as Visual Basic .NET or C#, and any SQL Server project template in Visual Studio .NET 2005 to create a CLR assembly that contains methods decorated to be mapped to stored procedures, functions, or triggers.
This assembly is then imported into a SQL Server 2005 database by using the CREATE ASSEMBLY DDL statement. CREATE ASSEMBLY permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, as well as to users with CREATE ASSEMBLY permission. The Windows login of the user executing CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.
The current release does not support assembly encryption or obfuscation.
After the assembly is imported into the database, the assembly .dll file on the disk is no longer required. It is recommended that you import into the database the source code and other files required for the assembly by using the ALTER ASSEMBLY...ADD FILE statement. While creating the assembly using the CREATE ASSEMBLY statement or re-importing the assembly using the ALTER ASSEMBLY statement, you can specify what an assembly can and cannot do. For example, you can use the WITH PERMISSION_SET clause with the CREATE or ALTER ASSEMBLY statement to put the assembly in one of following three permission buckets:
When the assembly bits are available in the database, you can create a stored procedure or a function or a trigger and use the AS EXTERNAL NAME clause with the CREATE PROCEDURE, FUNCTION, or trIGGER DDL statement and specify the assembly name, class name, and method name to which the module maps. After the stored procedure, function, or trigger is created, it can be invoked like any other T-SQL module.
When you call a managed stored procedure, function, or trigger, SQL Server 2005 first checks whether clr enabled systemwide configuration is enabled. If it is disabled, you see an error message similar to the following:
Msg 6263, Level 16, State 1, Line 3 Execution of user code in the .NET Framework is disabled. Use sp_configure "clr enabled" to enable execution of user code in the .NET Framework.
clr enabled is an advanced option, and you need to enable the show advanced options option and then run RECONFIGURE to see the clr enabled option. After you change the clr enabled option, you must run RECONFIGURE to activate the option. If the clr enabled option is enabled, SQL Server checks whether the assembly containing the mapped method is already loaded in memory. If it is not, the assembly is loaded from the bits imported in the database, and the method is invoked. The .NET CLR code access security integrated with the SQL Server authentication- and authorization-based security model ensures that the method can perform only the operations for which it is granted permissions.
You can drop an assembly from the database by using the DROP ASSEMBLY statement. You cannot drop an assembly unless all the objects (stored procedures, functions, triggers, types, and aggregates) that depend on the assembly are dropped and all the other assemblies that reference this assembly are dropped.
You can use the ALTER ASSEMBLY statement to modify the properties of an assembly, to refresh it to the latest or current version, or to add or remove files associated with the assembly. ALTER ASSEMBLY and DROP ASSEMBLY permissions default to the assembly owners and members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles. These permissions are not transferable.
The Programmability folder under the database in the Object Explorer tree in Management Studio lists the assemblies and modules such as procedures and functions. You can right-click an assembly and select View Dependencies to see objects that depend on the selected assembly and objects on which the selected assembly depends. The Object Dependencies dialog is shown in Figure 11.1.
Figure 11.1. SQL Server Management Studio allows you to view objects that depend on an imported assembly and objects on which the assembly depends.
Similarly, you can right-click an object, such as a stored procedure or a function, and select View Dependencies to see the assemblies and other objects on which the selected object depends.
SQLCLR Design Goals
CLR hosting in SQL Server 2005 is architected to meet the following design goals:
Meeting the above design goals was not easy because SQL Server 2005 and the CLR have different internal models for security, memory management, thread scheduling, and management schemes. To make SQL Server and the CLR work together, while achieving these design goals, the hosting API in .NET Framework 2.0 is enhanced to enable the runtime host, such as SQL Server 2005, to either control or make recommendation on how the resource should be managed.
SQL Server 2005 as a host has control over memory. It can reject the CLR memory request and ask the CLR to reduce its memory use if required. The .NET runtime calls SQL Server for allocating and de-allocating the 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 that the CLR and SQL Server are not competing against each other for memory.
As explained in detail in Chapter 2, "SQL Server 2005 Database Engine Architectural Changes," to minimize context switching, SQL Server uses cooperative thread scheduling, where a thread must voluntarily give up control of the processor. On the other hand, the CLR supports the preemptive threading model, where the processor takes control back from the thread when the time slice is over. To ensure stability, the 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 CLR defines the notion of application domain (appdomain), which can be thought of as a lightweight process. Unlike Win32 processes, which are isolated by having different memory address spaces, the isolation in appdomains is achieved by .NET keeping control over the use of memory. The .NET CLR ensures that appdomains do not access each other's memory. One Win32 process may include multiple CLR appdomains. SQLCLR leverages the notion of appdomains to make the SQLCLR integration reliable when running inside a SQL Server host. Programmers and database administrators do not have control over when and how appdomains are created. The sys.dm_clr_appdomains dynamic management view (DMV) can be used to view a list of appdomains in the SQL Server process space.
To increase the reliability of the .NET code running inside SQL Server, the classes and functions in the .NET Framework base class library have been updated to include attributes called host protection attributes, which can be used by hosts such as SQL Server 2005 to indicate which API can or cannot be called by the .NET code running in the host.
In summary, the SQL Server engine performs all the memory and thread or task management for the hosted CLR programs. The notion of appdomains and host protection attributes increases the reliability of SQLCLR code.
Figure 11.2 shows the SQLCLR architecture.
Figure 11.2. SQLOS and hosting layer components inside the SQL Server 2005 engine ensure the reliability, safety, and performance of the hosted CLR code.
The SQLOS is responsible for connection management, memory management, thread management, and synchronization services. The hosting layer provides coordination of assembly loading, deadlock detection, execution context, security, and memory management. This is explained in detail in Chapter 2.
Before looking at some SQLCLR examples, let's quickly review the benefits and challenges of integrating SQL Server 2005 and the CLR.
SQLCLR Integration Benefits
Here are some of the advantages of allowing .NET code to run inside the database engine:
SQLCLR Integration Challenges
Here are some of the challenges of allowing .NET code to run inside the database engine: