News

Managed Database Code: Opportunity or Threat?

• By Mike Gunderloy
• December 8, 2003

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:

Namespace MyStuff
    Public Class Conversions
        Public Shared Function LitersToGallons( _
         ByVal Liters As Double) As Double
            LitersToGallons = 0.264 * Liters
        End Function
    End Class
End Namespace

Next, you tell the SQL Server about the assembly and the function:

CREATE ASSEMBLY MyStuff
  FROM 'C:\MyStuff\bin\MyStuff.dll'
GO
CREATE FUNCTION LitersToGallons(@Liters FLOAT)
  RETURNS FLOAT
  EXTERNAL NAME MyStuff:[MyStuff.Conversions]::LitersToGallons
GO

And finally, you can call the external, managed function like any other function:

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 note!):

• 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 T-SQL.
• 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 and T-SQL.

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.