Common Language Runtime from the DBA Point of View

When the Common Language Runtime (CLR) was introduced in SQL Server 2005, it was enthusiastically received by developers. CLR gave developers a powerful new way to write database code by using the same modern object-oriented (OO) languages that they used to develop other applications.

From the DBA point of view, the introduction of CLR blurred the distinction between compiled application code and database code. It raised questions about the roles of the developer and the DBA in the deployment, management, and security of CLR code modules. To some extent, it also blurred the distinction between the business logic tier and the data access tier in n-tier development models.

For many organizations, implementing CLR requires making decisions about the development process and about the roles of those involved. If you’re preparing to implement CLR, you need enough information to facilitate those decisions without getting immersed in too much technical detail.

To that end, I’ll explain how CLR is implemented and discuss the pros and cons of CLR code and the new wrinkles that this technology has brought to SQL Server management and security. I’ll also describe situations in which CLR might be helpful and situations in which it might not be appropriate.

Creating and Deploying CLR

CLR is a programming model that allows developers to use compiled OO languages to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Basically, the process is as follows:

1.     A developer creates code in a Visual Studio (VS) project. The code can be written in either C# or Visual Basic .NET.

2.     The code is compiled and deployed from VS directly into the target database. The compilation process creates a DLL. The DLL is an executable file that contains the compiled code. Deployment also inserts information about the assembly into the sys.assemblies view and other related views in the target database.

3.     One or more T-SQL objects are created in the target database by using the EXTERNAL NAME option to point to a code module in the assembly. These objects are referred to as prototypes. A prototype can be defined as a stored procedure, user-defined function (UDF), trigger, user-defined aggregate, or user-defined type. The prototype contains no code; it’s simply an entry point that can be called from other database code. The compiled code in the DLL is executed when the code is called.

Usually, all these steps are executed when you compile and deploy the code from a VS project. But you can also execute each step manually.

Modern OO languages have functionality that’s difficult or impossible to reproduce through T-SQL. CLR offers a way to use the rich feature set of these languages in database code. CLR is often better suited for computational or iterative operations than is T-SQL. CLR code executes in the same memory context as SQL Server, so it runs very efficiently.

CLR code is managed code. This means that it runs inside a CLR virtual machine (VM) that prevents execution of any code that has the potential to destabilize the server. Although this is a good thing, any form of virtualization has some degree of overhead. For example, CLR can be overused in ways that affect performance. Although compiled code normally executes efficiently within the SQL memory space, T-SQL is still the best option for heavy lifting. If a procedure accesses or returns large amounts of data, it probably isn’t a good candidate for CLR.

An oversimplified rule of thumb is that T-SQL handles set-based operations more efficiently, and CLR handles iterative, procedural, or computational operations more efficiently. There are many exceptions to this rule, however.

Managing CLR

Sometimes the first contact a DBA has with CLR is a request to enable CLR on the server. Doing this is as simple as enabling the clr enabled server configuration option. But there are many issues to consider before you flip the switch.

Deploying code into the database has traditionally been the responsibility of the DBA, who should make sure that the code works correctly and won’t affect performance or cause other problems on the server. However, CLR code is ordinarily deployed into the database directly from the VS environment in which it was developed. Traditionally, VS hasn’t been a tool used by the DBA. This suggests that the developer might be in a better position than the DBA to deploy CLR code.

This responsibility has to be worked out in each individual shop. Many DBAs would be uncomfortable knowing that a developer has an unfettered ability to create compiled code modules that run in the same memory space as SQL Server itself. However, the DBA still has granular control over who can load CLR assemblies and at which security level the assemblies can function.