SQL Server Magazine UPDATE, January 9, 2003

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

THIS ISSUE SPONSORED BY

How the First-to-Know Stay Ahead

T-SQL Solutions Now Available Online for Free
(below COMMENTARY)

Massive Workstation Security Hole...Ignored!
(below NEWS AND VIEWS)

SPONSOR: HOW THE FIRST-TO-KNOW STAY AHEAD

A proactive Database Administrator installed TNT Software's ELM Enterprise Manager 3.0 on his critical servers to assess the benefits of real time monitoring. During the first week, EEM 3.0 paged him as a disgruntled employee attempted to access confidential tables, emailed him when the backup on his SQL Server hung, and automatically restarted a failed SQL service. And one less headache, EEM 3.0 was simple to license. It was purchased and fully deployed during the second week. To experience How the First-to-Know Stay Ahead(tm) with real time monitoring, download your FREE 30-day copy of ELM Enterprise Manager today.

January 9, 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
See this section for a list of ways to contact us.

1. COMMENTARY

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

Microsoft escalation engineers bring a vast amount of experience to the Product Support Services (PSS) groups. In Tuesday's special edition of SQL Server Magazine UPDATE, I told you about the role of Bob Ward, an escalation engineer with SQL Server PSS, and explained the evolution of PSS. This week, let's look at some practical advice that Ward gave me.

I had hoped that Ward, who knows more about SQL Server than most other people in the world, would give me a "silver bullet" that would let me quickly solve all my SQL Server support problems. However, Ward says that no such silver bullet exists. (Ok, I didn't really think there was a silver bullet. But you can't blame for me asking!) Just as you and I do, escalation engineers need a solid understanding of the technology, a tight problem statement, and good troubleshooting skills to solve problems.

But, Ward did share a few thoughts about three common problems that many database customers bring to PSS:

Ward says that you can avoid these problems by adhering to basic SQL Server best practices. This advice won't come as a surprise, but interestingly, most people who ignore these suggestions know that it's the right advice to follow. This week, let's examine the first of the best practices that Ward recommends—developing and testing a recovery plan—and look at the remaining two in next week's commentary.

