SQL Server Magazine UPDATE, June 17, 2004--Support Policy

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

Download Idera's FREE Performance Monitor for SQL Server

SQL Server Magazine Connections: Fall Dates
(Below COMMENTARY)

June 17, 2004—In this issue:

1. SQL Server Perspectives

2. News and Views

3. Announcements

4. Resources

5. New and Improved

Sponsor: Download Idera's FREE Performance Monitor for SQL Server

Idera's SQLcheck provides real-time performance monitoring for SQL Server, plus your server's hardware and operating system. The new version 2.0 features a secure screensaver, and a user-interface design that enables easy visibility of diagnostic information from a distance. Get the information you need to efficiently manage your SQL Server environment. Download SQLcheck now!

1. SQL Server Perspectives

(contributed by Brian Moran, news editor)

Last month, Microsoft announced an enhanced product-support policy that guarantees customers support for at least 10 years (read the full press release). However, the 10 years of support that the new Microsoft policy provides isn't as long as it appears. Although the new policy has some nice improvements, let's look at the real benefits for Microsoft customers—especially SQL Server users.

Microsoft's previous support policy provided 5 years of mainstream support, 2 years of extended support, and 8 years (or more) of online, self-help support. Mainstream support includes incident support (no-charge incident support, paid incident support, support charged on an hourly basis, and support for warranty claims), security-update support, and the ability to request non-security hotfixes. In the enhanced Support Lifecycle, which took effect June 1, the mainstream-support phase remains at 5 years. Extended support now includes paid support and security updates at no additional cost. But you must purchase an Extended Hotfix Support contract to receive non-security-related hotfix support and per-fix fees also apply. Microsoft won't accept requests for warranty support, design changes, or new features during the extended-support phase of a product. The new extended-support phase increases by 3 years to 5 years total. And for the online, self-help piece, Microsoft says it won't delete information from its Web site and Knowledge Base concerning "old" products for 10 years—a 2-year increase from the previous 8-year limit. But you won't be able to get support from Microsoft engineers during this period, so I think it's misleading to say that the product is supported for 10 years. (You can find more details about the Support Lifecycle policy regarding hotfixes, service packs, security updates, and particular products at http://www.microsoft.com/lifecycle/.)

Note that SQL Server 2000 users get an extension to the 5-year mainstream-support phase. Microsoft promises mainstream support for SQL Server 2000 for 2 years after the release of SQL Server 2005. Extended support will then continue for 2 years after the end of mainstream support. However, SQL Server 7.0 users don't have as much time to upgrade. Mainstream support for SQL Server 7.0 ends December 31, 2005. Microsoft says that extended support for SQL Server 7.0 "will end December 31, 2010, or two years after the next version of this product is launched, whichever length of time is longer."

Mainstream support is the highest level of support for a product. During the mainstream-support phase, Microsoft provides hotfixes, releases service packs, fixes bugs, and (of course) takes your money if you run into a problem and need help from Microsoft solving it. The extended support is also a good quality of support, and the time increase there is nice. Realistically, self-help isn't much of a support policy. Microsoft says that "by using Microsoft's online Knowledge Base articles, FAQs, troubleshooting tools, and other resources, many customers can quickly resolve their issues without contacting Microsoft directly." But you can get as good (or better) support from the SQL Server Magazine Web site, Google, or other third-party sites.

Sponsor: SQL Server Magazine Connections: Fall Dates

The SQL Server Magazine Connections conference is coming to Las Vegas on November 7-10, along with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Register early and receive access to all three conferences for one low price and get the best early bird discount. Call 800-438-6720 or 203-268-3204.
    http://lists.sqlmag.com/cgi-bin3/DM/y/egMu0FgQMn0BRZ0ggP0AT

2. News and Views

Microsoft has released a hotfix for an invalid cursor state that occurs after you apply hotfix build versions between 8.00.0859 and 8.00.0875 in SQL Server 2000 Service Pack 3 (SP3). Applications that execute a stored procedure that uses a SET NOCOUNT ON statement and that return one or more result sets following an informational message, a RAISERROR statement, or a PRINT statement, might have various failures in processing results. The error message differs depending on what client API you're using. Common error messages include "ADO: Operation is not allowed when the object is closed" and "ODBC: [Microsoft][ODBC SLQ Server Driver]Invalid cursor state." This bug also affects the Design Table feature in Enterprise Manager. To learn more about the problem and the supported hotfix, read the Microsoft article "FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or later in SQL Server 2000"

The voting has closed in SQL Server Magazine's Instant Poll for the question, "What SQL Server 2005 new feature or enhancement are you most excited about?" Here are the results (+/- 1 percent) from the 171 votes (deviations from 100 are due to a rounding error):

The next Instant Poll question is "Do you think Microsoft's new Support Lifecycle will benefit your organization?" Go to the SQL Server Magazine Web site and vote for 1) Yes, 2) No, or 3) I don't know.
    http://www.sqlmag.com

3. Announcements

Introducing version 8 of the SQL Server Magazine Master CD. Subscribe today and get portable, high-speed access to all articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions. Let this helpful resource save you some time anywhere you are. Subscribe now and get 25% off!

