Introduction to SQL Server .NET Integration - SQL Server 2008

SQL Server developers have had few choices in the past when it came to doing things in the database for which Transact-SQL (T-SQL) wasn’t especially well suited. This includes such things as complex or heavily mathematical logic, connecting to remote services or data stores, and manipulating files and other non–SQL Server–controlled resources. Although many of these tasks are best suited for operation on the client rather than within SQL Server, sometimes system architecture, project funding, or time constraints leave developers with no choice—business problems must be solved in some way, as quickly and cheaply as possible. XPs were one option to help with these situations, but as mentioned in this chapter’s introduction, these are difficult to write and debug, and are known for decreasing server stability. Another option was to use the sp_OA (Object Automation) stored procedures to call COM objects, but this has its own issues, including performance penalties and dealing with COM “DLL hell” if the correct versions are not registered on the SQL Server.

CLR integration does away with these issues and provides a structured, easy-to-use methodology for extending SQL Server in a variety of ways.

Why Does SQL Server Host the CLR?

There are some things that T-SQL just isn’t meant to do. For instance, it’s not known as a language that excels at accessing data from web services. Another good example is data structures. T-SQL contains only one data structure: the table. This works fine for most of our data needs, but sometimes something else is needed, such as an array or a linked list. And although these things can be simulated using T-SQL, it’s messy at best.

The CLR is a managed environment, designed with safety and stability in mind. Management means that memory and resources are automatically handled by the runtime. It is very difficult (if not impossible) to write code that will cause a memory leak. Management also means that SQL Server can control the runtime if something goes wrong. If SQL Server detects instability, the hosted runtime can be immediately restarted.

This level of control was impossible with the XP functionality in earlier versions of SQL Server. XPs were often known for decreasing the stability of SQL Server, as there was no access control—an unwitting developer could all too easily write code that could overwrite some of SQL Server’s own memory locations, thereby creating a time bomb that would explode when SQL Server needed to access the memory. Thanks to the CLR’s “sandboxing” of process space, this is no longer an issue.

The CLR builds virtual process spaces within its environment, called application domains. This lets code running within each domain operate as if it had its own dedicated process, and at the same time isolates virtual processes from each other. The net effect in terms of stability is that if code running within one application domain crashes, the other domains won’t be affected; only the domain in which the crash occurred will be restarted by the framework, and the entire system won’t be compromised. This is especially important in database applications. Developers certainly don’t want to risk crashing an entire instance of SQL Server because of a bug in a CLR routine.

When to Use CLR Routines

T-SQL is a language that was designed primarily for straightforward data access. Developers are often not comfortable writing complex set-based solutions to problems, and they end up using cursors to solve complex logical problems. This is never the best solution in T-SQL. Cursors and row-by-row processing aren’t the optimal data-access methods. Set-based solutions are preferred.

When non–set-based solutions are absolutely necessary, CLR routines are faster. Looping over a SqlDataReader can be much faster than using a cursor. And complex logic will often perform much better in .NET than in T-SQL. In addition, if routines need to access external resources such as web services, using .NET is an obvious choice. T-SQL is simply not adept at handling these kinds of situations.

When Not to Use CLR Routines

It’s important to remember an adage that has become increasingly popular in the fad-ridden world of information technology in the past few years: “To a hammer, everything looks like a nail.” Just because you can do something using the CLR doesn’t mean you should. For data access, set-based T-SQL is still the appropriate choice in virtually all cases. Access to external resources from SQL Server, which CLR integration makes much easier, is generally not appropriate from SQL Server’s process space. Think carefully about architecture before implementing such solutions. External resources can be unpredictable or unavailable—two factors that aren’t supposed to be present in database solutions!

In the end, it’s a question of common sense. If something doesn’t seem to belong in SQL Server, it probably shouldn’t be implemented there. As CLR integration matures, best practices will become more obvious. For the meantime, take a minimalist approach. Overuse of the technology will cause more problems in the long run than underuse.

How SQL Server Hosts .NET: An Architectural Overview

The CLR is completely hosted by SQL Server. Routines running within SQL Server’s process space make requests to SQL Server for all resources, including memory and processor time. SQL Server is free to either grant or deny these requests, depending on server conditions. SQL Server is also free to completely restart the hosted CLR if a process is taking up too many resources. SQL Server itself is in complete control, and the CLR is unable to compromise the basic integrity that SQL Server offers.

Why Managed Objects Perform Well

SQL Server CLR integration was designed with performance in mind. Compilation of CLR routines for hosting within SQL Server is done using function pointers in order to facilitate high-speed transitions between T-SQL and CLR processes. Type-specific optimizations ensure that routines are just-in-time (JIT) compiled, so no further cost is associated with their invocation.

Another optimization is streaming of result sets from CLR table-valued functions (which will be covered in detail in the next chapter). Unlike some other rowset-based providers that require the client to accept the entire result set before work can be done, table-valued functions are able to stream data a single row at a time. This enables work to be handled in a piecemeal fashion, thereby reducing both memory and processor overhead.

Why CLR Integration Is Stable

SQL Server both hosts and completely controls the CLR routines running within the SQL Server process space. Since SQL Server is in control of all resources, routines are unable to bog down the server or access unavailable resources, as XPs could.

Another important factor is the HostProtection attribute. This attribute allows methods to define their level of cross-process resource interaction, mainly from a threading and locking point of view. For instance, synchronized methods and classes (for example, System.Collections.ArrayList. Synchronized) are decorated with the Synchronization parameter of the attribute. These methods and classes, as well as those that expose a shared provider state or manage external processes, are disallowed from use within the SQL Server–hosted CLR environment, based on permission sets chosen by the DBA at deployment time. Permission sets are covered in more detail later in this chapter, in the section “Deploying CLR Routines.”

DBAs supporting the CLR features in SQL Server should realize that this is no longer the world of XPs. These objects can be rolled out with a great deal of confidence. And as will be discussed later in this chapter, the DBA has the final say over what access the CLR code will have once it is deployed within the server.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics