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, 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 fadridden 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.
Source of Information : Apress Accelerated SQL Server 2008
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 fadridden 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.
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment