SQL Server Express UPDATE--August 21, 2006:What's the CLR and Should I Care?

What's the CLR and Should I Care?

Features

From the Community: Backing Up to a Non-Default Directory

Check It Out: MSDE Not Supported on Vista

Resources and Events

Cross-Platform Data Roadshow

Microsoft TechEd: Developers

Consolidating SQL Server Deployments

Quest Software's SQL Server 2005 Backup and Recovery Challenge!

Featured White Paper

Business Communications Trends: Integrating Fax Servers in MFP Environments

Editors' Choice for Top SQL Server Express/MSDE Products

Exclusive Email Offers

Monthly Online Pass--only $5.95 per month!

Save $40 on SQL Server Magazine

========== Sponsor: AppDev ====================================

FREE SQL Server 2005 Training CD-ROM!
      Start learning SQL Server 2005 today with cutting edge training from AppDev. Get a FREE SQL 2005 training CD (a $115 value!) taken directly from our new course. Click the link below for your FREE SQL Server 2005 training CD.
      http://www.appdev.com/promo/RN00440

1. ==== Commentary ====================

What's the CLR and Should I Care?
      by Michael Otey, [email protected]

If you're just beginning to work with databases or you haven't kept up with all the new SQL Server 2005 features, then the relationship between the CLR and SQL Server 2005 Express is likely to be something of a mystery. Even if you're familiar with .NET programming, in which the CLR plays a major role, you still might be wondering how the CLR relates to a database.

Most .NET programmers know that the CLR is essentially a runtime layer that sits between your .NET application and the OS. The CLR's job is to translate .NET program-generated instructions into native Win32 instructions that Windows OSs can execute. The primary benefits that the CLR provides are increased application security and stability. In addition, the CLR helps to protect the OS from problematic applications that might have memory leaks or buffer-overflow exploits. The idea behind the CLR is not new. Java, and its associated Java Virtual Machine (JVM) layer, was the first programming language to employ this type of architecture.

So how does the CLR work with SQL Server Express? The CLR is integrated with all SQL Server 2005 editions, including SQL Server Express. Creating database objects by using CLR objects provides some key advantages. First, it lets you create database objects--such as stored procedures, triggers, and functions--by using any .NET language such as Visual Basic (VB) and C#. Before the CLR was introduced, you had to use T-SQL commands to create database objects. Although T-SQL has excellent set-oriented data- retrieval capabilities, the .NET languages are better at handling complex logic and mathematical operations. Using the CLR opens up additional capabilities in SQL Server database object programming that weren't available before.

Second, T-SQL is designed to be a relational-database-oriented language and it doesn't include the ability to access resources outside of a relational database. In other words, T-SQL can't do things like read and write to the file system or registry. To give SQL Server the ability to access resources outside of a relational database, Microsoft added extended stored procedures, which are essentially native Win32 programs that run within the same in-memory process space as the SQL Server engine. Extended stored procedures fill the gap by providing access to external resources. But because these procedures run in the same process as the database engine, a bug in an extended stored procedure can bring down the SQL Server engine--not an ideal situation. However, integrating the CLR into SQL Server Express solves both of these problems. CLR objects can access external resources such as the file system and registry, but because CLR objects run within the confines of the CLR, they're isolated from the SQL Server engine, so a bug can't affect the functionality of the core database engine.

Integrating the CLR into the SQL Server 2005 product line adds functionality to SQL Server Express and lets you create database objects that contain complex logic and access external resources. To take advantage of the CLR, you have to program CLR objects by using one of the .NET languages and you need to enable CLR functionality by using the SQL Server Surface Area Configuration tool. (For security reasons, the CLR is turned off by default). I hope I've demystified the CLR and you now realize that it's just another SQL Server development tool.

*********** SPI Dynamics ******************************

ALERT: How Hackers Launch SQL Injection Attacks
      It's as simple as placing additional SQL commands into a Web Form input box giving hackers complete access to all your back- end systems! Firewalls and IDS will not stop such attacks because SQL Injections are NOT seen as intruders. Download this *FREE* white paper from SPI Dynamics for a complete guide to protection!
      https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70160000000CZT8

*****************************************************

Editor's Note: Be a SQL Server Innovator!
      If you've developed a resourceful solution that uses SQL Server technology to solve a business problem, you qualify to enter the 2006 SQL Server Magazine Innovators Contest! Grand- prize winners will receive airfare and a conference pass to SQL Server Magazine Connections in Las Vegas, November 6-9, 2006, plus more great prizes and a feature article about the winning solutions in the January 2007 issue of SQL Server Magazine. Contest runs through September 1, 2006, so enter today!
      http://www.sqlmag.com/awards/?type=innovator

2. ==== Features =======================

From the Community: Backing Up to a Non-Default Directory

Author's Note: I received an interesting question from a reader about backup and restore and thought I'd share the information. The Jump Start column will be back in the next edition of the newsletter.

Q: I tried to back up my database by using T-SQL commands. However, when I entered the following code to back up my database to a non-default directory, the process failed.

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup', 'C:testing.Bak'
BACKUP DATABASE testDB TO testing_Backup

What should I do to back up and restore my database from a non- default directory?
-- Chau Cheng Chai

A: You raise an interesting question, Chau Cheng Chai. SQL Server 2005 Express is not restricted to backing up to just the default directory; you can back up to other directories as well. You didn't send me the error message you received while attempting the backup operation, but I can tell you that the two most likely causes of backup and restore errors usually pertain to the database or file-system permissions or locations. In your code, you attempted to back up your database to the root directory. The sp_addumpdevice can't use the root directory as a backup target. Instead, you should create another directory, for example c:sqlbackup. Try the following code:

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'c:sqlbackuptesting.bak'
BACKUP DATABASE testDB TO testing_Backup

Hope this helps, and as always it's great to hear from our readers.
-- Michael Otey, [email protected]

***** HOT SPOT: InterCerve ************************

Everything you need for a small- to mid-sized SQL Server environment
      The award-winning power of sqlSentry is now available at a lower price point--$395 per instance. That's a 60 percent savings!
      http://www.sqlsentry.net/sqlsentry-editions-standard.asp?ad=SS_Mag_HS_2006_08_21_B

******************************************************

EDITORS' NOTE: Regional Events Cover Four Key Interoperability Topics
      Are you a Windows fan, a UNIX diehard, or a Linux lover? Check out TechX World, an OS-agnostic event designed to give you the insider's tips on coping with your "Windows Plus" world. You'll get to spend a day with technical experts Michael Otey, Gil Kirkpatrick, Dustin Puryear, and Randy Dyess.
      Designed specifically for IT professionals who work in a "Windows Plus" environment, TechX World is a four-track, one-day event featuring information about OS interoperability, data interoperability, directory and security integration, and virtualization. The content will focus on interoperability tips to help make disparate systems work well together.
      The regional event series will visit four cities between October 24 and November 2, including Washington DC, Chicago, Dallas, and San Francisco. Attendees who register before August 31 will receive early bird pricing and a one-year subscription to Windows IT Pro. At $129 per person for four tracks and a full day of learning, it's worth sending the entire team to make sure you cover all the sessions. For complete agenda and speaker details, go to
      http://www.techxworld.com/registration/index.cfm?code=

Check It Out: MSDE Not Supported on Vista
      by Michael Otey, [email protected]

If you're currently running Microsoft SQL Server Desktop Engine (MSDE) and you're planning to upgrade your desktop OS to Windows Vista, you might want to also upgrade from MSDE to SQL Server 2005 Express before your Vista migration because Microsoft won't be supporting MSDE on Vista. For more information, check out Mike Wachal's blog at
      http://blogs.msdn.com/sqlexpress/

3. ==== Resources and Events ===========

Cross-Platform Data Roadshow
      Oracle professionals will cover key concepts about Oracle and SQL Server in enterprise database computing. This event provides invaluable information including: benefits of 64-bit computing on the Windows platform, SQL Server BI for Oracle, high-availability proof points for SQL and Oracle and much more.
      http://www.windowsitpro.com/roadshows/sqloracle/?code=0823emailannc

Microsoft TechEd: Developers, 7 to 10 November 2006, Barcelona, Spain
      Four days that will take you months ahead of the game! Get deep-dive technical training, information, and community resources focusing on building software solutions with Microsoft development tools. Visit the Web site for further information and register before the Early Bird deadline of 29 September 2006 to save 300 euros.
      http://www.microsoft.com/europe/teched-developers/

Consolidating SQL Server Deployments
     Does your SQL Server deployment suffer from low utilization rates, suboptimal availability, and management challenges? Learn to overcome these difficulties by deploying a database utility that can help lower your cost of ownership by 70% and can react to changes within 30 seconds. Live Web Seminar: Tuesday, August 22
      http://www.sqlmag.com/go/seminars/polyserve/consolidatesql/?partnerref=0821emailannc

Quest Software's SQL Server 2005 Backup and Recovery Challenge!
     Test your SQL Server 2005 Backup and Recovery Skills--you could win a $100 American Express Gift Card! Take the quiz now!
      http://popquiz.sqlmag.com/quest

4. ==== Featured White Paper ============

Integrating Fax Servers in MFP Environments
      Did you know that wasteful processes can drive the cost of document management and output to as high as 10 to 15 percent of your company's annual revenues? Download this free white paper today and find out how you can use fax solutions to achieve cost control, security and compliance, increased workflow and more.
      http://www.windowsitpro.com/go/whitepapers/Faxback/faxing?code=0821featwp

===================================

WANTED: Your reviews of products you've tested and used in production. Share your experiences and ratings of products to "[email protected]" and get a Best Buy gift certificate.

5. Editors' Choice for Top SQL Server Express/MSDE Products
      by Blake Eno

SQL Server Magazine recently announced the winners of its 2006 Editors' Choice Awards. In the SQL Server Express/MSDE category, Vale Software Express Agent was the platinum winner. This product lets you create and schedule jobs that can automatically perform routine functions such as backup maintenance and database backup. The gold winner, Layton Technology's Layton Database Manager, provides a graphical database-management console that lets you create, view, and manage your databases and their objects. You'll find more information about these products and their functionality and the complete list of winners in the 2006 Editors' Choice Awards at
      http://www.sqlmag.com/Articles/ArticleID/50660/50660.html

6. ==== Exclusive Email Offers ====

Monthly Online Pass--only $5.95 per month!
      Includes instant online access to every article ever written in SQL Server Magazine, plus the latest digital issue. Order now:
      https://store.pentontech.com/index.cfm?s=9&promocode=eu2168um

Save $40 on SQL Server Magazine
      Subscribe to SQL Server Magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2300 helpful SQL Server articles. This is a limited-time offer, so order now:
      https://store.pentontech.com/index.cfm?s=9&promocode=eu2168us

==== Contact Us ====

About the [email protected]
About the [email protected]
About technical questions--
http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product [email protected]
About your [email protected]
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected]

SQL Server Express 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.
      https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

Manage Your Account
You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
      http://www.sqlmag.com/email

View the SQL Server Magazine Privacy Policy.
      http://www.sqlmag.com/aboutus/index.cfm?action=privacy

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

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