SQL Server Magazine UPDATE, February 13, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections

http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ0ggP0AW

THIS ISSUE SPONSORED BY

ebook: Optimize SQL Server Performance

SQL Server Magazine University e-Learning Center
(below COMMENTARY)

Experience the Benefits of Real Time Monitoring
(below NEWS AND VIEWS)

SPONSOR: EBOOK: OPTIMIZE SQL SERVER PERFORMANCE

The free ebook "The Definitive Guide to SQL Server Performance Optimization" is now available for download. This ebook describes, in great detail, SQL Server concepts and techniques that can be leveraged by SQL Server DBAs and application developers to optimize response time. Whether your challenge is index usage, SQL statement tuning, lock resolution or simply understanding the dynamics of a SQL Server database ... this free reference book provides the answers.
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ03HV0AE

February 13, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. HOT RELEASES (ADVERTISEMENTS)

7. NEW AND IMPROVED

8. CONTACT US

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

You were right. I was wrong. There, that wasn't so hard to say! In last week's commentary ("After the Slammer," I said that professional administrators should be ashamed for not applying the patch that blocks the SQL Slammer worm, perhaps the fastest spreading worm in Internet history. But reader comments I received this week made me realize that I owe some professional administrators an apology. My comments grossly oversimplified the problem. The reader letters reminded me that applying the latest security hotfix isn't as easy as running a setup during your morning coffee break. Last week, I painted DBAs as the bad guys. I was off the mark.

It's true that Slammer wouldn't have spread as quickly as it did—if at all—if most SQL Servers would have had the patch installed. (Last week's SQL Server Magazine Instant Poll showed that 17 percent of respondents still hadn't installed SQL Server 2000 Service Pack 3 (SP3) or the Slammer patch even after the worm attack.) However, the patch isn't a one-size-fits-all solution.

The first obstacle many DBAs face when Microsoft releases a patch or update is that such updates are typically difficult and time-consuming to install. One reader recalled, "The original patch that Microsoft released did not include an installer. I remember back when I first downloaded and installed this patch. It involved manually copying a set of files into different folders, and then running one or more scripts (it's been a while, so I don't remember all the details). Microsoft re-released the patch later at some point (I don't know when) with an installer. I found this out when I read the bulletin after the Slammer had hit—they specifically state that they had re-released the patch with an installer... Installing the original patch was a pain in the neck, and I am sure many administrators skipped it just for this reason."

A second obstacle many readers describe is that blindly applying patches and updates can create problems instead of solving them. As one reader noted, "Most business organizations have a total environment to protect, with many applications, servers, and desktops that are [either] interconnected or widely unrelated. Microsoft patches in the past have had negative effects on production systems and need to be thoroughly tested and proven before 'professional' technical support people apply them. The Microsoft development mentality is that any errors will be proven in live situations, so frequently the patch is worse than the disease it is designed to prevent."

A third obstacle that has increased in these times of tight budgets is that many IT groups are understaffed and overworked. One reader provided a particularly dramatic example: "We have a network of hundreds of SQL Servers, shortly to grow to almost 2000. On top of all these SQL Servers, we have a mainframe running DB2, dozens of UNIX systems running UDB and Oracle—and just four DBAs. This is a business decision—the company has decided that it will not invest money in the DBA function [because] it does not see sufficient value. Rather, it prefers to take chances over issues like [the Slammer worm]. My colleagues and I have nothing at all to be ashamed of, and I suspect the same applies to many other DBAs in other companies as well."

Applying patches becomes infinitely more complex in an environment that requires 24 x 7 availability and supports dozens, hundreds, or thousands of servers. So although DBAs know about the risks, compelling technical and business reasons sometimes keep them from applying patches.

I oversimplified the problem when I blamed administrators for the spread of Slammer, and I don't want to oversimplify again this week by letting the DBA community off the hook entirely. However, these responses show that DBAs face serious problems in keeping their systems up-to-date. People are choosing to ignore certain classes of best practices—not because they want to or because they're lazy or indifferent but because they see the medicine as worse than the disease.

Fixing these problems requires a new way of looking at the application and management of best practices. And the solution must include input from the technical community, software vendors, companies that use the products, and the members of overworked IT groups.

Ultimately, software must evolve so that applying a patch or adhering to best practices is easier. I'll expand on this topic in an upcoming edition of SQL Server Magazine UPDATE, but I want more feedback from you first. How can software vendors such as Microsoft engineer software to make it easier to adhere to best practices? Is it their responsibility? Do you know of simple ways to solve the problems that the reader excerpts above highlight? Tell me what you think.

SQL SERVER MAGAZINE UNIVERSITY e-LEARNING CENTER

SQL Server Magazine University (SSMU) e-Learning Center delivers LIVE quality technical training to your desktop. Learn practical SQL Server skills from MCTs, MVPs, SQL Server Magazine authors, and SQL Server gurus, including industry leaders such as Kalen Delaney, Morris Lewis, Brian Moran, Itzik Ben-Gan, and others you've come to trust. Finally, a complete training program led by SQL Server experts with real-life business application experience—not just theory, but real, practical application! For an online schedule of SQL Server Magazine University e-Learning Center events, please click on the following links:
Training Courses: http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ067v0AW

Web Seminars: http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ07YW0Aa

2. SQL SERVER NEWS AND VIEWS

(contributed by Paul Thurrott, [email protected])

At the VSLive! 2003 developer conference in San Francisco this week, Microsoft will demonstrate the next two Visual Studio .NET versions, including Visual Studio .NET 2003 (code-named Everett), which will ship April 24 with Windows Server 2003. The next release of Visual Studio (VS), code-named Whidbey, will ship in early 2004 with the next SQL Server release (code-named Yukon). After that, the next VS release will ship with Longhorn, the next Windows release, in late 2004 or early 2005.

"Visual Studio .NET 2003 is a reassurance for customers," Dan Hay, lead product manager in Microsoft's Developer Division, told me recently. "The tools are improving, and we're listening to feedback. But [the product isn't] improving or changing dramatically. The Yukon release of Visual Studio is the next release, and it's a major, substantial release."

The Yukon SQL Server release will enable a variety of new technologies, products, and services at Microsoft, so it makes sense that the company is basing its next-generation development tools on this important platform. As a core database engine, Yukon will offer key programmability improvements, including integration with the Microsoft .NET runtime environment, the ability to use any .NET programming language—including Visual Basic .NET and Visual C# .NET—to code stored procedures, and a much simpler way for developers to create new user-defined data types.

But Yukon is much more than a database engine. Microsoft has been slowly working toward the promise of "information at your fingertips," a concept that Microsoft Chairman and Chief Software Architect Bill Gates introduced almost a decade ago, and Yukon will finally provide the underlying technology to make the concept possible. First, Microsoft will integrate Yukon with Longhorn and create a new file system called Windows Future Storage (WinFS). "We've always had the vision of abstracting data away, and people just want to get to their data and work with it," said Tom Rizzo of the SQL Server team. "Windows is the way that they will interact with their data. So we're going to make the experience richer and more easily searchable. The data should find the user."

Second, Microsoft will roll Yukon technology into a variety of products, most of which will probably rely on the new file system's underlying support. These products include Active Directory (AD), the post-Titanium Exchange Server release, and any other product that includes a data store of some type.

To take advantage of Yukon's gains, next year's VS release will include massive upgrades to the product's core Web services and data-acquisition capabilities, as well as improvements for mobile application development. Microsoft will also ship a Visual Studio .NET 2003-based development environment for Microsoft Office 11, which will replace the macro/scripting environment in earlier Office suites.

SQL Server Magazine is collecting feedback to use in a redesign of our Web site. What do you like most, and what do you dislike most, about the site? How could we make the site easier to use and navigate? How could we improve the look and feel of the site as well as the content it provides? What do you use the site for? Do you use the search engine, and if so, what do you use it to find? Do you use the navigation tabs for finding articles by a certain author, a certain topic, or a certain department title? These are just a few questions to get you started! We're eager for your feedback, and look forward to making the site even more valuable for SQL Server professionals. Please post your feedback in the Web site's Peeves and Praises forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=27&threadid=12649

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Did the Slammer/Sapphire worm directly affect your network, connectivity, or computerized activities?" Here are the results (+/- 1 percent) from the 397 votes:

The next Instant Poll question is "Are you considering consolidating SQL Servers in the next 12 months?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, we've already decided to consolidate, 2) Yes, we are thinking about it but need more information, 3) We've researched it and decided not to consolidate, or 4) No, we aren't looking at consolidation.
http://www.sqlmag.com

SPONSOR: TNT SOFTWARE

A proactive DBA installed ELM Enterprise Manager 3.0 on his critical servers to assess the benefits of real time monitoring. Within days, EEM 3.0 paged him when access to a confidential table was denied, emailed him when his SQL Server backup hung, and automatically restarted a failed SQL service. Shortly thereafter, EEM 3.0 was purchased and fully deployed. To experience the benefits of real time monitoring, download your FREE 30-day copy of ELM Enterprise Manager today.
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ07nY0Ax

3. READER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])

