Managed Database Code: Opportunity or Threat?
Microsoft, as you probably know, is adding managed code functionality to the
next version of SQL Server, code-named "Yukon." With Yukon, you'll be able to
create stored procedures, triggers, and user-defined functions using the .NET
platform and any managed language. These bits of managed code can be called as
first-class objects from Transact-SQL code within the database, and can use a
special provider to interact directly with the database that called them.
Similarly, IBM has announced that the next version of DB2 ("Stinger") will
host the .NET Common Language Runtime (CLR), which will give DB2 its own methods
to run managed code as part of the database. I haven't seen any mention of a
version of Oracle hosting managed code yet, but I'd be surprised if they're not
at least experimenting with the idea.
I had occasion to play with the PDC build of Yukon, and it turns out that
it's pretty trivial to implement the plumbing for a managed code function.
First, you need to define a static function in one of the .NET languages, for
example, Visual Basic .NET:
Public Class Conversions
Public Shared Function LitersToGallons( _
ByVal Liters As Double) As Double
LitersToGallons = 0.264 * Liters
Next, you tell the SQL Server about the assembly and the function:
CREATE ASSEMBLY MyStuff
CREATE FUNCTION LitersToGallons(@Liters FLOAT)
EXTERNAL NAME MyStuff:[MyStuff.Conversions]::LitersToGallons
And finally, you can call the external, managed function like any other
It gets a bit more complex when you want to call back into SQL Server or
implement richer connections between T-SQL and managed code (there's a good
overview article at MSDN: Overview
of .NET Programming Features in SQL Server "Yukon" Beta 1). But it's
certainly not rocket science. I expect lots of developers will be hooking their
database code up to managed code, at first experimentally and then in
production. And that brings me to the point of this column: I'm worried about
whether the tools will be there to make this a good thing.
While I know of good tools for SQL Server, and good tools for .NET
development, I don't know of good tools that cross the boundary from one to the
other. T-SQL development and .NET development are two very different arenas, and
I wonder how many developers will be able to combine the two, especially if the
tools that they depend on aren't ready for this new world. Here are a few things
that I'd like to see shipping in the "Yukon" timeframe (tool vendors take
- Source code control that understands that I have CLR code and T-SQL code
that go together. Just as you can check out a VB .NET form and get both the
visual design files and the source code, you ought to be able to check out a
managed stored procedure and get both the CLR code and the T-SQL code.
- Unit-testing frameworks that let me work with T-SQL. Actually, I'd like one
of these today.
- Build utilities that can handle everything from building the database to
building the managed stored procedure to registering it back in the database as
part of a single process.
- Memory and performance profilers that can give me a unified view of what's
happening in both the SQL Server engine and in the CLR.
- Static code analysis tools that will look at managed stored procedures and
make recommendations as to whether they ought to be reimplemented as pure
- Security analysis tools that can evaluate both the managed code and the
T-SQL code in an integrated fashion.
- Activity logging frameworks that can accept events from both managed code
You get the idea. What I want is the same rich development experience I can
get right now in the .NET world, extended to cover the managed database
applications that will soon be possible. Presumably Microsoft and various ISVs
are already working to make at least part of this vision come true.
Of course, even if the tools come to pass, there's another hurdle to think
about: the continuing culture clash between DBAs and developers. Already
developers in some corporations report problems getting new stored procedures
and schema changes approved by DBAs, who tend to be very protective of the
database. What will happen when the DBAs are faced with implementing managed
code stored procedures, especially if they don't have experience with managed
code themselves? But that's a topic for another time.
Mike Gunderloy has been developing software for a quarter-century now, and writing about it for nearly as long. He walked away from a .NET development career in 2006 and has been a happy Rails user ever since. Mike blogs at A Fresh Cup.