SQL Server Magazine UPDATE, January 23, 2003

Brian Moran talks about tracking configuration changes and settings within the servers and databases you support, learn about importing an MXL file, get the results of our latest instant poll and more!

ITPro Today

January 22, 2003

12 Min Read
ITPro Today logo in a gray background | ITPro Today

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

UltraBac Offers the Most Backup and Restore Options
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RO0AM

SQL Server Magazine Connections Spring Event
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RP0AN
(below COMMENTARY)

Find Answers to Your SQL Server Questions
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RQ0AO
(below NEWS AND VIEWS)

SPONSOR: ULTRABAC OFFERS THE MOST BACKUP AND RESTORE OPTIONS

UltraBac v7.0.2 provides customers with the ability to use any FTP server or IBM's Tivoli Storage Manager (TSM) as storage devices for backup and restore operations. The FTP Device allows administrators to perform backup and restore operations to any FTP server connected to the Internet by simply entering the server's address as the backup path. By including these devices as backup paths, UltraBac now sets the industry standard by offering more backup and restore options than any other application. Backup options include writing data to any type of local or remote media, including disk, tape, CD-RW and optical. Download now!
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RO0AM

January 23, 2003—In this issue:

1. COMMENTARY

  • Managing Configuration

2. SQL SERVER NEWS AND VIEWS

  • Microsoft Releases SQL Server 2000 SP3

  • Article Provides Checklist for Installing SP3 on a Virtual Server

  • Results of Previous Instant Poll: Working from a Baseline

  • New Instant Poll: Configuration Management

3. ANNOUNCEMENTS

  • Live Microsoft-Certified Training at Your Desktop

  • Online Seats Limited for Memory Management Event

4. RESOURCES

  • What's New in SQL Server Magazine: Keeping Up with Web Releases

  • Hot Thread: Running a Profiler Script in DTS

  • Tip: Importing an XML File

5. HOT RELEASES (ADVERTISEMENTS)

  • DataHabitat ZeroCode ETL

  • SQL Server Magazine Connections 3-for-1 Offer

6. NEW AND IMPROVED

  • Monitor SQL Server Transactions in Realtime

  • Verify Available Disk Space on SQL Server

7. CONTACT US

  • See this section for a list of ways to contact us.

1. COMMENTARY

  • MANAGING CONFIGURATION


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

The escalation engineers in the SQL Server Product Support Services (PSS) group have a complex job. Of course, they help callers solve specific problems. But one of their primary jobs is to help customers learn how to prevent problems in the first place. Over the past few weeks, I've shared advice I got from Bob Ward, an escalation engineer for SQL Server PSS. Ward explained that, although most customers know about the best practices that will prevent problems in their systems, many don't adhere to those best practices. Our readers have confirmed Ward's observation. The past three Instant Polls on the SQL Server Magazine Web site have asked readers if they follow these best practices, and most respondents have acknowledged that they don't. This result reveals that many customers are leaving their systems vulnerable to problems.

In the past two issues of SQL Server Magazine UPDATE, I discussed two of the three best practices that Ward recommends: establishing and testing a recovery plan and creating a performance baseline for your system. This week, I close the best-practices discussion by looking at Ward's third recommendation: tracking configuration changes and settings within the servers and databases you support.

Have you or a colleague ever said, "Wow! That database setting doesn't seem right. I wonder how long it's been like that?" If so, Ward's advice applies to you. Ward says that a surprising number of support calls revolve around the lack of a solid configuration-management plan. Too many customers simply don't track how and when changes are made to their server environments—and this lack of ongoing awareness can create many support problems. When you don't track your system's configuration, you might

  • inconsistently apply service packs and security patches

  • drop or add an index on a production box for testing purposes, then forget to add the index again when testing is finished

  • change the database recovery model setting for bulk load tasks, then forget to reset it when the tasks are complete

  • purposely or accidentally make a poor database configuration choice such as turning on auto shrink for production databases that experience widely ranging volumes of data in the course of a standard usage cycle

  • be unable to reset a production database to its original configuration after a complete rebuild because no one knows for sure what the original server and database settings were

The list of possible problems is almost infinite. But focusing on the list misses the point. Support calls—and the daily management of our database systems—would be much simpler if we tracked metadata-level changes to our database environments more rigorously.