Congratulations to Sergey Koshkin, senior software developer for Softmatics, Inc., in Syktyvkar, Russia, and Quentin Ran, an independent consultant in Houston, Texas. Sergey won first prize of $100 for the best solution to the February Reader Challenge, "Reporting Sales." Quentin won second prize of $50. You can find a recap of the problem and the solution to the February Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=37621

Now, test your SQL Server savvy in the March Reader Challenge, "Recent Orders" (below). Submit your solution in an email message to [email protected] by February 20. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Jack is a database programmer at a company that sells products online. He writes SQL queries and stored procedures to generate reports based on user orders. He needs to provide a report that lists the two most recent orders placed by each user. The details of each order are stored in a table that the following code creates:

CREATE TABLE order_details (userid     int NOT NULL,ordernum   int NOT NULL,orderdate  datetime NOT NULL,shipdate   datetime NOT NULL,PRIMARY KEY ( userid, ordernum ),UNIQUE ( userid, orderdate )      )— Sample data:INSERT INTO order_details VALUES( 1, 1, '7/1/01', '7/5/01' )INSERT INTO order_details VALUES( 1, 2, '7/10/01', '7/7/01' )INSERT INTO order_details VALUES( 1, 3, '7/5/01', '7/2/01' )INSERT INTO order_details VALUES( 2, 4, '8/1/01', '9/5/01' )INSERT INTO order_details VALUES( 3, 5, '10/10/01', '10/7/01' )INSERT INTO order_details VALUES( 3, 6, '10/1/01', '10/2/01' )INSERT INTO order_details VALUES( 3, 7, '10/24/01', '10/30/01' )INSERT INTO order_details VALUES( 3, 8, '9/24/01', '9/30/01' )

