SQL Server Magazine UPDATE, December 2, 2004--CLR

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

This Issue Sponsored By

Disaster Recovery Toolkit for the SQL DBA

Award Winning Backup and Restore for SQL Server

DB Ghost Builds, Compares & Syncs Your Databases

December 2, 2004

1. Perspectives

  • The CLR in IBM DB2 and SQL Server: Night and Day
  • 2. News and Views

  • Results of Previous Instant Poll: Time in IT

  • New Instant Poll: Base Salary
  • 3. Announcements

  • Get Access to Every SQL Server Magazine Article

  • SQL Server Magazine Gives DBAs What They Need!
  • 4. Peer to Peer

  • Hot Tip: Stopping a Stubborn Trace

  • Hot Article: SQL Server Magazine's 2004 Salary and Industry Survey

  • Hot Threads
  • 5. Events Central

  • Newest SQL Server Web Seminar: Data Protection & High Availability

  • SmartDBA Recovery Management Webinar

  • SQL Server Magazine Connections Spring 2005
  • 6. New and Improved
    - Migrate Stored Procedures - Get File-Based Continuous Data Protection

    Sponsor: Disaster Recovery Toolkit for the SQL DBA
    Written by SQL Server expert Brian Knight, this handy, "how-to" toolkit contains comprehensive first-hand advice and scripts for SQL Server DBAs that need to build and implement a successful disaster-recovery plan. With his tips and quips, Brian walks the DBA through real-world scenarios using an easy, step-by-step approach. And as part of the download, you'll receive four scripts, which will greatly speed your recovery time! Download it today, compliments of Lumigent:

    1. Perspectives

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

  •     by Brian Moran, [email protected]

    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.

    Award Winning Backup and Restore for SQL Server
    Make SQL LiteSpeed a part of your disaster-recovery plan. LiteSpeed is a rapid backup and restore solution from Imceda software that compresses data to up to 95% of its original size. Don't entrust your data to untested imitations, SQL LiteSpeed is the industry leader that has won awards and the praise of thousands. Test SQL LiteSpeed for free, tell us what you think, and we'll send you a free T-Shirt. Go to:

    2. News & Views

  • Results of Previous Instant Poll: Time in IT

  • "Approximately how long have you worked in IT?" Here are the results from the 406 votes:

  • 11%   Less than 5 years
  • 54%   6-15 years
  • 23%   16-25 years
  • 11%   26-40 years
  • 1%    More than 40 years
  • New Instant Poll: Base Salary
  • "What do you expect your total base salary compensation to be in 2004?" Go to the SQL Server Magazine home page ( http://www.sqlmag.com ) and submit your vote for

  • Under $30,000
  • $30,000 to $50,000
  • $50,000 to $75,000
  • $75,000 to $100,000
  • More than $100,000
  • 3. Announcements

  • Get Access to Every SQL Server Magazine Article

  • Order the newest version of the SQL Server Magazine Master CD and receive portable access to every SQL Server Magazine article ever written! As an added bonus, you'll also receive exclusive Scripting and Security content--a $30 value for FREE. Subscribe to this helpful resource today:

  • SQL Server Magazine Gives DBAs What They Need!

  • Order SQL Server Magazine today and get 30% off the cover price! Along with these great savings you'll receive free access to every article ever published in the magazine--online! That's a library containing over 5 years of tips, tricks and how-tos! Click here to subscribe:

    4. Peer to Peer

  • Hot Tip: Stopping a Stubborn Trace

  • by Brian Moran, [email protected]

    Q. When I query the fn_trace_getinfo() function, I see a trace running. When I try to stop the trace by using EXEC sp_trace_setstatus trace_id, 0, SQL Server shuts down. When I restart SQL Server, the trace is running again. What is this trace, and how do I stop it?

    Read the answer to this question today at

  • Hot Article: SQL Server Magazine's 2004 Salary and Industry Survey

  • SQL Server Magazine conducted its 113-question industry and salary survey from August 23 through September 10, 2004. We sent email invitations to about 30,000 SQL Server professionals who had opted to receive information from the magazine, and we promoted the survey in our UPDATE email newsletters and on our Web site. The magazine offered five $100 (US) American Express gift certificates as an incentive to participate in the survey. A total of 778 people provided data by the close date. See what respondents had to say in our three December focus articles.

    Read "Masters of Multitasking" today at

    Read "Who Makes What?" today at

    Read "SQL Server Pros: Surviving or Thriving?" today at

    Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.

    Unable to Login to Report Server
    Need Help Writing a SELECT Statement
    Running dtsrun.exe on a 64-Bit Server
    Migrating a Maintenance Plan
    Can I Query a Stored Procedure's Results?
    Setting Low Priority on Automatic Stored Procedure

    Hot Spot

    DB Ghost Builds, Compares & Syncs Your Databases
    For similar money you can either buy DB Ghost(TM) or you can go for our competitors tools that cannot build, and can only compare and sometimes synchronize. We think it's a no brainer. See for yourself:

    5. Events Central

  • Newest SQL Server Web Seminar: Data Protection & High Availability

  • Sign up today for this FREE, one-hour Web Seminar and learn how to ensure your SQL Server applications are always highly available. You'll also discover ways to best protect your mission-critical data and gain a better understanding of the associated metrics helping to quantify the value of your SQL Server based application data. Register now:

  • SmartDBA Recovery Management Webinar

  • Can you meet your backup and recovery SLAs? Learn how in less than 30 minutes! In this brief, complimentary webinar, you'll find out how BMC Software SmartDBA Recovery Management helps you ensure system availability and lower IT costs. View it now!

  • SQL Server Magazine Connections Spring 2005

  • Mark your calendar for the spring 2005 SQL Server Magazine Connections conference March 20-23, 2005, at the Hyatt Grand Cypress Resort in Orlando, Florida. You won't want to miss this exciting event offering cutting-edge sessions by industry gurus. Call 800-438-6720 or 203-268-3204 for more info and check our Web sites for updates.

    See the complete Windows IT Pro Network guide to Web and live events

    6. New & Improved

        by Dawn Cyr, [email protected]

  • Migrate Stored Procedures

  • AdventNet announced the release of a free edition of SwisSQL Database Stored Procedure Migration Tools, part of the company's suite of tools that automates migration of complex proprietary stored procedures, SQL dialects, database schema, and enterprise data between database platforms. The SwisSQL database migration suite is the first toolset to offer stored procedure migration. The free edition, which is designed for small projects or parts of larger projects, lets customers migrate up to 5000 lines of stored procedures across SQL Server, Oracle, IBM DB2, and Sybase databases. The free edition supports all the migration features of the product's Professional Edition, which you can try free for 30 days. The free edition of SwisSQL Database Stored Procedure Migration Tools is available for download at http://www.swissql.com . Pricing for the Professional Edition is based on the number of lines you need to migrate and starts at $495. For other product information and pricing, contact AdventNet at 925-924-9500 or [email protected]

  • Get File-Based Continuous Data Protection

  • TimeSpring Software announced TimeData, file-based continuous data-protection software. The software automatically captures all changes to application data in realtime so that transaction-dependent organizations--such as those involved in the financial, retail, healthcare, and manufacturing industries--can recover from data loss, corruption, or security breaches in minutes. The software makes it possible to perform offline processing such as reporting, analysis, or application testing by capturing all application and file metadata changes as they occur. The product checks all data changes for consistency and time stamps them in a secure repository built on a standard Windows server. The data repository provides a complete change history and audit trail for application data that's easy to access and use. Pricing for TimeData starts at $1295 per file server. For more information, contact TimeSpring Software at 408-834-8966 or 888-375-7634.

    Contact Us

  • About the newsletter—[email protected]
  • About technical questions—http://www.sqlmag.com/forums
  • About product news—[email protected]
  • About your subscription—[email protected]
  • About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]
  • SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

    Manage Your Account You are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail

    To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.

    View the SQL Server Magazine Privacy Policy.

    SQL Server Magazine is a division of Penton Media, Inc., 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2004, Penton Media, Inc. All Rights Reserved.

    Hide 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.