CLR architecture

The .NET Framework CLR is very tightly integrated with the SQL Server 2005 database engine. In fact, the SQL Server database engine hosts the CLR. This tight level of integration gives SQL Server 2005 several distinct advantages over the .NET integration that's provided by DB2 and Oracle. You can see an overview of the SQL Server 2005 database engine and CLR integration in Figure 3-1.

As you can see in Figure 3-1, the CLR is hosted within the SQL Server database engine. A SQL Server database uses a special API or hosting layer to communicate with the CLR and interface the CLR with the Windows operating system. Hosting the CLR within the SQL Server database gives the SQL Server database engine the ability to control several important aspects of the CLR, including

• Memory management
  
• Threading
  
• Garbage collection

The DB2 and Oracle implementation both use the CLR as an external process, which means that the CLR and the database engine both compete for system resources. SQL Server 2005's in-process hosting of the CLR provides several important advantages over the external implementation used by Oracle or DB2. First, in-process hosting enables SQL Server to control the execution of the CLR, putting essential functions such as memory management, garbage collection, and threading under the control of the SQL Server database engine. In an external implementation the CLR will manage these things independently. The database engine has a better view of the system requirements as a whole and can manage memory and threads better than the CLR can do on its own. In the end, hosting the CLR in-process will provide better performance and scalability.

Figure 3-1: The SQL Server CLR database architecture

Enabling CLR support

By default, the CLR support in the SQL Server database engine is turned off. This ensures that update installations of SQL Server do not unintentionally introduce new functionality without the explicit involvement of the administrator. To enable SQL Server's CLR support, you need to use the advanced options of SQL Server's sp_configure system stored procedure, as shown in the following listing:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CLR Database object components

To create .NET database objects, you start by writing managed code in any one of the .NET languages, such as VB, C#, or Managed C++, and compile it into a .NET DLL (dynamic link library). The most common way to do this would be to use Visual Studio 2005 to create a new SQL Server project and then build that project, which creates the DLL. Alternatively, you create the .NET code using your editor of choice and then compiling the code into a .NET DLL using the .NET Framework SDK. ADO.NET is the middleware that connects the CLR DLL to the SQL Server database. Once the .NET DLL has been created, you need to register that DLL with SQL Server, creating a new SQL Server database object called an assembly. The assembly essentially encapsulates the .NET DLL. You then create a new database object such as a stored procedure or a trigger that points to the SQL Server assembly. You can see an overview of the process to create a CLR database object in Figure 3-2.

Figure 3-2: Creating CLR database objects

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security