SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
THIS ISSUE SPONSORED BY
Live Microsoft-Certified Training at Your Desktop
Experience the Benefits of Real Time Monitoring
(below NEWS AND VIEWS)
SPONSOR: T-SQL TUNING TIPS BY QUEST SOFTWARE
Have you ever enabled the SHOWPLAN_TEXT option for a query only to find out you can't understand the results? Ever wonder what the difference between a bookmark lookup and an index seek is? Learn the latest tips and tricks by SQL tuning expert and best-selling author Kevin Kline in the final installment of his SQL tuning white paper series. This white paper focuses on how to make the most of your tuning time by explaining all of the results returned by SHOWPLAN_TEXT and the Graphic SHOWPLAN tool. Download the FREE white paper today!
January 30, 2003—In this issue:
- SP3: To Install or Not to Install?
2. SQL SERVER NEWS AND VIEWS
- Slammer/Sapphire Worm and Shades of Code Red
- Internet Endures Blistering Attack
- SQL Slammer Worm Hits Microsoft
- Microsoft Releases MDAC 2.7 SP1
- Results of Previous Instant Poll: Configuration Management
- New Instant Poll: Applying the Patch
- SQL Server Magazine Connections
- SSMU Web Seminar Instructors Make the Difference!
- What's New in SQL Server Magazine: Starting SQL Server
- Hot Thread: SQL Server Connectivity Through a Firewall
- Tip: Initializing a Database When Installing Replication
5. HOT RELEASES (ADVERTISEMENTS)
- DataHabitat ZeroCode ETL
- mssqlXpress the Ultimate IDE for MS SQL!
- Omnireplicator Accelerates Data Integration
6. NEW AND IMPROVED
- Stop the SQL Slammer Worm
- Drop Tables from Your Database into Multistep Queries
7. CONTACT US
See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, firstname.lastname@example.org)
Microsoft released SQL Server 2000 Service Pack 3 (SP3) on January 17, raising the inevitable question, "To install or not to install?" SQL Server Product Support Services (PSS) recommends applying the latest service pack even if you're not aware of a specific fix that will help you. I'm aggressive about applying service packs because I hate to spend hours tracking down a problem only to find that the last service pack fixed it. However, some DBAs prefer to wait a few weeks and monitor the newsgroups to be sure that installing a new service pack won't create a problem. And some SQL Server shops that have high uptime requirements for their production systems might have to wait months before applying a service pack. A recent newsgroup posting said that one company took 8 months to test and deploy SP2 on one of its critical systems.
The "don't fix it if it ain't broke" school of thought is prudent when it comes to server maintenance. SQL Server service packs are especially tricky because you can't easily remove them. To remove SP3 (like SP1 and SP2), you have to uninstall SQL Server, then reinstall from the ground up. Needless to say, you should have a well-crafted recovery plan—including tested backups—before you apply a service pack to a production server. Like doctors, good system administrators should live by the creed "first do no harm" when making decisions that can affect the stability or availability of the systems they manage.
However, once you've installed SP3, you can take advantage of the upgrades it provides. Service packs are supposed to fix bugs, but SQL Server service packs also regularly add new features; SP3 is no exception. The readme.txt file's section 5.1, Database and Desktop Engine Enhancement, provides a complete and cumulative list of all the features that SQL Server 2000 service packs have added. One new SP3 function, fn_get_sql(), is particularly interesting. This function is similar to the existing DBCC INPUTBUFFER command. Both functionalities let you see the SQL statement that's being executed by a given SPID, but fn_get_sql() is more powerful. DBCC INPUTBUFFER shows only the first 255 characters of a SQL statement and shows only the SQL statement at the top of a nesting hierarchy. Fn_get_sql() lets you see the entire SQL statement that's being executed, even if it's nested within multiple layers of procedure calls. For example, if a procedure named Proc1 calls Proc2, DBCC doesn't let you see when Proc2 is running, whereas fn_get_sql() shows when Proc2 is being called.
Note that SP3's main download page announces that the service pack contains a "new monitoring API" with which "database administrators (DBAs) or third-party tools can diagnose problem processes." That item refers to fn_get_sql(), which is a great function but hardly a monitoring API. This misnomer has already caused some confusion and started discussions on the SQL Server newsgroups, with people searching for documentation about a new API that doesn't exist.
Experienced and junior DBAs alike need to read the readme.txt files for each service pack. Read them end to end—you'll almost always find a nugget of information that's relevant to your environment. In the case of SP3, you also need to read the addendum to the readme file to get all the readme information. Think of it as readme.txt Part 2.
Even if you don't immediately plan to apply SP3 to any of your servers, visit the SP3 download site. With SP3, Microsoft has released an updated version of SQL Server Books Online (BOL) and updated samples for SQL Server. You can find all the relevant download files, fix lists, and readme documents at http://www.microsoft.com/sql/downloads/2000/sp3.asp .
One final note: Please review the latest security posting about the SQL Slammer worm at http://www.microsoft.com/security/slammer.asp and update your systems accordingly. This newest bulletin describes a patch that fixes a SQL Server problem that Microsoft originally found and fixed last summer. The QL Slammer worm couldn't have spread so quickly over the weekend if SQL Server administrators—including some at Microsoft, according to news reports—had kept up with the latest security hot fixes. (For coverage of the SQL Slammer worm, see the News and Views section below.) Over the past few weeks, I've discussed the importance of adhering to best practices, and this unfortunate worm attack underscores the importance of making sure the basics are covered. I'll revisit the best-practice discussion in an upcoming issue of SQL Server Magazine UPDATE. But PLEASE review the Microsoft security bulletin right now.
LIVE MICROSOFT-CERTIFIED TRAINING AT YOUR DESKTOP
SQL Server Magazine University e-Learning Center (SSMU), in partnership with Holistech, Inc., offers LIVE online training by the industry's best instructors! These SQL Server gurus have real-life business application experience, not just theory. Convenient one-hour classes at your desktop save you time and money. Students have 24 x 7 access to our Virtual Lab—not lab simulation, but realtime experience on real servers. PLUS, your registration includes online archives of each class that are available 24 x 7 for 90 days, offering you the opportunity to catch up on a missed class or to review at your own pace! Get all the details here.
2. SQL SERVER NEWS AND VIEWS
Although the SQL Slammer worm didn't damage data or databases, the uproar that it caused has proved some simple concepts. First, that a tiny worm (376 bytes) with only the essential amount of code can spread rapidly and consume large amounts of bandwidth in the process. And second, that it's essential for businesses to take defensive security measures sooner rather than later. Mark Edwards explores the Slammer worm's effects in his Security UPDATE commentary "Slammer/Sapphire Worm and Shades of Code Red".
(contributed by Paul Thurrott, email@example.com)
A fast-spreading computer worm attacked the main pillars of the information superhighway Saturday, bringing almost 20 percent of the Internet to its knees. Security experts are already calling the attack the worst the Internet has suffered since a similar worm called Code Red wreaked havoc nearly 2 years ago. This time, the worm—dubbed SQL Slammer and Sapphire—targeted servers running SQL Server 2000 and 7.0. In July 2002, Microsoft supplied a fix that would have prevented this problem, and just last week the company released SQL Server 2000 Service Pack 3 (SP3), which included the fix. As is usually the case with such outages, human error—in the form of inadequately updated servers—is at fault.
"Microsoft is currently investigating a virus that appears to affect versions of SQL Server 2000 that aren't up-to-date with service packs," the company noted on its Web site this weekend. "The attack has resulted in widespread Internet availability issues. At this time, we highly recommend that all of our customers running SQL Server 2000 update their servers immediately to SP3."
As of Saturday evening, the worm had compromised almost 200,000 servers. Experts I spoke with at DataPipe, a New York-based hosting company, said that the relatively benign worm replicates itself and presents a Denial of Service (DoS) attack. "It's not malicious code, so it doesn't delete or pass customer data along to other servers," said Brian Laird, senior application developer at DataPipe. "Unfortunately, Microsoft has issued several cumulative security patches for SQL Server since the original patch was issued in July. Had administrators installed any of these patches, this worm would have been prevented from spreading."
Many network administrators, including those at DataPipe, were able to block SQL Server network traffic, help prevent the worm's spread, and ease network congestion. Others weren't so lucky. The worm devastated DellHost, Interland, and other hosting companies, as well as many of UUNet's core routers. Worldwide, the worm caused damage in many locations; KT, South Korea's largest Web access provider, went offline Saturday.
Investigators at the Federal Bureau of Investigation's (FBI's) National Infrastructure Protection Center (NIPC) are looking into the problem but haven't yet determined where the attack originated. By Saturday evening, however, Internet traffic reached usual levels as network administrators shored up their SQL Server boxes.
For Microsoft's response and to download SQL Server 2000 SP3, visit the Microsoft Web site.
(contributed by Paul Thurrott, firstname.lastname@example.org)
Just a week after Microsoft celebrated the 1-year anniversary of its Trustworthy Computing initiative, one of the most virulent computer worms of all time hit the company. The so-called SQL Slammer worm, which is credited with bringing vast portions of the Internet to its knees over the weekend, targets a known SQL Server 2000 security vulnerability that the company first fixed last summer. Although administrators might have been lax in applying the fixes, various groups are now complaining that Microsoft's fixes were difficult to install—so difficult, in fact, that the company didn't patch many of its own servers, which the worm subsequently infected.
"We, like the rest of the industry, struggle to get 100 percent compliance with our patch management," said Microsoft spokesperson Rick Miller, who acknowledged that the worm affected many of the company's servers, including much of the MSN infrastructure, which was widely unavailable over the weekend. "We recognize—now more than ever—that this is something we need to work on. And, like the rest of the industry, we're working to fix it."
Ironically, in a letter to customers last week, Microsoft Chairman and Chief Software Architect Bill Gates discussed Trustworthy Computing and the progress his company has made during the past year. He ended the letter with a list of things customers can do to help, and the first item was "stay up-to-date on patches." That advice is fairly obvious and something the company might take to heart itself.
Since SQL Slammer hit, however, Microsoft has made it easier to patch SQL Server by using a standard executable update that doesn't require administrators to copy files manually, as earlier patches did. You can find the new SQL Slammer patch on Microsoft's Web site, or you can simply install SQL Server 2000 Service Pack 3 (SP3), which also patches the vulnerability.
Microsoft announced the availability of Microsoft Data Access Components (MDAC) 2.7 Service Pack 1 (SP1). This new release includes the same data access core components as Microsoft Windows XP SP1, but doesn't include Microsoft Jet, the Microsoft Jet OLE DB Provider, the Desktop Database Drivers ODBC Driver, or the Visual FoxPro ODBC Driver. For more information or to download MDAC SP1, visit the Microsoft Web page.
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Do you keep records to track and manage your system configuration?" Here are the results (+/- 1 percent) from the 188 votes:
- 30% Yes, we track changes and regularly update our configuration record
- 63% No, we don't have a formal tracking system
The next Instant Poll question is "When did you apply the patch for the elevation of privilege vulnerability that SQL Slammer is attacking?" Go to the SQL Server Magazine Web site and submit your vote for 1) Last year, when the patch was first released, 2) When the cumulative patch was released, 3) When Slammer began its attack, or 4) We haven't applied the patch yet.
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.
(brought to you by SQL Server Magazine and its partners)
If you're looking for 3 full days of technical drilldowns into Microsoft SQL Server, an opportunity to interact live with your favorite writers from SQL Server Magazine, or a desire to talk first-hand with Microsoft product architects, then you need to register today to attend SQL Server Magazine Connections. Register now and you'll also gain free access to concurrently run Microsoft ASP.NET Connections and Visual Studio .NET Connections—three conferences for the price of one.
SQL Server Magazine University (SSMU) Web seminar instructors are tried-and-true people you've come to know and trust through their articles and insights published in SQL Server Magazine. Finally, online training led by SQL Server gurus with real-life business application experience, not just theory! Get complete course info.
Starting SQL Server seems like it should be a simple operation, but you might discover that you need to use different start methods for different situations. Fortunately, you have multiple options for starting SQL Server. Michael Otey highlights seven of these options in his January 2003 SQL Server Magazine article " Starting SQL Server," available online.
Acitservices is running a project-management tool that has information stores on a SQL Server behind a firewall. Clients should be able to connect to the SQL Server from anywhere on the Internet. He's been trying to connect to the SQL Server externally through Query Analyzer by using SQL Server authentication and the firewall IP address, but he gets the error message "MSG 11, level 16, state 1 \[microsoft\] \[odbc sql server driver \] \[dbnetlib\] general network error. Check your network documentation." Acitservices has tried connecting to the SQL Server database through the firewall by using the SQL Server IP address and the firewall IP address, but neither address works. How can acitservices establish a connection? Offer your advice and read other users' suggestions on the SQL Server Magazine forums at this URL.
(contributed by Microsoft's SQL Server Development Team, email@example.com)
Q. When I installed SQL Server 7.0 replication, I chose not to initialize the database. Now I'm getting an error message saying that SQL Server can't find the stored procedure needed for replication. How can I fix this problem?
A. If you choose the option to initialize the database during replication installation, snapshot replication processing automatically creates the custom stored procedures and triggers that replication needs. But when you initialize the subscriber manually, you need to create the replication stored procedures and triggers manually as well. To fix the problem, you can generate scripts that will recreate the stored procedures and triggers every time you set up replication. First, choose the "immediate update" option for the subscriber. Next, run the following stored procedures at the publisher: sp_scriptinsproc, sp_ scriptdelproc, sp_scriptupdproc, and sp_script_synctran_commands. Then, apply the scripts that these stored procedures generate to the subscribing database.
Send your technical questions to firstname.lastname@example.org.
5. HOT RELEASES (ADVERTISEMENTS)
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.
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! Why use Query Analyzer and Enterprise Manager?
Lakeview Technology's OmniReplicator provides real-time changed-data replication of your cross-platform information and automates data sharing across Oracle, DB2, Sybase and SQL Server. Download your FREE OmniReplicator Technical Backgrounder by visiting our Web site.
6. NEW AND IMPROVED
(contributed by Carolyn Mader, email@example.com)
Ecora announced that it's offering a free SQL Worm Prevention kit to protect infrastructures from the SQL Slammer worm. The SQL Slammer worm enters networks through the Internet, looks for unpatched SQL Server 2000 databases, replicates itself, and starts sending out data that results in Denial of Service (DoS) failures. The free kit includes Ecora PatchLite, a tool for patch analysis, the patch to close the Slammer worm, recommended Computer Emergency Response Team (CERT) guidelines, and step-by-step instructions about how to apply the patch. You can download the SQL Worm Prevention kit at the following Web site:
Austin Sierra Technologies announced Query Studio 1.1, a multistep visual SQL builder for Microsoft Visual Studio .NET that supports cross-database queries and cross-schema queries for database servers. You can visually drop tables and columns from your database into multistep queries without writing SQL or ADO.NET code. The software supports SQL Server, Microsoft Access, Advantage Database Server, IBM DB2, Oracle, and Sybase databases. Query Studio is available in two editions: the Desktop Edition and the Developer Edition. The Desktop Edition is available in a standalone application and costs $99. The Developer Edition integrates with Visual Studio .NET and costs $249. Contact Austin Sierra Technologies at 425-885-2100 or firstname.lastname@example.org.
7. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — email@example.com
- ABOUT THE NEWSLETTER IN GENERAL — firstname.lastname@example.org
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — email@example.com
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — firstname.lastname@example.org
- 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@example.com 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.
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.