SqlJunkies is your online community resource for original tutorial and how-to articles for developing applications with SQL Server 2000 and Yukon; peer-to-peer help and networking through discussion forums and newsgroups; technology tips and pointers from expert bloggers; and the latest in SQL Server-related events and news.
    http://lists.sqlmag.com/cgi-bin3/DM/y/egMu0FgQMn0BRZ0BFll0AZ

4. Resources

Common table expressions (CTEs), a new T-SQL feature in SQL Server 2005, give you expressive powers for writing T-SQL queries to manipulate hierarchies easily and efficiently. Some major advantages of CTEs are that they're ANSI SQL-99 compliant, they let you write shorter code than ever to manipulate hierarchies, and their internal implementation uses set-based queries rather than cursors and temporary tables. In his June T-SQL Black Belt column, "Cycling with CTEs," Itzik Ben-Gan looks at how to use CTEs to manipulate multiparent hierarchies such as a bill of materials (BOM) as well as how to detect cycles and sort siblings. Read this article today

Snipeet wants to capture records that an announcement field flags as 1. Snipeet needs to capture the records in an array-type format and display all of them, but a date range on display changes regularly based on the date and time field. Right now, a stored procedure is capturing the data, but Snipeet wants to know how to view the information in an array format that shows exactly how many records the stored procedure captures. Offer your advice and see what other people have said on SQL Server Magazine's Development forum at http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1670&threadid=122120

by Brian Moran

Q. SQL Server 2000's ability to automatically update statistics for my tables and indexes is enabled by default. How often does SQL Server perform this update, and does the action have a negative effect on performance?

A. The auto update statistics option can slow down your system, but don't disable the option simply because you're concerned that it might affect performance. More often than not, keeping this option enabled is the correct decision and will lead to substantially better performance. If you load a lot of data into a table during a peak processing period, SQL Server's automatic statistics update on that table might further degrade performance. In this case, you can disable the auto statistics option for the table during the load, then manually run UPDATE STATISTICS when the load is finished. However, you might find that certain queries begin to choose inefficient query plans if you've disabled the auto statistics option for the table, which could cause the statistics to no longer reflect the accurate distribution of data in the table and result in SQL Server choosing a suboptimal execution plan.

SQL Server 7.0 and earlier releases don't have the automatic update statistics option. With these releases, the most common solution to a sudden query-performance decrease is to update your statistics. I recommend leaving the auto update statistics option enabled unless you have a reasonably strong SQL Server performance-tuning background, can measure the before and after effect of disabling the option, and have benchmarks that prove disabling the option makes sense in your environment.

As for how often SQL Server performs this update, see the Microsoft article "INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work." This interesting article covers the algorithms SQL Server uses to choose when to automatically update statistics. The timing depends on the number of rows in the table, the number of rows affected by queries since the last statistics update, and whether the table is permanent or temporary.

5. New and Improved

(contributed by Dawn Cyr, [email protected])

VERITAS Software announced NetBackup 5.1, backup and recovery software that uses snapshot technology to maintain system availability during backups. The software's latest release provides expanded support for SQL Server backups. Through NetBackup's GUI, you can perform backups from a specific point in time or transaction-log mark without shutting down the database, and you can select specific database files, filegroups, or transaction logs for backup. The software supports backing up and restoring databases through multiple data streams. And you can archive the streams to separate devices or multiplex them to the same device. The new release also features enhanced security. Flexible access-control features let you customize security roles to meet your organization's needs, and you can use up to 256-bit encryptions of data as it comes off the client. NetBackup 5.1 supports SQL Server 2000 (64-bit and 32-bit versions), 7.0, and 6.5, as well as databases on UNIX, Linux, and NetWare platforms. Pricing for NetBackup 5.1 starts at $5000. For more information, contact VERITAS at 800-327-2232 or 650-527-8000.
    http://lists.sqlmag.com/cgi-bin3/DM/y/egMu0FgQMn0BRZ0KaT0Ax

Parasoft announced .TEST 1.6, unit-testing software that automatically tests classes written on the .NET Framework without requiring developers to write test scenarios or stubs. The software is fully integrated with Visual Studio .NET, so developers creating applications that work with SQL Server can automate realtime unit testing for code construction and functionality, regression testing, and test-report generation. In addition, the product performs module testing and provides complete code diagnosis and failure details. You can incorporate user-defined and automatically generated scenarios into your testing process, and the product includes a configurations manager that lets you customize testing for your environment. .TEST supports multiple data sources such as SQL Server, Microsoft Excel, and .csv files, so you can generate scenarios based on user-provided data. .TEST Professional Edition is available for a limited time at the special price of $895. The special price includes 60 days of free technical support. For information about the promotion, contact Parasoft at [email protected]. For other information, contact Parasoft at 626-256-3680 or [email protected].
    http://lists.sqlmag.com/cgi-bin3/DM/y/egMu0FgQMn0BRZ0BA1T0A6

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.
   http://www.sqlmag.com/sub.cfm?code=ssei211x1y

CONTACT US

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

Manage Your Account

You are subscribed as #EmailAddr#

To unsubscribe from this email newsletter, send an email message to mailto: #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.
http://www.winnetmag.com/email

Copyright 2004, Penton Media, Inc.