Most DBAs know that a great backup is worthless if you can't restore the data, and they know that they can help ensure successful data recovery by following the best practice of developing and testing a recovery plan. But Ward says he can't count the times that he's had to help a customer recover missing data after the customer couldn't restore what he thought was a good backup. We can't blame Microsoft for this kind of failure. SQL Server has effective, tightly integrated backup tools. The inability to restore data doesn't happen because the tools fail; it happens because a DBA hasn't fully thought through and tested the recovery plan. In fact, the early results of this week's SQL Server Magazine Instant Poll ( at http://sqlmag.com ) show that a surprising number of respondents don't even have a recovery plan.

Most DBAs give lip service to the idea that, in the event of a failure, the ability to restore data is what counts. But I'm amazed at the number of database customers—not just SQL Server customers—who don't test their recovery plans. The worst time to test your recovery plan is during a critical outage. Unfortunately, most customers write a recovery plan, test the backup portion, and perform only a limited and trivial test of the plan's restore component. I admit that testing a backup is easier than testing a restore. Testing a restore requires creativity and agility to fake an outage, then recover the data—especially if you don't have an extra production-quality recovery server lying around. But testing your recovery plan is a crucial step in maintaining the readiness of your production database environment.

Creating and testing a restore plan is entirely the responsibility of the SQL Server user, but Microsoft can do a few things to make it easier for us to adhere to the other two best practices that Ward listed. Next week, I'll discuss the difficulty involved in setting performance baselines and monitoring changes to your database configuration and other settings, and I'll offer some suggestions for how Microsoft can make these tasks easier for the SQL Server community to implement.

T-SQL SOLUTIONS NOW AVAILABLE ONLINE FOR FREE

SQL Server Magazine has relaunched T-SQL Solutions Web site FREE to registered users, making all the content from its print newsletter available to the broad community of SQL Server administrators and developers. To access new articles and tips, archived articles, and associated code, simply complete an online registration form! The site features Kalen Delaney's T-SQL Admin column, Kimberly L. Tripp's T-SQL Tutor column, and Itzik Ben-Gan's collection of T-SQL Q&As, as well as an active T-SQL forum, a weekly Instant Poll, and highlighted links to practical T-SQL articles from past issues of T-SQL Solutions and SQL Server Magazine. Register today!
http://lists.sqlmag.com/cgi-bin3/flo?y=ePAl0FgQMn0BRZ0Kqz0A8

2. SQL SERVER NEWS AND VIEWS

For the recent eWeek OpenHack 4 competition, Microsoft engineers built a Web site that withstood more than 82,500 attempted attacks. The site used SQL Server 2000 with the Microsoft .NET Framework, Microsoft Windows 2000 Advanced Server, and Internet Information Services (IIS) 5.0. Read how the engineers built and configured the solution, and learn about best practices that software developers and systems administrators can use to secure their own solutions.

You can use SQL Server 2000 to increase availability, recover from catastrophic disasters, and prevent and minimize downtime. The Microsoft SQL Server 2000 High Availability Series helps you plan a data center to achieve the level of availability you need for your business environment. The series shows you how to implement server redundancy solutions to minimize unavailability caused by such events as server failures and planned downtime. Access the series online.

Microsoft reports that logins that use the SQL Server Authentication (Standard) security mode might be allowed rights that the login wouldn't normally have. The logins can acquire the new rights when they use a particular query method to access data from an OLE DB data source. To avoid this security problem, download the SQL Server 2000 Cached Connection Patch.

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How much downtime can your environment handle at any one time?" Here are the results (+/- 1 percent) from the 443 votes:

   -  17% None   -  35% 1-15 minutes   -  30% Less than 3 hours   -  11% Less than 8 hours   -   6% Availability isn't a concern

The next Instant Poll question is "Have you tested your database recovery plan?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, we test it regularly, 2) Yes, we tested it when we developed it, 3) No, we're still working on our recovery plan, or 4) No, we don't have a formal recovery plan.
http://www.sqlmag.com

SPONSOR: MASSIVE WORKSTATION SECURITY HOLE...IGNORED!

In just a few minutes any of your domain users could become the administrator of ALL your machines without your knowledge. A quick search of Google.com for password crackers is all it takes. There is a solution. Download our guide to plugging the DISTRIBUTED CREDENTIALS FLAW in Windows.

3. READER CHALLENGE

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

Congratulations to Kristofer Anderson, senior software developer for RR Technologies in Fort Lauderdale, Florida, and Arnie Rowland, SQL Server trainer and consultant for WestWood Consulting, Inc. in Portland, Oregon. Kristofer won first prize of $100 for the best solution to the January Reader Challenge, "Corrupted Characters." Arnie won second prize of $50. You can find a recap of the problem and the solution to the January Reader Challenge here.

Now, test your SQL Server savvy in the February Reader Challenge, "Reporting Sales" (below). Submit your solution in an email message to [email protected] by January 16. 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: Karen is a SQL Server developer at a Web-hosting company that uses SQL Server 2000 and 7.0 to host several sales databases. The Purchases table in each sales database captures items that a company sells. The Purchases table schema, with the relevant columns, is

   CREATE TABLE Purchases (    SaleID int NOT NULL IDENTITY PRIMARY KEY,    SaleAmount money NOT NULL,    SaleTime smalldatetime NOT NULL   )   CREATE INDEX idx_saletime ON Purchases( SaleTime )   — Sample data:   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (6.24, 'Jan 1, 2002 8:22 am')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (22.29, 'Jan 1, 2002 10:42 pm')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (128.40, 'Jan 2, 2002 11:12 am')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (0.45, 'Jan 3, 2002 6:28 pm')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (16.32, 'Jan 4, 2002 11:41 am')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (9.11, 'Jan 6, 2002 6:30 pm')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (69.96, 'Jan 6, 2002 6:55 pm')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (6.99, 'Jan 6, 2002 8:23 pm')   INSERT INTO Purchases (SaleAmount, SaleTime) VALUES (18.43, 'Jan 7, 2002 11:42 am')

Karen needs to generate a report from each database's Purchases table that shows the following for a given month of the year:

In addition, the report must include all the days between the first and last sales date of the month, and the output must be ordered by the day of the month. Help Karen produce this report efficiently.

4. ANNOUNCEMENTS

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

SQL Server Magazine University delivers quality technical training on your schedule and at your desktop. Microsoft-certified courses and Web Seminars are taught by SQL Server industry gurus, SQL Server Magazine authors, MVPs, and instructors from Solid Quality Learning. Microsoft-certified courses include 24 x 7 Virtual Lab access. Get complete info.

Join Kalen Delaney, Friday, February 7, 2003, for a 1-hour Web seminar, "SQL Server 2000 Data Storage, Part 1: Memory Management," brought to you by SQL Server Magazine and Solid Quality Learning. No need to leave your desk; set your browser and dial the phone! Click here for details and to register!

5. RESOURCES

Students in a master's degree program were given the task of completing an analysis project using data-mining techniques on real data. Authors Christine Y. Mato and Greg Meyer chose to analyze a body of data that had been collected by the United States Geological Survey (USGS) between 1955 and 1970. The results of their data-mining project show that whatever the size of the data set, SQL Server 2000's data-mining capabilities can save you time and effort. Read Mato and Meyer's January 2003 article, "Unearthing Patterns" online.

Nfide 1 has been using xp_sendmail on a server running SQL Server 2000 Service Pack 2 (SP2), and until a week ago, the procedure worked. But inexplicably, he's now getting the following error:

   Server: Msg 18025, Level 16, State 1, Line 0   xp_sendmail: failed with mail error 0x80004005

Nfide 1 has searched Microsoft's TechNet documentation, but hasn't found a solution that makes xp_sendmail work. Nfide 1 is also confused because he has another SQL Server that's configured identically, and it runs xp_sendmail without a hitch. What's the problem with the first server? Offer your advice and read other users' suggestions on the SQL Server Magazine forums.

(contributed by Microsoft's SQL Server Development Team, [email protected])

Q. Can I install SQL Server 2000 and 6.5 on the same machine?

A. Yes, you can install SQL Server 2000 and 6.5 on the same machine if SQL Server 2000 is using named-instance support. In this configuration, you run SQL Server 2000 as one (or more) named instances and SQL Server 6.5 as a default instance. However, we have a couple of caveats about this setup. Avoid running SQL Server 2000 and 6.5 together in clustered mode because the technology used to cluster SQL Server 2000 is incompatible with previous versions. Save time by going to reviewing Section 6 (SQL Server 2000 install orders) and Section 22 (how SQL Server 2000 named and default instances work side by side with SQL Server 6.5) of the Microsoft article "Frequently Asked Questions-SQL Server 2000 Setup". "Basic Guidelines for Installing SQL Server 6.5 or 7.0" focuses on install orders and explains the pitfalls of running in clustered mode. Also read "Supported Windows Versions for SQL Server", which warns that when you install the SQL Server 6.5 server components or client tools on Windows 2000, you might receive the following error message:

   This program does not run correctly   on this version of Windows. To continue,   click Run program. To exit, click Cancel.   For more information, click Details.

You can safely ignore this message. Click Run to continue with the install.

Another caveat: When you run scripts with administrator permissions, be sure that you're connected to the correct instance. Confirming an accurate connection will help you avoid unexpected errors such as running obsolete commands against SQL Server 2000 or SQL Server 2000-specific commands against your SQL Server 6.5—and an unnecessary phone call from your operations team.

Send your technical questions to [email protected].

6. HOT RELEASES (ADVERTISEMENTS)

Make life easy! Instantly restorable history of all objects—with Visual Source Safe connectivity! Predictive typing shows context-aware pick lists of objects and variables! Multi-database tree display! Multiple editing windows! Make your own script templates!

SQL-UP! creates distributed SQL Server Clusters over LAN or WAN without a shared storage device and with no distance limitations. Clustered databases are automatically synchronized in real-time, ensuring uptime and disaster-protection. Click here for free download and information.

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Lumigent Technologies announced Entegra, software that can create audit records of data access to ensure compliance with business-practice requirements. Entegra provides you with an audit trail of information about data access, including database structure and permissions changes. You no longer need to modify every application that might be used to access data. Entegra captures all data access, even data access directly against the database. The software provides a trigger-free approach across all applications and databases. You can see a complete, sequential record of changes to any database record. Entegra supports SQL Server 2000 and 7.0 and costs $3000. Contact Lumigent at 978-206-3700 or 866-586-4436.

i-tivity announced Password Recovery Wizard for SQL Server 2000, a wizard that can recover passwords from offline and online servers. The wizard includes 35 different language dictionaries and word lists and a permutation engine to recover passwords for SQL Server accounts. Password Recovery Wizard for SQL Server 2000 costs $249.99 for one license. Contact i-tivity.

8. CONTACT US

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

SQL Server Magazine UPDATE is brought to you by SQL ServerMagazine, 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://www.sqlconnections.com