Skip navigation

The CLR in IBM DB2 and SQL Server: Night and Day

Last August, in "Will Stinger Steal Yukon's Glory?" ( http://www.windowsitpro.com/Article/ArticleID/43713/43713.html ), I commented about how interesting it was that IBM DB2's latest release beat Microsoft to market with native Common Language Runtime (CLR) support within the database engine. Since then, I've further explored how the DB2-CLR implementation compares to the CLR support that SQL Server 2005 will have next year. I spoke with several people from Microsoft, including SQL Server Director of Product Management Tom Rizzo, SQL Server Group Program Manager for CLR Mahesh Prakriya, and SQL Server Group Program Manager for the relational database engine Balaji Rathakrishnan. They gave me some helpful information about the most important differences between SQL Server 2005 CLR implementations and IBM DB2 8.2's implementations.

DB2 supports CLR 1.0 or CLR 1.1, whereas SQL Server 2005 will support CLR 2.0, which provides a richer and more powerful set of hosting APIs. To be fair, because Whidbey hasn't been released, IBM couldn't have supported CLR 2.0. However, the richer hosting APIs in Whidbey are incredibly important to SQL Server 2005's ability to host the CLR in-process. DB2 hosts its CLR out-of-process by using what IBM calls a "fenced mode." I believe that the difference between in-process and out-of-process CLR implementation is perhaps the single most important difference between Microsoft and IBM in this regard. Among other things, hosting in-process will provide substantial performance gains. Sources at Microsoft told me that in lab tests, the SQL Server 2005 CLR implementation performs up to seven times faster than the out-of-process IBM implementation.

In addition, the richer hosting APIs in Whidbey let SQL Server dynamically optimize the CLR within its view of what's happening within the process. DB2's reliance on previous versions of the CLR prevents the core engine from dynamically tuning the CLR as well as SQL Server 2005 will be able to. DB2 simply won't know what's happening inside of the CLR process. Think of it this way: SQL Server is essentially acting as the OS for the CLR. In comparison, DB2 and its CLR aren't much more than side-by-side processes that talk to each other like any other two processes might. Neither process has much information about the other, and neither process can make intelligent decisions about how it should run based on the activity in the other process.

The differences in SQL Server and DB2 security are also important. SQL Server 2005 will pass along security and context information so that the CLR can effectively impersonate the original caller when external resources are accessed. DB2's CLR is based on process and user-level isolation, similar to the way SQL Server 2000's xp_cmdshell works, which isn't as flexible as SQL Server 2005's security model.

DB2 doesn't support user-defined types (UDTs) or user-defined aggregates (UDAs) within the CLR implementation--SQL Server 2005 will. CLR assemblies in SQL Server 2005 are a native part of the database and are stored in the database. DB2 assemblies are stored in the file system and administrators must back them up separately, making backup and recovery more difficult. SQL Server 2005's CLR has more sophisticated host protection, which prevents users from calling certain .NET APIs that could be dangerous to run within the engine. DB2's host protection isn't as rigorous as SQL Server's. For example, opening a GUI dialog box from a DB2 CLR procedure would essentially hang that connection, whereas SQL Server's CLR would consider such an action unsafe and prevent it. A DB2 CLR procedure could call the Environment.Exit method, which would essentially shut down the entire DB2 CLR instance. Again, the host protection in the Microsoft version would prevent this action. Microsoft has provided a richer level of tool support and tighter integration with Visual Studio .NET. For example, SQL Server 2005 will let you step through a CLR procedure by using the Visual Studio debugger. This isn't possible with the DB2 implementation. You can manually attach to the CLR process for debugging, but DB2 doesn't natively support debugging in the core development tools.

The entire list of differences between the Microsoft and IBM CLR implementations is long, so I'll stop now and just give you a few links that will help you dive deeper into the subject. For a comprehensive overview, see "Comparing SQL Server 2005 and IBM DB2 v8.2 as a Database Platform for Visual Studio and .NET Developers" by Michael and Denielle Otey at http://download.microsoft.com/download/c/e/c/cecc2098-85a1-4c41-98bd-f4c6d566a78d/DatabaseForVSDevelopers.pdf. Or, you can review the published Web chat "SQL Server Chat: SQL Server 2005 Beta 2 CLR Q&A," sponsored by the SQL Server Worldwide Users Group (SSWUG) at http://www.microsoft.com/technet/community/chats/trans/sql/SQL_101204.mspx. And Microsoft's Chris Brumme writes a Weblog ( http://blogs.msdn.com/cbrumme/category/2669.aspx ) that has some great CLR information.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish