SQL Server Magazine UPDATE, September 21, 2006--Eliminating Stored Procedures? Look Before You Leap

In This Issue:
With the proliferation of blogs, many of which contain excellent advice from knowledgeable experts, how do you know what advice is right for you? Savvy SQL Server professionals avoid absolutes and make sure they know the implications of choices they make in their own environments.

New Instant Poll: Testing for Recovery
"Have you tested your disaster-recovery plan?" Go to the SQL Server Magazine home page ( http://www.sqlmag.com ) and submit your vote for:

Yes, we test our plan regularly.
Yes, we tested our plan when we developed it.
No, we have a plan but haven’t tested it.
No, we’re still working on our disaster-recovery plan..
No, we don’t have a plan.

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.

Our Sponsors, Who Help Support the Free Delivery Of This Newsletter:

September 21, 2006

1. Perspectives

Eliminating Stored Procedures? Look Before You Leap

2. SQL Server Watch

Roadshow Session Compares Data Mirroring Scenarios for Oracle and SQL Server
This Month’s Focus: Disaster Recovery—Putting Together Your High Availability Puzzle

Product Watch: TulaSoft

3. Hot Articles

Q&A: Looking for AdventureWorks?
Editorial: Making Sense of the Editions
In a Nutshell: A Couple Book Reviews
Hot Threads: T-SQL and SQL Server 2005 Management Tools

4. Events and Resources

SQL Server Magazine Connections Conference
ORACLE AND SQL SERVER… BETTER TOGETHER?
Free Compliance E-Book
Use Virtualization Technology to Reduce TCO

5. Featured White Paper

Streamline the Compliance Lifecycle

6. Announcements

SQL Server Performance Tips, Articles, and Forums
Discounted Offer for the SQL Server Magazine Master CD

7. Web Community

http://www.sqlmag.com

Sponsor: Microsoft
Find A Great Deal on Training for Microsoft SQL Server 2005
SQL Server 2005 helps organizations get the most value from their data management investments. And now you can get expert training from Microsoft Certified Partners for Learning Solutions to learn the skills you need to get the most from SQL Server 2005. Best of all, you can find special limited-time offers on training at http://www.sqlserver2005offers.com.

1. Perspectives

Eliminating Stored Procedures? Look Before You Leap
by Brian Moran, [email protected]

A few weeks ago, one of my clients asked me a weird question. The client, who is relatively new to SQL Server, said, “Hey, I just read online that you should never use stored procedures when developing a SQL Server application. The advice was on the home page of a Microsoft MVP, so I figured it would be pretty accurate. What do you think? Should we avoid using stored procedures in the application we’re building?”

The first reaction of the consultant in me was, “Sweet! Without advice like that confusing my clients, I might have to get a real job.” But then, the more compassionate mentor in me rushed to sit my client down and explain the birds and the bees of stored procedures and not believing everything that you read online. Of course, I started the conversation by making it clear that the answer to any question that starts, “What’s the best thing to do” is invariably, “It depends.”.

Still, I was intrigued that my customer had said his advice had come from a Microsoft MVP (from the developer community, not a SQL Server MVP) and that he’d seen similar postings on other Microsoft-related development sites. I read some of the articles that my customer was referring to and I was surprised to see that I agreed with many of the insights that the “just say no to procedures” crowd was advocating. However, I disagree with the conclusion of “avoid procedures like the plague” when developing SQL Server applications.

I don’t have enough space to explore the ins and outs of the developers’ arguments and what I believe to be their somewhat faulty conclusions. In fact, the main point of this week’s editorial isn’t to take a firm stand on the use of procedures (off the record, I, of course, use them all the time). Instead, I wanted to make a point about acting on technical advice without understanding the implications of taking the advice.

I’d like to share an anecdote from a project I worked on several years ago, when “agile” development was first becoming popular. It will help me make my point. During the project, I was called in to “fix” a performance problem, which turned out to be related to the agile, object-oriented design of the shopping-cart style application. The developers had created an elegant, encapsulated, easy-to-use-and-maintain development library for managing the shopping-cart. Unfortunately, they took the “object-oriented” idea a bit too far. Every item (e.g., an office chair) had multiple properties (e.g., color, height), and a round trip to the server was required to get the data associated with each property for each item. The main search page of the application would often show as many as 20 items, and each item often had more than 20 properties. So, refreshing a single page during a search often required more than 400 round trips to the server. Needless to say, that didn’t scale well. The basic concepts the developers were trying to design around made sense, but they chose form over function. They created a seemingly elegant application-level architecture with no understanding of what effect those decisions would have on the database tier.

Similarly, the reasonsing behind the blog posts my client was reading are reasonably sound. However it’s dangerous to boil down any set of advice into “This is always the right way,” especially when you don’t fully understand the effect of your absolute rule.

And that’s the primary point I want to make this week. The best initial answer to almost any performance or architecture question is “It depends.” From there, you need to explore the facts and determine what makes sense for a given solution, pulling from your bags of tricks as needed. It’s dangerous to say, “This is always the way to do something,” and it’s dangerous to act on advice unless you understand the implications of that advice. Do you really know the effect of eschewing stored procedure usage for all eternity? Well, until you can make a fully informed and educated decision, I caution you to look before you leap.

Sponsor: AppDev
NEW! Free Training Download or CD—SQL Server 2005: Reporting Services
Get 3 FREE hours of award-winning AppDev training from our latest course—"SQL Server 2005: Reporting Services." You'll receive step-by-step instruction by industry expert Paul Litwin, plus printable courseware—a $115 value. Get your free download or CD now!

2. SQL Server Watch

Roadshow Session Compares Data Mirroring Scenarios for Oracle and SQL Server
Oracle and SQL Server DBAs looking for the optimal high-availability solutions will get a crash course in failover clustering, database mirroring, and transactional replication from Scalability Experts at the "Managing Your Cross-Platform Data" roadshow coming to Chicago next Tuesday, September 26. Sponsored by Oracle Magazine, Windows IT Pro, HP, Intel, and Microsoft, the show features information about the Windows 64-bit platform for database computing, an under-the-hood tour of Oracle and SQL Server, an overview of deploying highly available Oracle and SQL Server databases, guidelines for using SQL Server business intelligence on the Oracle platform (presented by Douglas McDowell of Solid Quality Learning), and a research-based session about how IT professionals can prepare for the changing database job market.

This coming Tuesday, September 26, the roadshow visits Chicago. There’s still time to get in, so sign up today! Between now and October 24, you can also catch the roadshow in St. Louis; Houston; Irvine, California; San Francisco; Phoenix; New York; Atlanta; and Seattle. For complete agenda and speaker information, go to: http://www.windowsitpro.com/roadshows/sqloracle/

This Month's Focus: Disaster Recovery—Putting Together Your High Availability Puzzle
In SQL Server 2005, Microsoft not only added database mirroring to achieve higher availability but also substantially improved existing availability features. Read how to keep your data available today at http://www.sqlmag.com/Articles/ArticleID/49037/49037.html

Product Watch
by Blake Eno, [email protected]

Compare and Synchronize Databases and Schema
TulaSoft announced SQL Examiner 1.4 and SQL Data Examiner to address your needs for database comparison and synchronization. SQL Examiner lets you compare the schema of two SQL Server databases and locate the changes in tables, views, stored procedures, or any other object in the database. When you use the product with SQL Data Examiner, you can also compare and synchronize data differences between the two databases. And now, SQL Examiner can compare and synchronize Common Language Runtime (CLR) objects that SQL Server 2005 supports. Pricing for SQL Examiner and SQL Data Examiner starts at $199.95. For more information, contact TulaSoft.

Sponsor: Idera
Get SQL diagnostic manager and a Free SQL Server Superhero T-Shirt!
Don't let locks, blocks, freezes, or stops get in your way—solve SQL Server performance problems fast with Idera's award-winning SQL diagnostic manager! Chosen the BEST SQL Server performance monitoring solution by SQL Server Magazine, SQL diagnostic manager will save you hours of time, help you diagnose and solve SQL Server slowdowns like worst performing procedures, and will keep you in touch with the status of your SQL Servers 24 x 7 with customized alerts. Download it before September 30, fill out a survey, and we'll send you a free SQL Server Superhero T-shirt!

3. Hot Articles

Q&A: Looking for AdventureWorks?
by Brian Moran, [email protected]

Q: Help! I just realized that installing SQL Server 2005 doesn't automatically install Microsoft sample databases, such as AdventureWorks, or related sample applications. How do I install them?

A: Read the answer to this question today.

Editorial: Making Sense of the Editions
SQL Server 2005 clearly delineates the features of the different editions. Customers need to choose wisely for their needs. Read this article today and post your comments at http://www.sqlmag.com/Articles/ArticleID/92800/92800.html.

In a Nutshell: A Couple Book Reviews
This week, Kevin Kline points to reviews of his latest book “Pro SQL Server 2005 Database Design and Optimization,” co-authored with Louis Davidson. Check out the book and post your comments today at http://www.sqlmag.com/article/articleid/93497/sql_server_blog_93497.html.

Hot Threads:

4. Events and Resources

SQL Server Magazine Connections Conference
Now in its sixth year, SQL Server Magazine Connections returns November 6-9 to Mandalay Bay Resort in Las Vegas. Get down to business today with SQL Server 2005. Interact with and learn from an all-star lineup. Register for one event and attend sessions of the concurrently run events for FREE!

ORACLE AND SQL SERVER... BETTER TOGETHER?
Learn about optimizing 64-bit database computing, business intelligence for SQL Server and Oracle, high-availability proof points for database computing, and the implications of architectural differences between Oracle and SQL Server. Coming to 12 US cities in September and October. Special price—sign up now for just $49!

How will compliance regulations affect your IT infrastructure?
Help design your retention and retrieval, privacy, and security policies to make sure that your organization is compliant. Download the free eBook today!

Total Cost of Ownership—TCO.
It's every executive's favorite buzzword, but what does it really mean and how does it affect you? In this podcast, Ben Smith explains how your organization can use virtualization technology to measurably improve the TCO for servers and clients.

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

5. Featured White Paper

The average enterprise spends nearly $10 million annually on IT compliance. Download this free whitepaper today to learn how to streamline the compliance lifecycle and dramatically reduce your company's compliance costs!

Hot Spot: Double-Take Software
Enhance SQL Reporting and Increase Server Performance
Increase your SQL production server’s performance by offloading Reporting Services to a secondary server. Gain additional insight into your important business data quickly and effectively. Download the whitepaper today!

6. Announcements

SQL Server Performance Tips, Articles, and Forums
Get hundreds of free tips and articles on SQL Server performance tuning and clustering. And get quick and accurate answers to your performance- and cluster-related questions in our forum. All from the SQL Server performance and clustering authority: SQL-Server-Performance.com.

Discounted Offer for the SQL Server Magazine Master CD
Save 50% on the SQL Server Magazine Master CD! Order now and get access to the entire SQL Server Magazine article database on CD. Subscribe now.

7. Web Community

About the newsletter—[email protected]
About SQL Server Perspectives—[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!

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

Comments

Plain text