Evolving CLR Executables Technology


In earliers versions of SQL Server, developers, DBAs, and code-generator tools coded stored procedures, triggers, functions, and other objects in T-SQLand just T-SQL. However, if your skills are sharp enough, you can develop and call an Extended Stored Procedure (ESP) written in C or C++. Not many developers choose to take the ESP route, as it requires a thorough understanding of Open Data Services (ODS), COM, and other complex (and potentially destructive) technology. Not only that, but developers need to get their DBA to sign off on their use, and DBAs are naturally hesitant to do so. That's because if the ESP code screws up, it can (easily) bring down the server or worse[1], and you'll need someone to take the blame.

[1] For an excellent article on Extended Stored Procedures, check out this article by Kevin Cox, published by 29th Street Press (1997): www.windowsitlibrary.com/Content/77/20/1.html#1

ESPs did not gain much traction back in the late 1990s, when they were first discussedthey were seen as too hard to code and maintain and far too dangerous. Understand that an ESP could kill SQL Server in a heartbeat. It's kinda like juggling with chainsawscool to watch until something goes wrong. As a result, ESPs were used very selectively to address some of the most CPU-intensive tasksthose well beyond the reach of T-SQL. For example, ESPs were implemented to perform complex geometry, trigonometry, or statistical calculations; expose low-level Windows API functionality; perform other complex CPU-intensive operations; or just provide functionality that T-SQL could not perform without assistance or a lot of code. Over the years, the SQL Server team exposed a number of system ESPs for use by the SQL Server tools and special DBA functions.

One problem with using ESPs is that SQL Server must (by definition) fully trust the codethere is no "partial-trust" or "sorta-trust" option. "Trust" functionality is first enabled with SQL Server 2005. Because you use unmanaged code to create ESPs and they run in the same address space as SQL Server, a problem in an extended stored procedure could damage SQL Serverin fact, it could blue-screen the system. Not good. Now who was it that said we should use ESPs?

Are CLR executables any safer than the ESP procedures they replace? No, not necessarily. If the CLR executable has to be marked as "Unsafe", it can do just as much damage as an ESP executable.

Tip

Microsoft wrote significant parts of SQL Server 2005 using CLR languages such as managed C++, C#, and Visual Basic .NET.


Microsoft's ESP Replacement

In SQL sServer 2005, T-SQL is still the language of choice for server-side executables, but you can also implement many of these executable objects in C# and Visual Basic .NET, and whatever .NET Framework CLR languages Microsoft chooses to add. In a nutshell, SQL Server 2005 exposes .NET Framework functionality by incorporating the ability to execute CLR code from within the SQL Server address space. This gives developers the option to:

  • Write SQL Server executable code such as stored procedures, functions, and triggers in CLR-based C# or Visual Basic .NET, and invoke those stored procedures just as T-SQL stored procedures are executed

  • Write CLR-based user-defined data types (UDTs) and aggregates, and use them and their associated code to define your tables and business rules

  • Execute CLR code in three defined levels of trust"SAFE" (completely untrusted), "EXTERNAL_ACCESS" (partially trusted), and "UNSAFE" (completely trusted)[2]

    [2] These characterizations of the trust level might seem backward, but they aren't. See the further discussion later in this chapter.

  • Write replacements for ESPs that perform the same operations, but (potentially) more safely and with direct access to the latest SQL Server datatypes

The real question is: Should you use CLR executables? If you remember Jurassic Park (1993) and Jeff Goldbloom's character's speech to Mr. Hammond:

Yeah, but your scientists were so preoccupied with whether or not they could, they didn't stop to think if they should.[3]

[3] Quoted from the movie Jurassic Park. © 1993, Universal Studios.

No, I don't think that CLR executables are reincarnations of carnivorous velociraptors or lumbering brontosauruses in disguise, but this technology is a radical departure from well-known and well-understood T-SQL and should be treated (like any new technology that I'm just learning) with a good measure of respect. I also hope you have doubly redundant high-tension fences placed strategically around your serverjust in case.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net