.NET Framework Integration

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.

Note

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:

• SAFE This is the most restrictive, recommended, and default permission set. It allows an assembly to perform internal computations and data accessand nothing else.

• EXTERNAL_ACCESS This is the next level after SAFE, and it adds the ability to access external resources, such as files. Members of the sysadmin server role and principals who have login-level EXTERNAL_ACCESS permission can put assemblies in the EXTERNAL_ACCESS permission bucket.

• UNSAFE The UNSAFE permission set allows assemblies unrestricted access to resources, both within and outside SQL Server. Assemblies can even call unmanaged code. Assemblies should be put in the UNSAFE permission bucket only after thorough consideration and analysis. Only the sysadmin role members can import assemblies with this permission set.

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:

• Security The .NET code running inside SQL Server must follow 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 the network from the .NET code running in the database. Database administrators should be able to enable or disable running .NET code from the database engine.

• Reliability The .NET code running inside SQL Server should not be allowed to perform operations that compromise data integrity and transactional correctness. It should not be allowed to perform operations such as popping up a message box requesting a user response, exiting the process, overwriting DBMS memory buffers or internal data structures, causing stack overflow, and so on.

• Performance and scalability The .NET code running inside SQL Server must perform as well as or better than an equivalent implementation through T-SQL. The .NET code should be restricted from calling APIs for threading, memory, and synchronization primitives to ensure the scalability of the system.

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:

• Enhanced programming model The SQLCLR integration allows you to use any .NET programming language and the familiar ADO.NET data access methods to extend the T-SQL language. .NET features such as the object-oriented programming model and structural exception handling facilitate writing code that is manageable and robust.

• Access to the .NET Framework base class library Managed modules (stored procedure, functions, and so on) have access to hundreds of classes in the .NET Framework base class library to performs tasks related to mathematical or statistical calculation, cryptography, disk I/O, XML and string manipulation, and so on.

• Performance T-SQL is an interpreted language, whereas .NET code is just-in-time compiled the first time it is run. The compiled code is cached until the appdomain resides in memory. Several innovative techniques, such as fast transitions between SQL and CLR, the new streaming table-valued functions, binary format normalization of SQLCLR user-defined types, and so on, have been implemented to ensure that SQLCLR code performs as well as or better than an equivalent implementation through T-SQL.

• A replacement for extended stored procedures As mentioned earlier, SQLCLR is an excellent replacement for extended stored procedure. You as a database administrator have control over what a SQLCLR module can and cannot do. The SAFE and EXTERNAL_ACCESS permission buckets guarantee that there will not be any memory leaks and that the module cannot compromise the stability and integrity of the database.

• Security and reliability The SQLCLR integration feature is turned off by default. The .NET CLR code-access security integrated with SQL Server authentication- and authorization-based security gives you complete control over what a SQLCLR module can and cannot do. SQL Server 2005 as a host controls access to memory, thread management, and synchronization. This, in addition to the use of appdomains and host protection attributes, increase the reliability of SQLCLR code. SQL Server 2005 provides several catalog views, DMVs, profiler events, and Performance Monitor counters that you can use to track and trace SQLCLR activity. The ability to create external access or an unsafe SQLCLR module is restricted to few server and database roles or to principals that are granted permission to do so. In summary, SQL Server 2005 goes to great lengths to ensure the safety and reliability of the server.

• Extending type systems and aggregates If SQL Server 2005 does not contain a built-in type or aggregate that you need, you can use SQLCLR to implement your own type or aggregate.

• Developing and debugging using Visual Studio .NET The Visual Studio .NET 2005 IDE has been enhanced to simplify the development, debugging, and deployment of SQLCLR objects. The SQL Server Project template simplifies creating assemblies that you can import into SQL Server. Visual Studio .NET allows cross-language debugging. The deployment feature in Visual Studio .NET 2005 allows you to deploy managed modules directly to the database with a single click. It takes care of dropping existing object, re-importing the assembly, and re-creating the objects.

SQLCLR Integration Challenges

Here are some of the challenges of allowing .NET code to run inside the database engine:

• Deciding between T-SQL and .NET T-SQL is best suited for data access with little or no procedural logic. SQLCLR is best suited for computational, procedural, CPUintensive logic. Another reason to use SQLCLR is to make use of classes and functions that are available in the .NET Framework base class library. SQL Server 2005 introduces several significant improvements to the T-SQL language, such as recursive common table expression (CTE) queries, ranking functions, and new relational operators. You should be sure to consider these enhancements and see if you can implement the task at hand efficiently by using T-SQL before you start writing stored a procedure, trigger, or function using .NET managed code. You should continue to leverage T-SQL's set-oriented processing wherever possible. And you should consider writing CLR code as an efficient alternative for logic that cannot be expressed declaratively in query language. The CLR can be used to replace server-side cursors and in some such situation, it can gain performance improvements.

• Deciding between the middle or client tier and the server tier If you want to centralize data validation or avoid frequent round-trips to the database server, or if you need to process large amounts of data while needing a small portion of it for application use, it makes sense to move the code from the middle or client tier to the server tier. However, you must consider the increased load on the server, which might affect the overall performance and scalability of the server.