SQL Server Magazine UPDATE, October 24, 2002

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

THIS ISSUE SPONSORED BY

Need to Configure a Server for Microsoft SQL Server?
http://www.hp.com/solutions/mssql

Free Geo-Clusters Paper from Unisys
http://www.app1.unisys.com/simplify/sql/centerspread/
(below COMMENTARY)

Team Approach
http://www.ca.com/newallfusion
(below NEWS AND VIEWS)

SPONSOR: NEED TO CONFIGURE A SERVER FOR MICROSOFT SQL SERVER?

Check out the online ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000 from HP. This automated tool will help you determine an optimum hardware configuration for your database server—based on YOUR requirements. Through an interview process, a set of hardware configurations is developed using patented system sizing and configuration technology. Specific configuration information and performance recommendations are provided for each configuration. Developed by HP in our software integration lab, this tool supports the ProLiant server family and appropriate options. For more information, visit
http://www.hp.com/solutions/mssql

October 24, 2002—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. ANNOUNCEMENTS

4. HOT RELEASES (ADVERTISEMENTS)

5. RESOURCES

6. NEW AND IMPROVED

7. CONTACT US

1. COMMENTARY

Customers often ask me how long Microsoft will support their particular SQL Server release, but it hasn't always been easy to find or understand that information. Past support policies were geared to version-based support, which ties support to the release dates of future products. For example, in its RTM–1 release to manufacturing policy, Microsoft supported the current product and the immediately preceding release. Version-based support is easier for a software-maintenance team to plan for because they know the two code bases for which they must maintain their expertise. However, version-based support tends to be unpredictable for customers, who must try to predict ever-moving ship dates. When you combine the unpredictability of ship dates with different SQL Server releases that run on different OSs, support becomes difficult to master.

Last week, Microsoft showed its commitment to streamlining its product-support policies by rolling out its new Support Lifecycle policy. The policy, which replaces version-based product-support policies for most products, provides consistent and predictable timetables that are tied to a product's date of general availability. Now, companies can consider SQL Server product-support deadlines when they undertake their planning and budgeting processes. For business and development software, including SQL Server, the Support Lifestyle policy comprises the following three phases of support:

Microsoft tells me that customers should expect a regular release of service packs for SQL Server products during the first 3 to 4 years of mainstream support and a final service-pack maintenance rollup sometime near the end of the five years. Microsoft has no plans to release service packs during the extended support phase or beyond.

What does the new policy mean for SQL Server? Here are two examples. SQL Server 7.0 Service Pack 4 (SP4) mainstream support is scheduled to end on March 31, 2004, and mainstream support for SQL Server 2000 SP2 is scheduled to end on December 31, 2005. The optional extended support continues 2 years beyond those dates for both products, and Microsoft promises to provide online help resources for an additional 3 years beyond those dates. To find more about three-phase product support, as well as information about service-pack and security-patch support and custom support relationships, go to Microsoft's Product Support Lifecycle page at http://support.microsoft.com/lifecycle. You'll find product-support time frames for SQL Server at http://support.microsoft.com/default.aspx?scid=fh;en-us;obsoletesrvr .

Microsoft's new policy of tying support phaseout to a product's original launch date is a welcome improvement over earlier version-based policies that were difficult to interpret. For the SQL Server community, long-term product planning just got a little smoother.

SPONSOR: Free Geo-clusters paper from Unisys

FREE EXPERT ADVICE: There are no systems more important than database servers when it comes to high availability and business continuance. After all, your business could come to a halt if your databases were down for long. That's particularly true when it comes to applications that need data around the clock or around the world — especially your Internet based ones. And with security such a vital issue today, clustering and dispersing your Windows-based servers across distances is the best protection you can have. Let the experienced designer/engineers at Unisys show you how to do it successfully in this detailed, comprehensive white paper on Geographically Dispersed Clusters.
http://www.app1.unisys.com/simplify/sql/centerspread/

2. SQL SERVER NEWS AND VIEWS

David Litchfield of Next Generation Security Software discovered a vulnerability that lets users with PUBLIC permissions execute the xp_runwebtask extended system stored procedure and perform inserts, deletes, and updates on the Web tasks table, as reported by Ken Pfeil on the http://www.secadministrator.com/articles/index.cfm?articleid=27033Security Administrator Web site. Attackers can elevate their privileges by updating a database owner's Web task and executing the task through the stored procedure. Attackers could then, for example, run OS commands or add themselves to the SYSADMIN group. The vulnerability affects SQL Server 2000 and 7.0, Microsoft Desktop Engine (MSDE) 2000, and Microsoft Data Engine 1.0. Microsoft has released Security Bulletin MS02-061 (Elevation of Privilege in SQL Server Web Tasks) and recommends that affected users apply the cumulative patch mentioned in the bulletin. For complete information, go to http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS02-061.asp , and for details of the vulnerability discovery, go to http://www.nextgenss.com/advisories/mssql-webtasks.txt

Sponsored by Unisys
http://www.app1.unisys.com/simplify/sql/newsletter/

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How much time do you spend per day on storage administration?" Here are the results (+/- 1 percent) from the 187 votes:

The next Instant Poll question is "How familiar are you with XML?" Go to the SQL Server Magazine Web site and submit your vote for 1) I've used XML in a project, 2) I understand XML, including the data model, parsers, and technologies, 3) I'm just starting to learn about XML, 4) I've heard the name and know that XML is similar to HTML, or 5) I don't know anything about it.
http://www.sqlmag.com

SPONSOR: Team Approach

Trying to coordinate shared resources manually? Computer Associates International, Inc. offers the first multi-user modeling environment making large-scale modeling possible: the AllFusion(tm) Model Manager. Repeatable modeling success depends upon the ability to share results and actively promote standards. Easy to set up; platform- and network-independent; sits on your choice of relational databases. Visit
http://www.ca.com/newallfusion or call 1-800-783-7946.

3. ANNOUNCEMENTS

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

Check out what other companies are doing with SQL Server in the Real World Success Stories case-study supplement. Detailed accounts of successes are included from large and small companies using SQL Server—this is an opportunity to find pertinent and concrete ways to implement SQL Server. Click here!
http://www.itbuynet.com/pdf/1002-realworld.pdf

You can participate in this industry research panel by providing market input and commenting on trends in the industry. Industry-leading companies also sponsor research studies to tap into your expertise concerning your product needs and opinions. Express your views and make your voice heard in the SQL Server community.
http://www.up2research.com/sqlpanel

4. HOT RELEASES (ADVERTISEMENTS)

Develop .NET applications without knowing web technologies! Use the Model Driven Development (MDD) tool to build an advanced, multi-tier web application—including the user interface pages, logic flow, and the underlying SQL queries.
http://www.logixml.net/sqlmag.asp

Fragmentation is the root cause of many system problems, server slowdown being only one of them. Maximize server stability and uptime with Diskeeper Lite disk defragmenter for Windows(R). Download free Diskeeper Lite now.
http://www.execsoft.com/downloads/menu.asp?ad=sqlnl3

Access 3 conferences for the price of one. Over 160 sessions to choose from. Last chance to register; event begins Oct. 27. Click here to register and learn how you can win a Harley-Davidson motorcycle.
https://secure.win2000mag.com/events/sql_register.asp

5. RESOURCES

Devising calculations that deal with dates and times can be fraught with peril. You might want to organize data by the week—but which day of the week is first? It varies by country. Itzik Ben-Gan's T-SQL Black Belt column "Changing Times" describes helpful techniques for grouping weekly data when the first day of the week is dynamic. This article appears in the October 2002 issue of SQL Server Magazine and is available online at
http://www.sqlmag.com/articles/index.cfm?articleid=26163

Strange things have been happening since KSW's SQL Server team installed service pack 4 (SP4) for SQL Server 7.0 on a server that also runs IIS. Querying takes longer, CPU usage fluctuates wildly, Web pages load more slowly, and the SMTP server stopped sending out emails. Has a demon invaded the server? Offer your exorcist advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/rd.cfm?cid=4&tid=9649

(contributed by Brian Moran, [email protected])

Q. Can I write a script that checks for the existence of an index on a particular table?

A. I recently answered a question like this one on the Microsoft SQL Server public newsgroup at msnews.microsoft.com, and I'd like to share my answer here for two reasons. First, I want to show a convenient way to solve the problem. Second, and perhaps more important, I want to explain why referencing system tables directly can be a bad idea.

Here's a simple query that shows how you can determine whether an index called CustomerID exists on the Northwind database's Orders table:

SELECT INDEXPROPERTY( object_id('northwind..orders') ,'CustomerID' , 'IndexId' )

This query returns the IndexId of the index if the index exists and returns NULL if the index doesn't exist. The INDEXPROPERTY()function belongs to the family of functions called metadata functions. For more information about these functions, see the "Meta Data Functions" section of SQL Server Books Online (BOL). You'll find several helpful functions that let you query a variety of system-related data.

The newsgroup thread I responded to included the following script as a possible solution to the problem:

LISTING 1: Script That Checks for the Existence of an Index on a Specified Table

SELECT COUNT(*)FROM sysindexes I,sysobjects OWHERE I.id = O.id      AND O.name = 'TableName'      AND I.name = 'IndexName' 

This script would find the information you're looking for, but it requires you to directly query the sysindexes system table. I admit that I regularly query system tables, but it's a nasty habit that I'm trying to break. Microsoft has been warning us for years that we shouldn't query system tables because they're undocumented and could change in future releases. Lately, the company has been mumbling about making good on that promise in the next release of SQL Server, code-named Yukon. I'm trying to rid my queries of direct references to system tables by using system procedures, system functions, and other documented techniques for accessing system data.

Send your technical questions to [email protected].

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

eEye Digital Security developed a free tool to help you determine if your systems are vulnerable to the SQL Worm that can infect SQL Server. The Retina SQL Worm Scanner can scan as many as 254 IP addresses at once. The scanner alerts you when it finds a vulnerable IP address. Then, you can double-click the IP address and find information about how to fix the vulnerability. eEye Digital Security has also developed a special version of the Retina SQL Worm Scanner, Class B and A. The Class B and A scanners can scan an entire Class B and A subnet at one time. The Class B and A scanners are available for purchase by request only. Contact eEye Digital Security at 949-349-9062 or 866-339-3732.
http://www.eeye.com/html/research/tools/sqlworm.html

XlineSoft released DBtoHTML Express 3.0, software that lets users convert all major databases to HTML or PDF documents. The program is a template-based tool that can convert database files to static, search-enabled documents. DBtoHTML Express establishes a tabbed interface, in which you can generate HTML documents. After you connect to your database, you can select a table and build an SQL query or load an existing SQL script to run against the data. DBtoHTML Express supports SQL Server 2000, 7.0, and 6.5 and runs on Windows XP, Windows 2000, Windows NT, Windows Me, and Windows 9x systems. Pricing is $129 for a single-user license. Contact XlineSoft at 703-904-8376
http://www.xlinesoft.com

7. CONTACT US

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

(please mention the newsletter name in the subject line)