I've spent the past few weeks establishing that a problem with best-practice compliance exists. But so far, I haven't tried to offer advice about how customers can do a better job of adhering to the best practices we've discussed, nor have I queried Microsoft for ideas about what the company can do to make best practices easier for customers. I have some thoughts about both sides of the equation, and 'll share them with you over the coming weeks. But I'd also like your input. Do you or your colleagues successfully implement these best practices? Send me an email describing your techniques. Do you have ideas about how Microsoft can make avoiding problems easier for us? Tell me your ideas. I'll share the best advice with your fellow SQL Server Magazine UPDATE readers.

SPONSOR: SQL SERVER MAGAZINE CONNECTIONS SPRING EVENT

Now in its fifth year, SQL Server Magazine Connections hits the "Big Easy!" Join us in New Orleans on May 6-9 for the latest and greatest information on SQL Server 2000 and the next release of SQL Server, code-named Yukon. Get information by calling 800-811-3486, 203-268-3204, or clicking here!
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RP0AN

2. SQL SERVER NEWS AND VIEWS

  • MICROSOFT RELEASES SQL SERVER 2000 SP3


Microsoft has posted the latest updates to SQL Server 2000 in Service Pack 3 (SP3). The new service pack includes fixes for known vulnerabilities and provides numerous added benefits. SP3 improves serviceability by providing enhanced error reporting, improved multiserver administration, and a new monitoring API. DBAs can now use QLogic's SANblade QLA2350 Fibre Channel controller to reduce CPU consumption and improve performance. And SP3 helps DBAs secure their SQL Servers by providing new security patches, enhancements to SQL Server Agent, and updates to security documentation in SQL Server 2000 Books Online (BOL). You can download the latest service pack at
http://www.microsoft.com/sql/downloads/2000/sp3.asp

  • ARTICLE PROVIDES CHECKLIST FOR INSTALLING SP3 ON A VIRTUAL SERVER


Before you install SQL Server 2000 Service Pack 3 (SP3) on a SQL Server 2000 virtual server, you must complete several essential checks to your system. Read about these checks—and why your installation might fail if you don't complete them—in the Microsoft article "INF: SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server for Failover Clustering" at
http://support.microsoft.com/default.aspx?scid=kb;en-us;811168

  • RESULTS OF PREVIOUS INSTANT POLL: WORKING FROM A BASELINE


The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How do you use baselines in your database environment?" Here are the results (+/- 1 percent) from the 192 votes:

  • 4% To monitor daily performance changes

  • 10% To track performance changes over time

  • 8% To troubleshoot problems when they come up

  • 14% All of the above

  • 64% We haven't established a baseline

  • NEW INSTANT POLL: CONFIGURATION MANAGEMENT


The next Instant Poll question is "Do you keep records to track and manage your system configuration?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, we've kept a record of our original system configuration, 2) Yes, we track changes and regularly update our configuration record, or 3) No, we don't have a formal tracking system.
http://www.sqlmag.com

SPONSOR: FIND ANSWERS TO YOUR SQL SERVER QUESTIONS

Inside the SQL Server Magazine Master CD, you'll find a gold mine of SQL Server code, tips, and information! And new with the March 2003 release, get the entire T-SQL Solutions newsletter article archive in addition to 48 complete issues of SQL Server Magazine! Here's your opportunity for realtime access to expert information right on your desktop. Search by keyword, subject, author, or issue. Mention Priority Code ei283aup to receive the March 2003 release!
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RQ0AO

3. ANNOUNCEMENTS


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

  • LIVE MICROSOFT-CERTIFIED TRAINING AT YOUR DESKTOP


SQL Server Magazine University (SSMU) e-Learning Center, partnering with Holistech, offers live online training by the industry's best instructors! Convenient 1-hour classes at your desktop save you time and money. Plus, students have 24 x 7 Virtual Lab access—not lab simulation, but realtime experience on real servers. Get the details at
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RR0AP

  • ONLINE SEATS LIMITED FOR MEMORY MANAGEMENT EVENT