Help Jack write a query that will return a row that contains the order number, ship date, and order date of the two most recent orders for each user.

4. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

Looking for 3 to 4 days of technical drilldowns into Microsoft SQL Server? Want an opportunity to interact live with SQL Server Magazine writers and also with Microsoft product architects? Register today for SQL Server Magazine Connections and get free access to Microsoft ASP.NET Connections and Visual Studio Connections!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ0ggP0AV

The Wilshire Meta-Data Conference and DAMA International Symposium will be April 27-May 1, at the Renaissance Resort, Orlando, Florida. Join 1,000+ colleagues and 130+ speakers to learn the latest trends in meta data, XML, business rules, data modeling, architecture, business intelligence, and database administration. Early payment discounts expire March 14.
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ07nZ0Ay

5. RESOURCES

None of SQL Server's upcoming programming-productivity enhancements will have more impact on DBAs than the .NET Common Language Runtime (CLR). By integrating the .NET CLR with the upcoming Yukon release of SQL Server, Microsoft will finally boost SQL Server's programmability to a level that Oracle and IBM DB2 have enjoyed for years through their built-in integration with Java. This new capability, however, begs the question, "Which .NET language should DBAs learn?" Michael Otey's February SQL Server Magazine editorial, "A New Language," explores the most practical choices. You can read this article online at
http://www.sqlmag.com/articles/index.cfm?articleid=37517

When you create views, it's likely that users can and will use them appropriately. But do you know what you can't put in a view? And if something isn't allowed, is there a workaround? Even if you can put something in a view, should you do it? Can you update, delete, and insert data through a view? Is every SELECT statement valid within a view's definition, or will some SELECT statements generate an error and keep you from creating the view? In her T-SQL Tutor column "Viewer Advisory," Kimberly L. Tripp explores the limitations on the kind of SELECT statement that you can place within a view's definition and shares some good habits and things to avoid. This article is available online to readers who've registered for the free T-SQL Solutions Web site.
http://63.88.172.208/articles/index.cfm?articleid=37660

SQL-Newbie has a job that he wants to run nonstop forever, without returning a result to the SQL Agent. Offer your advice about whether this goal is possible and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=3&threadid=12826

(contributed by Brian Moran, [email protected])

Q. We plan to add two processors to an existing SQL Server 2000 machine. We don't need the extra processors to handle the SQL Server workload; we're adding the processors so that we can run a small application server on the machine. Do I have to buy SQL Server licenses for the extra processors even if we use the sp_configure "affinity mask" option to prevent SQL Server from using more than two processors at once?

A. The short answer is yes. You have to license all four processors even if your SQL Server is using only two of them. This requirement might not seem fair, but it's what the license agreement says.

Send your technical questions to [email protected].

6. HOT RELEASES (ADVERTISEMENTS)

Learn how to make the most of your tuning time by understanding all of the results returned by SHOWPLAN_TEXT and the Graphic SHOWPLAN tool. Download this FREE T-SQL Tuning Tips white paper today!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ07na0A6

New UltraBac v7.0.3, the popular backup and disaster recovery software, now includes built-in encryption, client side file compression, single file restore from image backups, and a new remote installer. Download a free live trial now!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePaz0FgQMn0BRZ07nb0A7

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Lumigent Technologies released Log Explorer 3.0, software that lets users identify SQL Server database problems and lets users recover information when crucial data is lost. Log Explorer uses SQL Server's transaction log to audit database activity, quickly recover data online, and salvage data when other techniques fail. You can view who made changes to database information and structure and when those changes were made. The software supports all binary large objects (BLOBs) so that you can recover more data. You can view, redo, and undo values for BLOBs. Log Explorer supports SQL Server 2000 and 7.0 and costs $995 per server for one copy of Log Explorer Enterprise Edition. Contact Lumigent Technologies at 978-206-3700 or 866-586-4436.
http://www.lumigent.com

Wonderware announced IndustrialSQL Server 8.0, historian software that captures detailed plant data in realtime so that companies can gain a better understanding of the manufacturing process and make improvements in product consistency and productivity. The software provides performance and scalability for industrial automation users. You can receive quick access to your history data. IndustrialSQL Server can serve companies as small as 100 tags and as large as 100,000 tags. For pricing, contact Wonderware at 949-727-3200.
http://www.wonderware.com

8. CONTACT US

Here's how to reach us with your comments and questions:

(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email

Thank you for reading SQL Server Magazine UPDATE.