Don't miss Kalen Delaney's 1-hour live Web seminar, "SQL Server 2000 Data Storage, Part 1: Memory Management," on Friday, February 7, at 11 A.M. (Mountain time), brought to you by SQL Server Magazine University (SSMU) and Solid Quality Learning. No need to leave your desk! Click here for complete details and register today!
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RS0AQ

4. RESOURCES

  • WHAT'S NEW IN SQL SERVER MAGAZINE: KEEPING UP WITH WEB RELEASES


For some DBAs, if it doesn't come in the box, it doesn't go on their server. But Microsoft's SQL Server Web downloads can add functionality to your database server. Learn about the benefits of these free updates in Michael Otey's January 2003 SQL Server Magazine article "Keeping Up with Web Releases," online at
http://www.sqlmag.com/articles/index.cfm?articleid=27499

  • HOT THREAD: RUNNING A PROFILER SCRIPT IN DTS


Norton has a script that SQL Server Profiler produced. He wants to run the script as a Data Transformation Services (DTS) package so that he can schedule it as a job. When Norton runs the script in Query Analyzer, it works. But when he copies the working script from Query Analyzer into DTS, the parse fails and he gets an error message. The script's GOTO statement references the label ERROR, but the label hasn't been declared. Why does this script parse and work well in Query Analyzer but not in DTS? Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=12102

  • TIP: IMPORTING AN XML FILE


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

Q. How can I import an XML file into SQL Server 2000?

A. You can import an XML file into SQL Server in several ways. Here are three approaches.

If you want to extract the document's entities and attributes into relational tables, the fastest way to import XML into SQL Server is to use the Bulk Load COM interface in SQL Server 2000 Web Release (SQLXML) 3.0 Service Pack 1 (SP1). The interface comes with the free SQLXML download at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/824/msdncompositedoc.xml . This method of importing XML is fast, and the download documents the Bulk Load tool with code samples.

If you don't want to extract the document's entities and attributes into relational tables, you can use the textcopy.exe command-line utility. Using textcopy.exe is a good method for loading the document into a text data type column.

If your XML document is simple, you can use Data Transformation Services (DTS) by writing transformation code that looks at each line of the XML document and extracts the information into tables. This approach requires you to define the XML file as an input data source, define a database table as the output data source, and write ActiveX script that parses the input for "<" and ">" characters to extract entities, attributes, and their values.

Send your technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

  • DATAHABITAT ZEROCODE ETL


ZeroCode ETL creates SQL Server data warehouses without programming. Cut your Business Intelligence costs by up to 80% by quickly building DTS Packages and Stored Procedures with a codeless and visual interface.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RT0AR

  • SQL SERVER MAGAZINE CONNECTIONS 3-FOR-1 OFFER


SQL Server Magazine Connections will co-locate with Microsoft ASP.NET Connections and Visual Studio Connections May 6-9 in New Orleans. Attendees will have a chance to win a Harley-Davidson! Register now to save:
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ0ggP0AW

6. NEW AND IMPROVED


(contributed by Carolyn Mader, [email protected])

  • MONITOR SQL SERVER TRANSACTIONS IN REALTIME


AppDancer Networks released AppDancer/FA, network application flow and analysis software. The newest version lets you monitor SQL Server and Exchange Server transactions in realtime. AppDancer/FA not only monitors your devices and applications and all associated IP flows but it also lets you understand the inner workings of your network. The software can show you whether your network is suffering from overuse or incorrect usage, which cause brownout conditions. The software supports SQL Server 2000 and 7.0. For pricing, contact AppDancer Networks at 770-643-6800.
http://www.appdancer.com

  • VERIFY AVAILABLE DISK SPACE ON SQL SERVER


Woodstone released Servers Alive 3.3, a program that can monitor any Winsock service, ping a host, and determine whether a Windows NT service or process is running. You can use the software to retrieve a URL, look at your database, and report on available disk space on your server. Servers Alive can also verify SQL Server and Oracle available disk space. You can run Servers Alive natively as a service. You can monitor as many as 1000 hosts and services, including all TCP services such as POP3, SMTP, FTP, and DNS. Servers Alive supports SQL Server 2000, 7.0, and 6.5 and costs $90 per license. Contact Woodstone at
http://www.woodstone.nu

7. CONTACT US


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

(please mention the newsletter name in the subject line)

  • WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
    More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.

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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like